The purpose of this document is to provide a walk through in creating a report in Notification Server that has drill down capabilities.
First, start by creating a new report on anything you like. For this document I will create a report that pulls a list of computers with their last logged on user, and then we will create a drill down that lets us query hardware information on any of the machines.
SQL for top level report:
SELECT Name,
[Last Logon User],
[Client Date]
FROM Inv_AeX_AC_Identification
Now that we have our top level report built we are going to create our drill down.
1. Select your new report in the NS console and click the pencil icon to edit your report
2. Insert a new query at level one. This is done by clicking the Go button next to the command:
(Level 0 is where your original report resides.)
3. Now you can either use the report builder or click the Edit SQL Directly button to build your drill down query. I am going to use the Edit SQL Directly button.
4. Enter the following SQL query in your level 1 report:
------------------------------------------------------------------------------
SELECT i.Name,
cpu.Speed 'CPU Speed',
hd.[Device ID],
hd.[Free Space in MBytes] 'Free Space in MB',
m.[Total Physical Memory (MB)]
FROM [Inv_AeX_AC_Identification] i
LEFT JOIN [Inv_AeX_HW_CPU] cpu
ON i._ResourceGuid = cpu._ResourceGuid
LEFT JOIN [Inv_AeX_HW_Logical_Disk] hd
ON i._ResourceGuid = hd._ResourceGuid
LEFT JOIN [Inv_AeX_HW_Memory] m
ON i._ResourceGuid = m._ResourceGuid
WHERE [Free Space in MBytes] > 0
And i.Name=%Name%
------------------------------------------------------------------------------
Notice that we have "%Name%" at the end of the query in the WHERE clause. This parameter is passed to this second SQL query from the results of the first query by the drill down. We will now set up the drill down to use this %Name% parameter.
5. On your level 0 query, click the Drill Downs button:
6. Click the Add Drill down button.
- Drill down to: - this is where you want to point your drill down. We are going to point it at another query level. You can also point it at things like other reports, but this is the cleanest way I have found to have an all in one report that holds all its own drill downs.
- Name: - this is the text that will be displayed when you right click a machine in your report to access your drill down report.
- Query Level: - this is the report that we will be drilling down to. You can have multiple drill downs by creating multiple query levels and then just point your drill down to the appropriate level. For this example, the query level will be 1. Remember 0 is your original report.
- Drill down parameters: - this is the parameter from the level 0 report that you are going to pass to the drill down. What it is looking for here is a column name from your original report. We will enter "Name" because we want to pass the value in the Name column of what ever row we right click on to the next report. You can pass as many parameters as you like by separating the column names with the "|" symbol. When referencing these parameters in your SQL drill down SQL, you place percent signs on each side as in the SQL query above i.e. %Name%
- Open drill down in: - this is simply the way you want your drill down to open. I usually use a new window so it is easy to navigate back and forth from the level 0 report to your drill down.
7. Fill out your form as I have below accept all your changes and click Apply:
Now you are ready to run the report. You will notice that you now have a View Hardware option at the top of your menu when you right click an asset.
Selecting that link will pull up the hardware information for the machine that you right click on in a new window:
Other Drill Down Options:
While the other query levels are probably what you will use 90% of the time, using some of the other drill down options, you can do things like automatically search for computer models out on Google, or using another search engine. To do something like this, let's add a bit of code to our SQL to include computer model and then create another drill down. Enter the following as the SQL of your level 0 query:
Select Name,[Computer Model],[Last Logon User],[Client Date]
From Inv_AeX_AC_Identification i Left Join Inv_AeX_HW_Serial_Number sn on i._ResourceGuid = sn._ResourceGuid
Now create a new drop down just as before, but this time point it to another web page instead of another query level. I am using Google for my example, so mine looks like this:
Apply your changes and re-run your report. Now you will have another new option from your right click menu:
When you select this option, it will take your computer model for the currently selected line and Google it:
That is all there is to it. I hope this helps!