ABECAS Insight

This is Part 3 of the TaskAgent & PowerShell series. Here you can find Part 1 and Part 2 posts. Check the Downloads section at the end of the post for the available source files and PDF version of the full post.

Scenario 3 – Web Dashboard powered by TaskAgent and Windows PowerShell

The third task which I wanted to automate involves the periodic update of an online dashboard which displays a summary of different types of business data – . This time I will use some T-SQL scripting from within the PowerShell script to pull the necessary data from the database. In addition, to make the script usable across different databases, I will be passing the database connection information into it via command-line parameters.  Here’s how this is done:

1.       We will start by creating the “frame” for our online dashboard. I will use a simple HTML file - dashboard.html  -which has just a heading text and a table with two rows and two columns – this will give us the four cells where the dashboard data will be presented. Each dashboard cell will be a separate web page represented by an IFRAME element. Here is the content of the dashboard.html file:

In addition to the dashboard HTML file, create four files for the different frames – frame1.html, frame2.html, frame3.html and frame4.html – the files can be simply blank or contain some default text to be displayed while there is no summary data. These files will later be automatically replaced by the TaskAgent with ones containing our live data. Here is how the directory containing the dashboard HTML files should look:

Here is how the empty dashboard should look while there is no data to display:

2.       Create the PowerShell scripts which will do all the processing.  We will use a separate script to update each dashboard cell. This will provide the flexibility of updating the different parts of the dashboard at different intervals (using different TaskAgent jobs) or using data from different data sources. The demo will use a single job to process all four tasks, but it is very easy to separate the tasks into different jobs with different schedules. You can use the ready files available from the download section – demo_webdashboard_frame1.ps1, demo_webdashboard_frame2.ps1, demo_webdashboard_frame3.ps1, demo_webdashboard_frame4.ps1

 

I will briefly explain what the purpose is of the different sections of the first script file. The scripts for the other frames are similar – only the SQL query and the frame HTML file which they update are different.

The first section of the script handles the input parameters. The scripts expects two parameters – the SQL server name (specified by /S:<server_name>)  and the database name (specified by /D:<database_name>). It checks if the required parameters are provided and extracts their values. If any of the required parameters is not provided, the script throws an exception with the appropriate message.

The second part of the script prepares a SQL command object and a SQL connection object and executes a SQL query to get the summary data. The section marked with blue is the SQL query which is different in each of the frame scripts. The database connection is created using the information specified by the command-line parameters. It uses integrated security (Windows authentication) instead of requiring username and password.

The last part of the script formats the retrieved SQL data in an HTML table and writes the results to an HTML file. The section marked with blue is the HTML file name which is different for each of the frame scripts.

3.       In the TaskAgent console, create a job with a time schedule (similar to the way it is done for the prior two scenarios)

4.       Create a task for the job created above and select “Run External Program” as the task type.

5.       On the “Task Settings” – “General” tab, in the “Program or File to Run” field, enter the PowerShell executable “powershell.exe” and in the “Command-line Parameters” field, enter the full path to the PowerShell script file. The difference here is that in addition to the script file, we provide as part of the command-line parameters the database connection information which should be used by the PowerShell script. In this particular case, we are providing only the SQL server name and the database – the PowerShell will use Windows authentication to connect to the database. This requires that the Windows credentials which will be used to execute the task have a corresponding SQL login on the specified SQL server. I chose to go this route to avoid entering in plain text the SQL server username and password. I recommend that you use this approach as well because it does not expose any secure information.

Note that the script input parameters are surrounded by single-quotes.

6.       On the “Security” tab provide the necessary Windows account credentials (which also have a corresponding SQL login account) if the TaskAgent service account does not provide them.

7.       On the “Execution Result” tab configure what the TaskAgent should do after the external program has been started.

8.       Repeat the steps 4 through 7 three more times – the only difference each time will be the PowerShell script file which you specify in the Command-line parameters field. The end result should be a job with four tasks – each task executing one of the four PowerShell scripts which we created in Step 2.

Do not forget to configure the job task workflow –  use the “Workflow” tab to specify how the tasks should be executed. The end result should be similar to this:

Save the job and give it a test run. Depending on your TaskAgent configuration it may take a minute or two to complete the execution of all four tasks. At the end, the updated web dashboard will look similar to this.

Depending on the job schedule, the TaskAgent will periodically update the dashboard with the latest information from your database. You can even configure the different tasks to pull information from different databases – this way the dashboard will be presenting on a single page summary information from multiple data sources. By modifying the SQL queries you can present different summaries. You can even add new “cells” to the dashboard to expand the presented information. The current style of the dashboard is not perfect and requires the attention of a web designer but I hope that at least its business value is clear.

Downloads

PDF version of the full post is available here: TaskAgent & Windows PowerShell

The demo source files are available for download here: Sample PowerShell scripts and other source files from the demos

This is the second part of a series of posts related to the TaskAgent and Windows PowerShell. You can find the first part here: TaskAgent & Windows PowerShell - Part 1

Check the Downloads section at the end of this post for a PDF version of the full post and related source files.

Scenario 2 – Check the available disk space and send an e-mail notification

This scenario attempts to automate a standard system administrative task by periodically checking the available disk space on multiple servers and sending e-mail notifications if the free disk space is below a certain limit. To accomplish this I will use again a Windows PowerShell script which will collect the necessary information using Windows Management Instrumentation (WMI) objects and send the notifications using .NET objects. Lets start:

1.       We will start by creating the PowerShell script. For your convenience, you can download the source for this demo and use the file demo_checkdiskspace.ps1

I will briefly explain what each part of the script is doing:

In the first section we just declare the list of computer names which should be checked and an empty array variable to store the results. Remember to change the list of server names to something which applies to your network.

In the second section the script iterates through the list of server names and uses the WMI object Win32_LogicalDisk to collect the disk drives information (marked with red).  Since the servers may have multiple drives,  the script iterates through the disk drives information of each server and adds to the result information the server name, calculates the percent free disk space and uses the calculated percentage to place the server disk drive one of the following 3 groups: less than 20% - CRITICAL, less than 35% - WARNING and everything else – OK (marked with blue). You can change these percentages to different levels if necessary.

The third part of the script further filters the results collected so far and leaves only the ones with CRITICAL or WARNING status. You can omit this step if you would like to send in the e-mail a report which includes information for all disk drives, not only the ones which are running low in free disk space.

The last section of the script checks if there are any results and formats them in a nice report which is written to a text file (marked with red). The script then uses a .NET object to prepare an e-mail message and send the results in the body of the message (marked with blue). Remember to change the From and To e-mail addresses. If your SMTP server requires authentication, you can also specify the necessary username and password.

2.       Create a TaskAgent job and add a time event for it. (see Scenario 1 for more details how to do this)

3.       Add a task for the job which we created above. The task type should be “Run External Program”.

4.       On the “Task Settings” – “General” tab, in the “Program or File to Run” field, enter the PowerShell executable “powershell.exe” and in the “Command-line Parameters” field, enter the full path to the PowerShell script file.

5.       On the “Task Settings” - “Security” tab, you can provide the Windows credentials which the TaskAgent should use when executing the external program (in our case this is the powershell.exe). By default, the TaskAgent will use the same credentials as the ones which the TaskAgent service is using. Our PowerShell script is using WMI to interrogate multiple servers for their disk drives – this requires administrative permissions which the TaskAgent service account may not have. If this is the case, on the “Security” tab enter the credential for a Windows account which has the necessary permissions.

6.       On the “Task Settings” – “Execution Result” tab, you can specify what the TaskAgent should do after the external program is successfully started. In this specific case, I have configured the task to wait for 20000 milliseconds (20 seconds) for powershell.exe to finish its execution. If the powershell.exe does not finish within the allocated period, the TaskAgent will terminate the external process. In addition, the TaskAgent can examine the exit code of the external process to determine whether the execution was successful or not. In this specific case, I have configured the task to accept only 0 as a successful exit code.

Save the job and test it. If everything is correct and some of the servers in your list are running low on free disk space, you should get a report containing information similar to this:

Not bad for a short script – by integrating the TaskAgent with PowerShell, WMI and .NET we can automate a multitude of administrative and business tasks.

Downloads

You can download a PDF version of the full post here: TaskAgent & Windows PowerShell

The related source files are available for download here: Sample PowerShell scripts and other source files from the demos

­During the Argos Software users’ conference in October 2008, I presented a session on how to extend the standard TaskAgent functionality using scripting with T-SQL and Windows PowerShell. In several blog posts I will try to make the examples and materials from that session available for all Argos customers. Check the “Downloads” section at the end of this post for the demo source files and other available downloads.

Prerequisites

You will need to install the latest version of the TaskAgent (available for download on our FTP server here: ftp://ftp.abecas.com) and Windows PowerShel version 1.0 (available for download here: http://www.microsoft.com/downloads/details.aspx?familyid=6CCB7E0D-8F1D-4B97-A397-47BCC8BA3806) in order to be able to use the examples below.

Scenario 1:  Automate file and directory maintenance

One common task which I have to do quite often involves maintenance of different files and directories. I wanted to configure a simple TaskAgent job which will monitor a specific file system directory every 15 minutes and will delete files which match certain criteria – in this specific case, delete all files which have been created more than 8 hours ago. Setting up in the TaskAgent a job schedule which triggers the job execution every 15 minutes is very easy. However, configuring a task to delete the necessary files is not straight forward. Initially I tried to do this with a simple batch file, but getting the current system date and time and using it to filter files proved to be quite difficult. After some research I found an easy answer which was using the newest scripting offering from Microsoft – Windows PowerShell, which is a standard part of Windows Server 2008 and is also available for Windows XP and Windows Server 2003. With PowerShell, all I needed to write is a single line of script code to achieve my task goal:

dir “D:\Temp” | ? {$_.CreationTime -lt (get-date).AddHours(-8)} | del –recur

This single line of code performs the following:

1.       Retrieves the list of all files and sub-direcotries in D:\Temp

2.       Iterates through the list and checks if the creation date & time of the file/sub-directory is more than 8 hours ago

3.       Each file/sub-directory, which matches the criteria above, is deleted and in the case of directories, all sub-directories and their content is deleted as well.

It is beyond the scope of this blog post to provide a full explanation and reference for using the Windows PowerShell – you can refer to the multitude of available online resources for that (one such reference can be found here: http://channel9.msdn.com/Wiki/WindowsPowerShellWiki/). However, I do want to show you how to integrate the PowerShell scripts in the TaskAgent to extend its functionality and automate a wide variety of tasks.

Here is what we need to do for our single-line script above:

1.       If you have not installed the Windows PowerShell on the TaskAgent server, do so.

2.       Open the PowerShell command prompt, enter the following command:

Set-ExecutionPolicy RemoteSigned

and press “Enter”.

This command will enable the execution of PowerShell script files which are created on the TaskAgent server, because by default this option is disabled to prevent the execution of malicious scripts. (note: Windows PowerShell provides also options to enable only the execution of digitally signed scripts – you will need to have a certificate which can be used to sign your scripts). If you enter the command

Get-Help Set-ExecutionPolicy

You will get detailed description for the available options.

3.       Create a simple text file using Notepad, copy and paste the script line above into your text file and save the file in a directory accessible by the TaskAgent using as filename demo_deletefiles_bycreationdatetime.ps1.

Note the extension .ps1 – by default, this is the extension used for PowerShell script files. If you are using Notepad to create the script files, when saving the files make sure that you specify in the “Save As Type” the option “All Files” – otherwise Notepad will append the default .TXT extension to your filename.

You can use any text editor to create or modify the script files. The only requirement is to save the files as plain text ASCII or Unicode files. My favorite text editor is UltraEdit – it provides among other things color syntax highlighting for the PowerShell scripts which makes it easier to edit them. In the screenshot below, there is an extra line which starts with # - this designates a comment in your PowerShell script file. Such lines will be ignored during the script execution, they are used only to document your script code – very useful for the future script maintenance.

4.       Open the TaskAgent console and make sure the RunProgramWorker is registered and enabled – this is the worker which will be used to process the script tasks

5.       In the TaskAgent console, create a new job and add a date & time schedule for it – for this example I used a time schedule which is triggered every 15 minutes, every work day from 8am to 5pm – feel free to set the time schedule as you see fit. (note: to test the script task you do not even need a schedule – just a job with the necessary task, the job can be triggered manually using the option “Run Job Now”)

6.       Add a task for the job created above – for the task type, select “Run External Program”

7.       On the “Task Settings” – “General” tab, in the “Program or File to Run” field, enter the PowerShell executable “powershell.exe” and in the “Command-line Parameters” field, enter the full path to the PowerShell script file.

As a rule, I always surround the file paths with double-quotes to avoid errors if the file path contains spaces.

8.       On the “Task Settings” - “Security” tab, you can provide the Windows credentials which the TaskAgent should use when executing the external program (in our case this is the powershell.exe). By default, the TaskAgent will use the same credentials as the ones which the TaskAgent service is using.

9.       On the “Task Settings” – “Execution Result” tab, you can specify what the TaskAgent should do after the external program is successfully started. In this specific case, I have configured the task to wait for 20000 milliseconds (20 seconds) for powershell.exe to finish its execution. If the powershell.exe does not finish within the allocated period, the TaskAgent will terminate the external process. In addition, the TaskAgent can examine the exit code of the external process to determine whether the execution was successful or not. In this specific case, I have configured the task to accept only 0 as a successful exit code.

This is all – now save the job and we are ready to test it! You can manually trigger the job by right-clicking on it in the TaskAgent console – Jobs list and selecting the option “Run Job Now”.

Online Resources

Below is the list of some of the available online resources which I have used.

•        Windows PowerShell

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx

http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/start.mspx

http://channel9.msdn.com/wiki/windowspowershellquickstart/

•        Batch Files

http://commandwindows.com (Great One!)

•        SQL Scripts

http://msdn.microsoft.com/en-us/library/bb510741.aspx

The example above is a simple one. In the following posts I will  show more elaborate PowerShell scripts.

Downloads

A PDF version of the contents in this blog post is available for download here: TaskAgent & Windows PowerShell

A ZIP file containing the demo source files is available for download here: Sample PowerShell scripts and other source files from the demos

If a Task Agent job appears to be “stuck” in a processing state and cannot be disabled or modified, perform the following steps to forcefully reset the running state of the Task Agent job.

Important Note: Some Task Agent jobs may require longer period of time to complete their processing. Proceed with the following steps only after the Task Agent was not able to completed the job (with either success or failure result) within a reasonable amount of time.

Step 1. Stop the Task Agent service on the local machine and any other machine where the Task Agent service is installed. You can view the list of running Task Agent worker processes by selecting the “Task Queues” node on the Task Agent Console menu tree.

Important Note: Failure to stop all Task Agent services while performing the following steps may result in Task Agent jobs or tasks to be left in an unstable state.

The column “Machine Name” of the Task Workers Summary section contains the machine names where a Task Agent service is running. Use the Refresh button to get the current list of Task Agent worker processes.

Step 2. In the Task Agent Jobs list, select the job which is in a running state and click the “View Job History” toolbar button (or right-click the job and select the option “View Job History” from the context menu)

Jobs which are in a processing state display as their “Last Run On” information the text “running now”. Use the “View Job History” option to display the job processing history dialog for the selected Task Agent job.

Step 3. The job history dialog will be displayed. In the list of job processing records, locate the record(s) which are not completed and whose Processing Result is “In Progress”. Once you have located the record(s) select them and press the Delete key on the keyboard (or right-click on the selection and choose “Delete Selected” from the context menu). You will be prompted to confirm the deletion of the selected record(s).

Important Note: If any log information is available for the processing record(s) which are about to be deleted, you can copy the text from the log messages and save it in a text file for future troubleshooting reference.

You can sort the list by clicking on the column headers to display the records which you are looking for at the top of the list. If none of the displayed records is still in progress, you can try showing more records by using a bigger result set for the option “Show the last…job processing records” and clicking the Refresh button.

Step 4. Since the job processing record indicates that the job is still in progress, a warning message will be displayed.

Important Note: Make sure that the deletion of the job processing records is performed while all Task Agent services (running on any machine) are stopped.

Step 5. Once the job processing record(s) are deleted, close the job processing history dialog and refresh the Task Agent Jobs list. The job will be reset and will no longer be in a “running” state. You will be able to disable or modify its configuration as needed.

Important Note: Be sure to start the Task Agent service(s) which have been stopped in Step 1 after the Task Agent job issue has been resolved.

A PDF file containing the information from this post can be downloaded using the link below:

Task Agent Job Troubleshooting

Hi all,

I just released a new version of the Task Agent. It is available for download from our FTP server for both ABECAS Insight 7.3 and 7.4

All changes in the new version of the Task Agent are related only to the Task Agent Management Console and does not affect the operation of the Task Agent engine. Here’s what changed:

• The Task Agent console now updates correctly the list of available database connections after the initial configuration is created. Before the Task Agent console had to be closed and reloaded in order the changes to reflected on the user interface.
• The ABECASInsightTaskWorker configuration screen now provides an option to set the Task Agent as the default handler of e-mail and fax tasks created by ABECAS Insight. Here a screen shot of the configuration screen:

Here is the About box with the new version information:

First of all I would like to thank everybody who could attend this year’s Users Seminar, which took place here in Fresno from the 24th to the 26th of September. It was great to catch up with Charlie, Ingrid, Barry, Beth, Tina, Rhonda, Bill, Lynn, and many other customers.

On Wednesday morning I gave a talk on the use of Task Agent with our “Enterprise” modules (Inventory, Purchasing, Order Management, etc.), and I’d like to talk more about it here, as well as provide the sample code.

Scenario
The scenario I went through was to automate the delivery of sales information to sales managers and also individual sales persons. They would receive an email every Friday, containing performance comparison between the current week of the current year and the same week of the prior year. The manager would receive a .csv (comma-separated value) file with data pertaining to all sales persons, and each sales person would receive an email with his/her own numbers.

Methodology and database considerations
In order to achieve this I used Task Agent to run an “Execute SQL Script” task.
The script requires two functions that already exist in your database if you’ve updated it using a setup after 9/26/2007. Otherwise, you can add them manually.
You can get the main query and these 2 companion functions in the zip file located here.

The functions are:
>> fnAB_WeekOfYear: returns the week # given a date (from 1 to 52)
>> FnOM_GetHeaderTotalAmount: Returns the total amount for a given Sales Order

The main query returns the following columns:
ParticipantIdentityID,
ParticipantName,
RecipientEmailAddress,
ThisWeek_ThisYear,
ThisWeek_LastYear,
Difference

Although the main query is not harmful (it’s a simple select statement), please make sure to read carefully the comments on the top of it to understand its mechanism and filtering criteria.

Bird’s eye view of the complete Task Agent solution

Tasks
We’re using 2 “Execute SQL Script”-type tasks. They both run the same query and the only difference is the type of delivery. The first one delivers individual emails to each sales person, whereas the second task sends one single email with all the information to the sales manager.
Here’s an image to illustrate where you paste the query:

First task - emailing individual recipients
The first task emails the results to individual recipients and the information needs to be pertinent to that particular person. We do that by:
a) Set the Results Storage Type to MailMerge Format
b) Set the MailMerge Address to a specific column retrieved by the query (in this case the column called “RecipientEmailAddress”.

c) Leave the email recipients blank
d) In the email message tab, write your message, referencing the columns retrieved using tags (surround the column title with < >). Red arrows are pointed to these tags in the picture below.
e) Optionally, you can format your message using standard HTML tags, like the ones shown in blue in the image below.

Second task (task #3) - emailing the sales manager
To send one single email with all the information to one single recipient, we need to:
a) Set the storage type to be Comma-Delimited
b) Specify the manager’s email address
c) Do not use tags in the email message (standard HTML tags are still OK to use).

Conclusion
Task Agent is a great tool which really gives wings to our imagination. Although this was an example on a useful application, it barely scratches the surface on the use of this tool. I envisioned the job described here to be run on a weekly basis, but bear in mind that there are other “triggers” for jobs, such as data changes and system changes.
Also, additionally to SQL scripts, you can run reports, Abecas Insight programs, any external program, schedule FTP uploads and downloads, and much more.

Please send us feedback about this on any other topic, and if you have any questions, don’t hesitate to contact our support department (support at argosoftware dot com) or me directly (gustavoc at argosoftware dot com).

---

Gustavo Cavalcanti
Product Manager - EMS
Argos Software