When the boss emails you looking for metrics, how much time does it take you? It takes too long, in my opinion. I want to double click on something and generate a spreadsheet that I can email right back to him. Sure, most of our metrics are automated emails setup using notification policies, but what about those times you just need a one off report? In these articles, I use two of my favorite automation tools, VBscript and the ASDK to create scripts that bypass using the console completely for running reports.
You will need ASDK 1.4 installed on both the Notification Server and any clients you where you will be running the script.
Looking for parts
The first thing we need to do is look at what the ASDK has to offer. In the case of reporting, there are actually two methods of accomplishing this. One way is to use the ReportManagementLib class. This has two available methods, "RunReport" and "RunReportWithParameters". The other method available is under the ItemManagement lib class, called "ExecuteNSDatabaseQuery". The ability to run a direct SQL query offers tremendous flexibility but obviously requires some knowledge of the schema as well as SQL scripting. Using the ASDK's reporting methods is a bit simpler, but you are limited to canned or custom reports. These articles will walk through using both methods.
The 3 methods available are outlined in red.
The other consideration is what will the script be spitting out? Since most metrics are handed over in the form of a spreadsheet, I will be using an Excel object in the script. This is another area where I can be as flexible as I want. I don't have to spit out exactly what a report returns, nor do I have to keep it in the same format. Maybe I want to combine results from several NS's into one meaningful spreadsheet? Maybe I want flow control to determine what type of information I return? Once you have the flexibility of the ASDK, combined with the ease of VBscript, you can create reports that would otherwise be impossible using normal NS console tools.
Putting the parts together
Now it's time to get down to the good stuff. The code!!! In this example, I will build a simple script that runs the "Packages - All" report, which is included with Software Delivery Solution. This report is located under "\Reports\Software Management\Software Delivery\Windows\Packages/Programs". This is a great example for this article. I find myself running it a lot, but don't really have a need to automate it in a policy.
But first a little high level outline of what this script will be doing:
- Stage the environment- create some objects will be used throughout the script
- Execute a report and trap the results
- Loop through the results and send them to a spreadsheet.
Staging the playground
The first part of the script will be setting up the environment:
svrname = inputbox("Enter the Server Name", "Server Name")
set reportManagement = CreateObject("Altiris.ASDK.NS.ReportManagement")
reportManagement.TargetServer = svrName
reportManagement.Authenticate()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Publisher"
objExcel.Cells(1, 2).Value = "Package"
objExcel.Cells(1, 3).Value = "Version"
objExcel.Cells(1, 4).Value = "Source"
objExcel.Cells(1, 5).Value = "Last updated"
objExcel.Cells(1, 6).Value = "GUID"
objExcel.Range("A1:F1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Line 1 is simply a popup box to grab the NS you are connecting to if you have a multiple NS environment. If you wanted to just define one NS and be done, it could be changed to:
svrname = "servername.mycompany.com"
Line 3-5 set the stage to use an ASDK class called "Report Management". This is how the RunReport method is called later in the script. This block creates the object you will use to actually run the report, defines the server you are connecting to, and authenticates by passing your current Windows credentials.
Line 6-20 creates the spreadsheet. Line 6 creates the object, named "objExcel". This object will be used to set the spreadsheet properties or invoke available methods. Line 7 is simply making the spreadsheet visible. When the script is run a blank Excel sheet will open. Line 8 is where a blank workbook is attached to the Excel window. Line 9-14 is creating the column headers. Notice these lines here. The parameters for the "Cells" object are indicating the cell's location. The first number being the row, and the second is the column. The "Value" property then fills the cell with the string assigned to it.
Line 21-25 is then simply adding some formatting. First, select a range of columns, highlight the headers and last, force all data to auto fit the columns. This block is not needed for functionality, but it sure does make for some pretty reports.
Execute a report and trap the results
What's the point of this entire script if it doesn't "do" something? Now that everything is staged and ready, let's call on these objects to do our bidding:
set results = reportManagement.RunReport("5093c1f3-ae91-4f38-8093-7488b1ea9f37")
if results.Fields.Count > 0 then
results.MoveFirst()
intRow = 3
Line 26 is the real meat of this entire script. The "RunReport" method takes a single parameter, the GUID of the report you wish to run. In this instance the canned report, "Packages-All", has a GUID of "5093c1f3-ae91-4f38-8093-7488b1ea9f37". Once executed, the results are returned as a record set into an object called "results". At this point, the script can loop through the results and manipulate the data. For more info on using the record sets returned from the ASDK, there is a nice little section with examples titled "DataTableConversions" in the CHM.
Line 27 is a quick check and encloses the rest of the script in an "If Then" condition. I'm simply checking to see if something was returned or not. If "results" is empty, the script will simply skip over looping through the variable.
Line 28 moves the data stream to the first item in the results.
Line 29 is defining and setting the "intRow" variable, which is used to tell the script which spreadsheet row we are currently on so it knows where to write the data to. Otherwise, the script would just loop through and overwrite the same row over and over. During the final part of this script, "intRow" will be increased by 1, during each loop iteration.
The CHM has examples of using data tables/sets using C# or VBscript
Loop through the results and send them to a spreadsheet
Now to reap the rewards! We have our data just sitting there in an object called "results", but it has nowhere to go. The final part of this code will take the data and send it to a nice formatted spreadsheet.
do while not results.EOF
pubName = results.Fields.Item("Publisher").Value
pkgName = results.Fields.Item("Package").Value
version = results.Fields.Item("Version").Value
source = results.Fields.Item("Source Location").Value
lastUpdate = results.Fields.Item("Last Updated").Value
pkgGUID = results.Fields.Item("Package Id").Value
objExcel.Cells(intRow, 1).Value = pubName
objExcel.Cells(intRow, 2).Value = pkgName
objExcel.Cells(intRow, 3).Value = version
objExcel.Cells(intRow, 4).Value = source
objExcel.Cells(intRow, 5).Value = lastUpdate
objExcel.Cells(intRow, 6).Value = pkgGUID
results.MoveNext()
intRow = intRow + 1
loop
end if
Could this be any easier? Line 30 is nearly self explanatory; run through this block of code while the object "results" is not empty. In other words, go through each line in the recordset until there are no more rows of data and then exit this loop.
Lines 31-36 is simply to keep the code clean. Earlier I stated the data is returned in the form of a recordset. If you visualize this in your head, it would look just like a table in a database or the results in a report when run in a console:
Publisher | Package | Version | Source Location | Last Updated | Package ID
Microsoft | IE7 | 1 | \\mysource\software\ | 5/27/2009 11:40 | 22F23DB1-8E76-45A3-8289-9B571C5DF279
Using the example above, the first time this script goes through this loop, the first row of data would be the IE7 package. If I want to find out the values of each column, I need to call the column by name. The "fields" object takes this column name as a parameter. The string is then placed into a variable. The next block of code is where the actual values are fed to the spreadsheet.
Line 38 - 43 is taking the variables assigned in the last block and placing those values into the spreadsheet. Let's take a look at line 38 and go over what it's doing:
objExcel.Cells(intRow, 1).Value = pubName
Earlier you learned the "Cells" object takes 2 location parameters. The first is the row. Since I know there will not be just a single row, this parameter needs to dynamically change depending on which record the script is on in the loop.
Using the same IE7 example I gave above, the first time the script runs through this loop, the value of intRow is already set to 3. This is simply to leave a blank row in between the headers and the first row of data. Since the values are already assigned to a variable in the previous block, I use those variables in this block. So line 38 would actually be evaluated as:
objExcel.Cells(3, 1).Value = "Microsoft"
Line 45 is moving to the next row in the recordset. Not much to explain here. Line 47 is where intRow is increased by 1. Continuing with the above examples, the second time the script runs through this loop, it will be writing to the 4th row on the spreadsheet.
Line 49 and 51 is wrapping everything up. I close the "Do Until" loop and I enclose the "IfThen" statement afterwards.
At this point, you have a working script. There isn't any error checking or feedback, but you can see how flexible this could be. Now you can double click and have a report in hand and ready. Tell your boss you were up all night slaving away to get those metrics on his desk first thing in the morning!
Wipe the sweat off your brow, this report only took 2 whole clicks!!!!
In Quicker and better NS reports using the ASDK - Part 2, I will go over passing parameters to reports. It's actually easier than you think, even if you don't know what parameters a report may have.
Attached is the full script. Hope this helps!