Report on Machines Up or Down

I have had several requests from clients who would like to have a report that lets them know if their desktops are powered up or powered down. Usually this is something that they would like to know first thing in the morning. Altiris provides this for servers, however there is nothing really available for desktops.

In this article I will discuss how to utilize the standard ping.exe to determine if a collection of machines are responding or not responding (i.e., powered up or down) and report on it.

I will be using the following tools from Altiris:

• Task Server (to run the job)
• Connector Solution (to import/export results)
• CMDB Solution (to create a custom data class)

The high level steps are going to be as follows:

• create a server side job in Task server that has 3 steps:
  1. Export a list of machines from a collection to a file.
  2. Run a script that reads in the file line by line and executes a ping on them and returns the result into another file.
  3. Import the contents of the ping result file and populate user defined fields for ping status to the corresponding machines.
• Run a report that keys off the user defined fields for ping status.

First off we will need to create a user defined data type with 2 fields. Go to Configuration-->Resource Settings-->Data Classes-->User Defined. Right click on User Defined and select "Editable Data Class".

Click to view.

Name the data class "Ping Status" and the Data Entry Tab Name "Ping Status" as well. The last step is optional. What the last field will do is create a new tab so when you are editing an asset you will have a separate area to view/edit this field if you wish.

Now click on "Add New Attribute." Enter in the following information:

Name: Ping Result
Type: String
Size (optional): 4

Click apply and then add another attribute with the following information:

Name: Ping Date
Type: Date

You should see the following:

Click to view.

Next we need to associate the new data class to computers.

To do this go to Configuration-->Resource Settings-->Resource Types-->Asset Types-->IT-->Computer. Towards the bottom of the screen under the Configuration tab click on "Add Data Classes". Once the window appears navigate to where you created the new data class. It should be under Configuration-->Resource Settings-->Data Classes-->User Defined. Once it is selected you should see the following:

Click to view.

Now we have a new data class with 2 attributes associated with a computer. We will use these fields to populate the results from the ping.

Next we need to create the Connector Solution rules.

Navigate to Configuration-->Solution Settings-->Connectors-->Data Sources. Right click and select "CSV File Data Source". Populate the fields with the following:

Name: Computer Collection CSV
Delimiter Character: Comma
Allows Imports: Check
Import File: c:\output.txt
Allows Exports: Check
When Performing an Export: Write data to a specified file
Export File: c:\computerexport.txt
Create the specified file if it does not exist: check

Click to view.

Now we need to define the import/export rule to export a collection of computers.

This will be the input to our ping script which we will define a little later. Right click on "Import/Export Rules" and select New-->Resource Import Export Rule. Enter in the following fields:

Name: Collection Export - CSV
Data Source: Computer Collection CSV (this is what we created in the last step)
Replication Direction: Export
Resource Type: Computer
Export filter: Collection (then select which collection of computers you want to test with)

Select Column Mapping. Click on "Select Data Class". When the selection window appears click on Configuration all the way at the top, wait for everything to be selected, and then click on Configuration once more to deselect everything. Click on Configuration-->Resource Settings-->Data Classes-->Basic Inventory-->AeX AC Identification. Next clear all the values of the fields except for "Name" and enter "Name" in the source column. It should look like the following:

Click to view.

What we are doing here is selecting which field associated with a computer that we want to export. It will be this field that we will ping against. I've chosen this field, Name, for a simplistic example. In your environment you may want to choose Name+Domain.

Now we need to define the import/export rule for importing the ping results. Before we do this we need to copy the attached file called output.txt to your c:\ directory. The reason for this is that the import rule will look to its source for fields to map to. Since we have never run the ping script (coming up below) the rule does not know what fields to map to. Output.txt has the fields defined in the first row. Once you copy the file right click on Import/Export Rules and select once more New-->Resource Import Export Rule. Fill in the following fields:

Name: Ping Status Import
Data Source: Computer Collection CSV
Replication Direction: Import
Resource Type: Computer
Allow Resource Update: Check
Removed Assets should be: left unchanged in the NS

Select Column Mapping. Click on "Select Data Class". When the selection window appears click on Configuration all the way at the top, wait for everything to be selected, and then click on Configuration once more to deselect everything. Click on Configuration-->Resource Settings-->Data Classes-->User Defined-->Ping Status. Fill in the following fields:

Resource Lookup Key (required): Resource Name
Name: name
Resource Name: name
Data class mappings:
Ping Result: PowerUp
Ping Date: date

It should look like the following:

Click to view.

Now we will create the Task Server Job to run the import/export rules along with the ping script. Go to Manage-->Jobs. Right Click anywhere under Tasks and Jobs and select New-->Task/Job. Select "Server Job." Call it Ping Job.

Click on New-->Task.

Select "Execute Connector Rules". Name it Execute Connector Rules - Collection Export CSV

Click on "Add Rule" and select "Collection Export - CSV"

Click on New-->Task and select Script. The Script Type will be VBScript. The VBScript will be the following:

On Error Resume Next

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
  ("c:\ComputerExport.txt", ForReading)


Set objFS = CreateObject("Scripting.FileSystemObject")
Set objNewFile = objFS.CreateTextFile("c:\output.txt")

Set objShell = CreateObject("WScript.Shell")

objNewFile.WriteLine "name,PowerUp,date"

Do Until objTextFile.AtEndOfStream
 
 strNextLine = objTextFile.Readline
 Set objExec = objShell.Exec("ping.exe -n 2 -w 1000 " & strNextLine)
 strPingResults = LCase(objExec.StdOut.ReadAll)
 If InStr(strPingResults, "reply from") Then
  objNewFile.WriteLine strNextLine & ",Yes," & now() 
 Else
  objNewFile.WriteLine strNextLine &",No," & now()
End If
Loop


objNewFile.Close

This script will take c:\computerexport.txt as input and iterate until the end of the file. Each iteration will write the name, ping result (Yes or No), and the date/time that the ping was done. It will write to c:\output.txt.

Click on New-->Task

Select "Execute Connector Rules". Name it "Execute Connector - Ping Status Import"
Click on Add Rule and select "Ping Status Import"

It should now look like the following:

Click to view.

Now you should run this job so that information will be imported into the DB. Click on "Run Now" and select your NS to run it against.

Finally, let's create a report that will display this information. Go to View-->Reports, right click on where you would like to report to be saved and select New-->Report. Enter the SQL in directly with the following SQL:

select [Ping Result], count([Ping Result]) as 'Number of Machine' 
from dbo.Inv_Ping_Status 
group by [Ping Result]

I prefer to show this as a graph. To do so click on the graph icon to the right of the SQL statement.

Click to view.

Select Graph and set it as the default view. The values that I have for the graph are the following: (if I don't list a field it means it's blank or unchecked)

Graph Title: Machine Ping Response
Row Label Stagger: 0
Show Row Labels: checked
Show Column Labels: checked
Graph Type: ColumnChart
Width: 700
Height: 600
X Rotation: 164
Y rotation: 12
Z rotation: 0
Scale: 65
Legend Enabled: checked
Legend transparency: 150
Legend location: top
Legend span percentage: 25
Label Column: -1
Included Columns: -1

I included a drill down for the report to list the computers. To do this click on "Insert new query at level 1." Edit the SQL directly and enter:

SELECT   Inv_AeX_AC_Identification.Name, Inv_AeX_AC_Identification.Domain, Inv_AeX_AC_Identification.[OS Name], Inv_AeX_AC_Identification.[OS Type], 
           Inv_AeX_AC_Identification.[OS Version] 
FROM     Inv_Ping_Status INNER JOIN 
           Inv_AeX_AC_Identification ON Inv_Ping_Status._ResourceGuid = Inv_AeX_AC_Identification._ResourceGuid 
WHERE   (Inv_Ping_Status.[Ping Result] = '%Ping Result%')

Finally we need to configure the drill down at level 0. Click on the drill down icon to the right of the sql query at level 0.

Click to view.

Enter the fields as per the screen shot below:

Click to view.

And you're done! The final report should look like the following:

Click to view.

A couple things to consider:

1. You may want to change the ping command to accommodate for proper timeouts, etc.
2. You can schedule the task to run every morning
3. You can add a 4th task to the job to run the report that we created and email it to someone.
4. I included the XML of the import/export rules, the report and the Task Server Job in this article for download (for reference)