Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Creating Drill Down Reports in Notification Server

Created: 27 Apr 2009 • Updated: 27 Apr 2009 | 7 comments
Language Translations
Mike.Langford's picture
+14 14 Votes
Login to vote

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

imagebrowser image

2. Insert a new query at level one. This is done by clicking the Go button next to the command:

imagebrowser image

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

imagebrowser image

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:

imagebrowser image

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:

imagebrowser image

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.

imagebrowser image

Selecting that link will pull up the hardware information for the machine that you right click on in a new window:

imagebrowser image

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:

imagebrowser image

Apply your changes and re-run your report. Now you will have another new option from your right click menu:

imagebrowser image

When you select this option, it will take your computer model for the currently selected line and Google it:

imagebrowser image

That is all there is to it. I hope this helps!

Comments 7 CommentsJump to latest comment

haim96's picture

but if i remember right, for saved report this could be a problem
let's say that you have saved report that count you machines by OS.
then you have drill down to see all computers with specific.

when you open saved report the saved data will not update but the drill down data created on the fly.
so you got a situation when the drill down data not mutch the uper level data...
( there is also notification on that issue in the reports...)

i wish it was working just like in SMS, drill down is builtin in any report. you don't need to add it.

 

+2
Login to vote
Mike.Langford's picture

I figured ou how to create drilldowns that query data on saved reports.  It is a little complicated becase it involves querying XML with SQL, but you can pass the same types of parameters from the top level report to these queries as you would with normal drop down queries. 

I posted an article on querying XML and saved reports here: https://www-secure.symantec.com/connect/articles/q...

I have found since that this same technique can be used in drill downs to get data from a specific time period.
Again, it will take some creativity.

Let me know if you give it a shot and have any questions.

0
Login to vote
Eshwar's picture

Mike,
Thank you for your intension to share the knowledge with us. I really appreciate it.

I do create a lot of reports for auditing prpose. Sometime back I posted a question regarding automatic filtering option in reports. I haven't heard from anybody. I really don't know whether this is possible. But take a look

==================================================================================================
I'm creating an Altiris Report that displays Computer Name/User Name depending upon the following criteria:

1. REGION [DropDown Menu]
- Asia-Pacific
- North America

2. CITY [DropDown Menu]
- Delhi
- Mumbai
- Bangalore
- St Louis
- NewYork
- Chicago

So my requirement here is, when i select a particular "REGION" from the dropdown menu, "CITY" choice should be filtered automatically according to Region. So if i select "Asia-Pacific" from "Region", [Delh,Mumbai,Bangalore] should be displayed under the "City".

Any help is appreciated. Thank you for your time on this.
==================================================================================================

Cheers,

Eshwar

+5
Login to vote
Mike.Langford's picture

You are correct, that the drill downs do not work with saved reports.  The initial saved report will show the data from when it was saved, however, when you drill into it, the SQL behind the scenes is going to pull up current data from the database.  In some situations this may not matter if you are only concerned about history in the top level report, but either way, it is a good thing to keep in mind.  Thanks for pointing that out!

+2
Login to vote
Mike.Langford's picture

Unfortunately, you can't do what you are looking to do through the Altiris report builder.  You can get half way there and populate the city dropdown with values based on the initial value in the region drop down, but there is no way to re-load the city drop down when the region is changed.  I can think of a couple of work arounds, although neiter would be as nice as just being able to re-load the city drop down. 

The first would be to offer only a drop down box with City's and leave out the Region one all together.  You know what region they have selected based on the City they selected.  If you need that Region info in your report you could do something like this in the SQL

DECLARE @REGION varchar(20)
If %CITY% = 'St Louis' OR %CITY% = 'NewYork' OR %CITY% = 'Chicago'
BEGIN
SET @REGION = 'North America '
END
ELSE
SET @REGION = 'Asia-Pacific'

Select Distinct * From Table Where [City] = %CITY% and [Region] = @REGION

The second would be to have a multi layered report.  So basically when you run the report, you would not be prompted, but would instead see two lines  One that says 'North America', the other would say 'Asia-Pacific'.  You would then drill down into one of those and the drill down report would show citys that are in those regions.  Finally, you would drill into a city to see your final set of results.

I hope this helps.

+2
Login to vote
Eshwar's picture

Mike,
you are right. I tried your first option already and was half way successful. 

For the second option, i need to communicate this to the clients that this is a drill down report and you need to either double click on it or right click and select it. Tough job though.

Thanks anyway. I wish NS 7 has comeup with a better solution for this.

Thanks,
Eshwar
 

Cheers,

Eshwar

+4
Login to vote
popo21's picture

Hello,
i'd like to modify a global parameter according to the returned parameter from the drilldown.
is it possible?
i'd like to pass the value from the query level that recieves the value To a global parameter.
+
ESHWAR i found a solution to your problem, using drilldowns you can actually make the query lvl 0 a MENU table, that have two columns the first one is parameter the second the value, when the user right clicks on that table he can drill down to any parameter he likes

---------------------------------
Prameter   |      Value
---------------------------------
Region :         -- ALL --
City :                -- ALL --

when you right click you get :

REGION  DRILLDOWN
CITY DRILLDOWN
RUN DRILLDOWN

when the user picks to chose the REGION DRILLDOWN, a new table appears with all the region

---------------
REGION
---------------
Region A
Region B
Region C

when the user doble clicks one of the lines in this table you just drill down back to query level 0 and you set your region in the first table called menu

now you get
---------------------------------
Prameter   |      Value
---------------------------------
Region :         REGION B
City :                -- ALL --

now here's the trick  When the user choses CITY DRILLDOWN , you just send the parameter Region along with it.
and then u'll be able to filter the cities
you get
--------------------------
CITIES
-------------------------
CITY A FROM B
CITY B FROM B
CITY C FROM B

when the user double clicks one of those lines you drill down back to query level 0 with the city value

now you get

---------------------------------
Prameter   |      Value
---------------------------------
Region :         REGION B
City :               REGION A FROM B

Now all you have to do is pick the RUN DRILL DOWN from right clicking this table and you get ur results ( Run drill down calls another query level in which you select  whatever you want with the city and region the user have chosen )

**sorry for my english***

0
Login to vote