Archiving script: Port to Powershell

[halfdan]

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.

[matt]

Thanks halfdan.

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

[halfdan]

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]

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

[angel80]

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 = [System.Int32]::TryParse($x, [ref]$x2) return $isNum

}

Enjoy!

[matt]

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]

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.

[matt]

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?

[matt]

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!

[matt]

(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]

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).

[matt]

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 :)

[vipsoft]

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

[smithers]

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

[smithers]

...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]

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.

[smithers]

...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]

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?

[smithers]

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 ;)

[kane058]

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!

[Alexander]

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 enthält.
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]

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!

[matt]

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?

[smithers]

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!

[matt]

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!

[matt]

(In [2632]) fixes #1411

[smithers]

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 :)

[vipsoft]

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

[smithers]

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]

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.

[matt]

(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 #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

[matt]

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:  http://dev.piwik.org/trac/browser/trunk/misc/cron/archive.sh#L83

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

[matt]

(In [2737]) Refs #71

• 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 #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 #1411

[matt]

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.