Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Archiving script: Port to Powershell #1411

Closed
halfdan opened this issue Jun 4, 2010 · 36 comments
Closed

Archiving script: Port to Powershell #1411

halfdan opened this issue Jun 4, 2010 · 36 comments
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Milestone

Comments

@halfdan
Copy link
Member

halfdan commented Jun 4, 2010

While having to develop on Windows and a bash was not an option to install, I ported the archive.sh script to PowerShell. It pretty much works like the archive.sh script does, needs a single line to be configured by the user (location of php.ini).

I'd like to adjust the docs (http://piwik.org/docs/setup-auto-archiving/) to explain how the script is used on Windows using a scheduler.
Keywords: archive

@mattab
Copy link
Member

mattab commented Jun 4, 2010

Thanks halfdan.

Can you please write a comment with the documentation changes?
Will this typically work on all windows machines, IIs or Apache?

@halfdan
Copy link
Member Author

halfdan commented Jun 4, 2010

The Webserver is irrelevant (IIS, Apache, lighttpd, ...). The script works on all Windows machines that have the Powershell installed (standard for more recent Windows versions).


How to setup the Windows Scheduler to automatically archive the records?

Piwik comes with a Powershell script (located in: misc/cron/archive.ps1) that you can use to setup automatic archiving.

Configuration: Open the script in a text editor of your choice and adjust the first line according to your system configuration.

$PHP_INI = "C:\Windows\php.ini"

The value of $PHP_INI must reflect the full path to your php.ini.

You also have to make sure that either php5.exe or php.exe lie in a path that is specified in the %PATH% environment variable. Select Start -> Run and type "cmd" to open up a command line. Then type php.exe -version and hit enter to check whether PHP is available or not. If you have to change the %PATH% variable have a look at How To Manage Environment Variables in Windows.

Setting up the scheduler:
To open the task scheduler on Windows (XP, 7, 2003/2008 Server) click All Programs, point to Accessories, point to System Tools, and then click Scheduled Tasks. Click Add Scheduled Task and name the task e.g. "Piwik Auto Archiving". Click on the tab Trigger and add a new trigger. Select to create a trigger after a timetable, to be executed daily and every hour. Confirm the settings and switch to the action tab. Add a new action and select to start a program. We want to start powershell.exe using the additional arguments Noninteractive Noprofile Command &{<fullpath to script>} (where <fullpath to script> is the full path to your archive.ps1 script).

That's it!

References:
Using a task scheduler to run a Powershell script
How To Manage Environment Variables in Windows


I plan to create a screencast later on (probably in July) to explain the whole thing in-depth. But I think this is good enough for now.

@halfdan
Copy link
Member Author

halfdan commented Jun 5, 2010

Note: Use real quotation marks! " not and as posted before, this won't work!

@anonymous-matomo-user
Copy link

For Powershell 1 users the function IsNumeric wouldn't work because of the "try".

Alternatively, you can use the static System.Int32.TryParse() method in the .NET framework:

function IsNumeric ($x) {
$x2 = 0
$isNum = [ref
return $isNum
}

Enjoy!

@mattab
Copy link
Member

mattab commented Jun 10, 2010

halfdan, thanks for that.
angel80 and halfdan, is there a solution that the script can work on all powershell versions? (to try and minimise future user support and make it easier ;-)

@halfdan
Copy link
Member Author

halfdan commented Jun 13, 2010

angel80, thanks!

I've uploaded a new version that works with Powershell 1.0 too (tested with powershell.exe -version 1.0 .\archive.ps1). I've removed the "IsNumeric($x)" and replaced it with a regex match against ^\d+$.

jwiesener: Thanks for your script (via mail), I've copied your commenting to my script.

@halfdan
Copy link
Member Author

halfdan commented Jun 13, 2010

Attachment:
archive.ps1

@mattab
Copy link
Member

mattab commented Jun 15, 2010

is Powershell included by default in all IIS servers?
if not, should the documentation explain how to download/install it?

The other thing is that the script contains a path to the php.ini - is this mandatory?

If the php.ini path is mandatory, it means that most windows users will have to edit the file, and that their modification will be lost when they update to a newer Piwik release. How can we ensure that users don't have to modify the script every time Piwik is updated? for example, we could maybe ask them to add the path to the php.ini file in their config.ini.php ? The documentation would explain that + the script would return an error if the path is not found in the config file. Thoughts?

@mattab
Copy link
Member

mattab commented Jun 23, 2010

halfdan, can you please answer questions above? that would be good to have it included in 0.6.3 which I'm about to release. Thanks!

@mattab
Copy link
Member

mattab commented Jun 23, 2010

(In [2358]) Refs #1411 Adding Powershell script.

Thank you Halfdan for the patch and documentation. I updated documentation at http://piwik.org/docs/setup-auto-archiving/ using your text

@halfdan
Copy link
Member Author

halfdan commented Jun 25, 2010

Sorry for the late response - I was in the Outback, didn't expect to be away from the net so long.

For the .ini: I found it to be necessary. In Linux environments it's common to put the php.ini in the default path (e.g. /etc/php5/cgi/php.ini, etc.), so that it gets automatically discovered by php-cli. I had the problem on Windows that php.ini was expected to be in C:/Windows/ (which it was not) and the mysql extension was not loaded - making the script fail completely. I therefore thought it to be the best to include the path to php.ini in the archiving script itself.

I could definetely adjust the script to read the info from global.ini.php if you like - Just post me a message.

Powershell is AFAIK included from Windows Server 2008 and later. For 2003 Powershell is available but not installed per default (at least I couldn't find any proof of it).

@mattab
Copy link
Member

mattab commented Jun 25, 2010

np for the delay, as you saw I included it anyway.
I think reading from the config.ini.php file would be best indeed :) It's too risky to overwrite people's scripts and they wouldn't know about it.

Can you also submit changes to the doc?
in the doc update, should we write something like Microsoft does in their powershell page: Download Windows PowerShell 1.0 for Windows XP SP2, Windows Server 2003 or Windows Vista via our Windows PowerShell download page.
Thanks :)

@robocoder
Copy link
Contributor

Please leave out the explicit path to php.ini; instead document using the PHPRC environment variable if php.ini isn't in %SystemRoot%

@anonymous-matomo-user
Copy link

Hi,

this script is one of the best ideas :)

But I've a litte problem with it. When I start the script as described here I get the following error:

The term 'C:\marktplatz\apache2.2\htdocs\pwiki\misc\cron\archive.windows.ps1' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
At line:1 char:69
+ &{C:\marktplatz\apache2.2\htdocs\pwiki\misc\cron\archive.windows.ps1} <<<<

I'm not a powershell script expert (and english to ;)) so it would be great when you help me. In powershell I set the Set-ExecutionPolicy to Unrestricted for testing but it doesn't help. The environment variable path ist set correct. I've no idea where the problem is.

Thanks in advanced
Dennis

@anonymous-matomo-user
Copy link

...a little update to my previous post. I think the term problem has resolved (I don't know how) but now I get a new error:

Select-Object : Cannot expand property "Definition" because it has nothing to expand.
At C:\temp\archive.windows.ps1:31 char:53
+     $PHP_BIN = Get-Command $phpTestBin|Select-Object  <<<< -ExpandProperty Definition

as anyone an idea what the problem is?

Thanks
Dennis

@halfdan
Copy link
Member Author

halfdan commented Jun 30, 2010

Hi Dennis,
the problem is that your php.exe is not located in the Windows %PATH%, have you tried to invoke php.exe using Powershell or cmd.exe? See the documentation on how to fix this. The script however should've catched this error and exited with a proper message, I'll fix that code and see that it'll get updated in the next release.

@anonymous-matomo-user
Copy link

...thank you for the fast reply.

the php folder is in the %PATH% environment. I have test it like it is described in the documentation (open command promp -> type php -version) and I get a correct response from php. Also I've test it from a powershell command ($env:path and php -version) and everything looks good. So I think it is not a problem with the %PATH% variable. If I interprete the error message and the script code correct then the script have no problem with the %PATH% but with the following line

    $PHP_BIN = Get-Command $phpTestBin|Select-Object -ExpandProperty Definition

and if I understand the code correct the %PATH% is tested in the if statment before? But I don't know much about powershell java is more my world ;)

Or do you mean that explicit the php.exe have to be in the %PATH% environment? Normal the path to the folder is enough in windows?

@halfdan
Copy link
Member Author

halfdan commented Jun 30, 2010

The folder is of course enough. You are right, the if checks if either php.exe or php5.exe is located in %PATH%. The next line extracts the path for the binary.

What version of PowerShell are you using? Can you run the following command:

Get-Command php.exe

and post the output?

@anonymous-matomo-user
Copy link

Hi,

I'm using PowerShell 1.0 on a Windows 2003 64 Bit Server.

The output of the command is:

CommandType     Name                            Definition
-----------     ----                            ----------
Application     php.exe                         C:\marktplatz\php\php.exe

hope that help's :) if you need more information let me know I have admin access to the server so I can do what I / you want :) ok not all because it is our production enviroment ;)

@anonymous-matomo-user
Copy link

Hello,

I have the same issue like smithers.

"Select-Object : Cannot expand property "Definition" because it has nothing to expand."

I'm using Windows XP, Apache, PHP 5 and everything is configured (PATH, etc.).

Thank you!

@anonymous-matomo-user
Copy link

Hi,

I also had the problem with the php file not found, but I simply specified the file manually.

Instead of the "foreach($phpTestBin in $BINS)" block I simply wrote:

$PHP_BIN = "C:\Program Files (x86)\PHP\php.exe"

This was my error before:

C:\Users\Administrator>C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
 -Command "&{E:\wwwroot\Piwik\misc\cron\archive.windows.ps1}"
Select-Object : Die Definition-Eigenschaft kann nicht erweitert werden, da sie
keine erweiterbaren Elemente enthlt.
Bei E:\wwwroot\Piwik\misc\cron\archive.windows.ps1:30 Zeichen:53
+     $PHP_BIN = Get-Command $phpTestBin|Select-Object  <<<< -ExpandProperty De
finition

php binary not found. Make sure php5 or php exists in PATH.

The problem seems to be with the "Select-Object" function.

This is the output of Get-Command "php.exe":

CommandType     Name                                                Definition
-----------     ----                                                ----------
Application     php.exe                                             C:\Program Files (x86)\PHP\php.exe

Best regards,
Alexander

@halfdan
Copy link
Member Author

halfdan commented Jul 9, 2010

Figured out that this is indeed an issue in v1 due to different behaviour of Select-Object. Can you guys check if the following line works?

(Get-Command "php.exe").Definition

Thanks for the detailed reports!

@mattab
Copy link
Member

mattab commented Jul 9, 2010

on an unrelated note, is there a powershell /windows feature to have all cron errors sent to an email, like unix cron? if so, could we document this in the doc page?

@halfdan
Copy link
Member Author

halfdan commented Jul 9, 2010

Attachment:
archive.windows.ps1.patch

@anonymous-matomo-user
Copy link

Hi Halfdan,

sorry for the late response but I was still in holiday :)

The

(Get-Command "php.exe").Definition

works fine without errors.

Your patch works great and now all data will be archived. Thanks for that!!! A function/feature like matt idea will be great if it is possible.

The first time (after my holiday) I start your patched script and it get an error somthing like memory problem of php (sorry I've forget to document it :S) after I delete my piwik data everthing works fine. The problem the script say everything is fine but one php call create the error and the response is not checked for success.

One more time many thanks for the patch!

@mattab
Copy link
Member

mattab commented Jul 22, 2010

I added link to the powershell download page on: http://piwik.org/docs/setup-auto-archiving

Would be nice to add the path auto detect feature, but closing it for now as it solves a big issue for Windows users. Nice one halfdan!

@mattab
Copy link
Member

mattab commented Jul 22, 2010

(In [2632]) fixes #1411

@anonymous-matomo-user
Copy link

Hi,

I have a last question. Is it possible to use a system var to get the php.ini path? I think that is more efficient to configure the system only one time. Yesterday I did the piwik update and I forget to backup the script and today I saw that the script doesn't work because there is the wrong php.ini path. I think if the script use a system variable for example piwik_archive_php everybody can add this variable to the system and the next update have no problems.

That's only a feature request the script is realy good and rescue my life :)

@robocoder
Copy link
Contributor

I believe php-cli checks the directory specified by the PHPRC environment variable.

@anonymous-matomo-user
Copy link

that's possible. when your script could take the same system var you can eleminatd the line:

$PHP_INI = "C:\Program Files\EasyPHP-5.3.2i\apache\php.ini"

in the script and every update of piwik will work perfect.

@halfdan
Copy link
Member Author

halfdan commented Jul 24, 2010

I'm looking into it ASAP guys. I know this issue exists and it bugs me.

When I wrote the script I discovered that php isn't using any php.ini (if it's not in C:\Windows). As a consequence the mysql extension wasn't loaded and archiving failed. That's why I put the .ini-path in the script. On Linux the php.ini path is somewhat standardised, so PHP isn't having that issue.

@mattab
Copy link
Member

mattab commented Jul 24, 2010

(In [2648]) Fixes #1184 Great patch by Julien Moumne to add Scheduled Task API in Piwik

  • possibilty to schedule daily/weekly/monthly tasks
  • tasks are executed via the crontab script for now (refs Archiving script: Port to Powershell #1411 should be updated to trigger the tasks as well)
  • features the first use case: a Monthly OPTIMIZE TABLE statement ran on all piwik archive tables (to defragment the space after we run the DELETE statements)
  • Next candidates: PDF reports by email, custom Alerts
  • comes his very serious unit testing

@mattab
Copy link
Member

mattab commented Jul 28, 2010

Important: the code must be updated for powershell since the archive.sh contains a new API call in it.

Can someone (halfdan is on holiday) please submit the change tested with powershell?

Check code at bottom of archive.sh: https://github.com/piwik/piwik/blob/master/misc/cron/archive.sh#L83

it would be great to have code by tomorrow to include it in next release with PDF plugin. Thanks

@mattab
Copy link
Member

mattab commented Jul 28, 2010

(In [2737]) Refs #5491

  • Scheduled PDF reports by email work as expected
    • fixed issue with current week used instead of last finished period,
    • fixed issue that all recipients were listed in the same TO: field, now sending one email per address.
    • Super user API methods will return all PDF reports by default, but UI now only displays PDF created by Super User.
  • Refs Plugin API for Scheduled Tasks #1184 Better logging of what task was ran and how long it took
  • The API call to run scheduled tasks must also be ported to Powershell refs Archiving script: Port to Powershell #1411

@mattab
Copy link
Member

mattab commented Feb 15, 2011

Would any windows users be able to port the few lines that we added in the Shell script?

Here is the diff that must be ported to Powershell:
http://dev.piwik.org/trac/changeset?old_path=%2Ftrunk%2Fmisc%2Fcron%2Farchive.sh&old=3661&new_path=%2Ftrunk%2Fmisc%2Fcron%2Farchive.sh&new=3907

The feature is to pre-process a list of given Segments during the Archiving Process. It would be important to port it to the powershell script so that both scripts stay consistent.

@mattab
Copy link
Member

mattab commented Jan 31, 2014

In 916d8d6: Starting January 2014, this script is now deprecated. Please only use the standard archive.php script in your scheduled task setup. See documentation at: http://piwik.org/docs/setup-auto-archiving/ Refs #1959 #1411 #2848

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Projects
None yet
Development

No branches or pull requests

4 participants