Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

How-to Guide on the Report Builder

Created: 02 Oct 2007 • Updated: 08 Feb 2013 | 16 comments
Language Translations
Alex Held's picture
+9 11 Votes
Login to vote

If there's anything you ever wanted to know about the Report Builder and its Wizard (whether you were afraid to ask or not) this document probably has the answer.

Overview: Report Builder Wizard

Basically the Report Builder is a Web Users Interface that can parse together a SQL query and allow you to save it to the database. The following is for NS 6

  • Move to the folder you just created and right click and select New > Report.
  • This will open the Report Builder Wizard.
  • There are four sections to the Report Builder Wizard page.
  • There will be two texts box’s (Report name, Report description), both you can edit.
  • You can only select one of the four radio buttons under the Report type section.
    • Simple report
    • Summary reports
    • Advanced Report Builder
    • Enter SQL Directly.
  • And there are four contents to select from.
    • Data type: Resource type
    • Field: Tables and Columns
    • Criteria: Conditions
    • Sorted by: Columns

Simple Reports Builder

Resource Type (Categories)

  • This is the resource type of data you wish to report on.
  • You must select a Data type first. A good start is to select computer as data type.
    1. Select "—Select a Data types—" on the main page. This will open a window called "Categories – Web Page Dialog".
    2. Select the data type from the left pane and then use the "Add" button; this will move it to the right pane. This will grey out the right pane at that time. You can only select one data type.
    3. If you do select the wrong item from the left pane and then used the "Add" button, just select the item from the right pane and click the "Remove" button.
    4. Then use the "OK" button to close the window.
  • The display names come from this SQL query.
select rt.[Guid]

,isnull(dbo.fnLocalizeStringByGuid('item.name', rt.Guid, 'en-US'), rt.[Name]) as 'Name'

,count(cast(rtdc.ResourceTypeGuid as nvarchar(50))) as DataClassCount

from (((vResourceType rt

              inner join ResourceTypeDataClass rtdc on rt.[Guid] = rtdc.[ResourceTypeGuid])

          inner join DataClass dc on dc.[Guid] = rtdc.[ResourceDataClassGuid])

        inner join ItemReference ir on ir.[ChildItemGuid] = dc.[Guid])

inner join Item i on i.[Guid] = ir.[ParentItemGuid]

group by rt.Guid, rt.[Name]

order by rt.[Name] asc

Table and Column Selection (Field Selector )

  • This will show the tables and columns you wish to display.
  • In the SQL query, these tables names will show up under the "FROM" clause as joins and there conditions. The columns will up under the "SELECT" clause. The table’s alias … general notes
    1. Select this "- - Select field to display - -".
    2. This will open a window called "Field Selector–Web Page Dialog".
    3. Select the fields from the left pane and then use the "Add" button; this will move it to the right pane. The order you select is the order that will be displayed in your report.
    4. If you select an item you don't wish like a field or folder and not deselect before you click the "Add" button this will cause the item or items to be added to your left pane.
    5. If you do select the wrong item from the left tree pane and then used the "Add" button, just select the item from the right tree pane and click the "Remove" button.
    6. Then use the "OK" button to close the window.

  • The data comes from the MetaObject and MetaObjectColumn tables in the Altiris database. See general notes for the SQL query

Conditions (Criteria)

  • This will allow you to apply conditions in the SQL query based on column data.
  • This is an optional selection.
  • In the SQL query, this will show up in the “WHERE” clause.
    1. Select this "-- Select criteria (filtering) --".
    2. This will open a window called "Conditions – Web Page Dialog".
    3. Select the button "New Condition".
    4. There are three dropdown menus. (from left to right)
      1. The first dropdown will be the fields ([Table Name].[Column Name]) you selected from the field selector in the earlier step.
                i. Also in the same dropdown box at the bottom there will be the selection of "More…" This will allow you to select other columns not being displayed.
      2. The middle dropdown is used for the conditions you will use. See notes.
                i. This is a fixed list
      3. The last dropdown on the right is for the value you are testing.
                i. The default is %. See notes.
    5. If you wish to remove a condition, click red X at the far right side.
    6. Then use the "OK" button to close the window.

Sorting

  • This will allow you to sort by one or more columns. You can use ascending or descending.
  • This is an optional selection
    1. Select this "- - Select sort order - -".
    2. This will open a window called "Sorting -- Web Page Dialog".
    3. Select one of the three radio buttons first. (None, Descending and Ascending).
    4. I you select descending or ascending button, the field menu box will no longer be grey out.
    5. In the field menu box; you can select the field you wish to sort by.
    6. Then use the "OK" button to close the window.
  • In the SQL query, this section will be part of the “ORDER BY” at the bottom of the query.

  • Once you select the Apply button in the lower left of the report, the information is saved to the Item table using the stored procedure spItemSave.
  • Also the product GUID for any custom reports are all referenced to the Notification Server.

Custom Folder Creation (Organizing your custom reports)

  • Create a folder to hold your custom reports.
  • Right click the Report folder under the Reports Tab.
  • Select from the menu New > Folder. This will open a window called “New Folder”.
  • Enter name and then click "Apply" button.
  • Your newly created folder will appear in the left tree pane under report. This will be in alphabetical order after a refresh.
    • A good start will be to use the name Custom Reports, but any name is possible.
  • You can use subfolders to further define your report collection.
  • This categorizing of your folders will help you organize. The types will depend on your interest.
    • Example 1: Table type you are pulling from; Standard, Custom, Mixed.
    • Example 2: Application based structure: Asset, Inventory, App Metering …etc.
    • Example 3: Company divisions, department, geographical location …etc.
    • Example 4: Task based structure
  • You can also make a shortcut of these reports.

Other Report Types

Summary Report Builder

  • The “Summary” reports are very similar to a Simple report builder
    • But will be adding a “GROUP BY” condition under the “WHERE” clause
    • And a “count (*)” in the select clause of the SQL query.
    • Summary Report Steps
      • Main Page
      • Data type
      • Summary fields
      • Additional detail fields
      • Criteria
      • Display summary as
        • Grid
        • Chart (You can edit the chart type when you run the report)
      • Advanced… (This will appear when you edit after the initial report creation)
        • This will allow you to use the Advanced Report Builder

Advanced Report BuilderEnter SQL Directly

  • The “Advanced Report Builder” gives you greater latitude in a UI setting. You will need some SQL knowledge to use this effectively.
    • This type allows you to create parameters to be used to filter the report results.
    • You can use aggregate functions in the “SELECT” clause
    • You can use a “TOP” or a “DISTINCT” in the “SELECT” clause
    • You can filter against a collection in the last step (7 of 7 in the first time through)
      • Advanced Report Steps
        • Main Page
        • Table selection
      • Populate the “FROM” clause section
        • Table Join conditions (inner or outer joins)
        • Field Section (columns to be displayed)
      • Populate the “SELECT” clause section
        • Conditions
      • Populate the “WHERE” clause section
      • Parameter additions
        • Sorting and Grouping
      • Additions of (top, distinct, order by, and group by)
      • Parameter additions
        • Test your query
      • Filter on collections (exclusions and inclusions)

Enter SQL Directly

  • The “Enter SQL Directly” gives you the greatest latitude. It is only limited by your SQL knowledge, experience, and your imagination.
    • First create, edit, and test the SQL script in Query Analyzer.
    • Parameterize you variables to prevent SQL attacks.
    • Then paste the SQL query into the report (level 0) with any predefined parameters if needed.
    • Add any global parameters into the report UI (one by one, testing them)
    • Connect the global parameters into the report script to the variables and not to the query section directly. Testing one by one.
    • If needed, edit and test any other drilldown reports scripts (level 1, 2…) from Query Analyzer before adding then into the report.
      • The drilldown parameters come from the column names of the parent report and you will need to add them to the drilldown child report. See Notes
      • Connect the drilldown parameters to the child report.
      • If the drilldown is to the Resource Manager for a computer resource, there needs to a resource guid in the “Select” clause of the report SQL query.
    • Note that you are not limited to query just the Altiris database or even the local SQL server (trust relation between SQL servers).
      • On the first line of the query, use this “USE Database_Name” and just replace the “Database_Name” with a real database name.

Parameter Notes

  • Parameter Scope
    • Local parameter accessed by level 0 report
    • Global parameter accessed by all levels of the report
  • Parameter name
    • Passing name you use in the SQL query to filter the results
  • Parameter prompt
    • Display name in the report to the parameter box.
    • Non prompted parameters values can be passed to prompted parameters as a default value.
  • Parameter types (12 types)
    • Basic
      • Value types (String, Number, Date/Time, Money, or None)
      • Default value
    • Combobox
      • Combination of a basic type and a dropdown type
      • Value types (String, Number, Date/Time, Money, or None)
      • Default value
    • Constant
      • Fixed value, not prompted at evaluation time
    • DataTable
      • Table types (Inventory, Event, or DataBase)
    • Date/Time
      • Types (Long Date, Short Date, Time, or Custom Format)
    • Dropdown
      • Fixed List (Delimiter is “|” and display and pass value form is “Item;Value”)
      • Query Results (Two columns allow for a display and a passing value)
      • Value types (String, Number, Date/Time, Money, or None)
    • Group Memberships
      • User or group name.
    • HTTP Variable
      • The http parameters correspond to the headers contained within a web page request and are extracted from the calling page at evaluation time.
    • Item picker
      • Class filters (Asset, Collection, Company…)
      • It is not possible at this time to create a default resource in the UI.
      • By editing the exported XML file, you can add a default resource.
    • Query
      • Value types (String, Number, Date/Time, Money, or None)
    • Registry
      • Retrieves value from the system registry
    • Runtime
      • Runtime parameters retrieve their value from the object currently being run.
      • Examples (report, policy etc)
  • Parameter passing between report levels
    • Passing parameter name(s) is the column name(s) of the parent level report
    • The passing parameter name needs to be in the child level report query.
    • The Level 0 “Column Name” passed to Level 1 “Parameter Name in the SQL query”
  • Item picker parameter have assembly references
    • The importing of an item picker reports into another NS may have trouble.
    • By editing the XML and changing the assemble reference it is possible to import the report into another NS (Your OwnerNSGuid will be imported into the other NS as well).

Combining Report (drilldowns)

  • Connecting queries and reports (views) with drilldowns will help you manage your resources and events
  • These are the possible steps you can take.
    • A summarize view of events and/or resources
    • A filter view to compartmentalize/ limit events and/or resources
      • Filter on a collection (location, department, set of users …)
      • This step can be sub-divided into multiple queries and/or report
    • A general view of an event or resource
    • A detailed view of an event or resource
      • Query (Level)
      • Report
      • Resource Manager
    • A policy action to manage an event or resource
      • The policy action can happen at any level, depending on scope.
      • Solutions do this for you, but not all possible actions.

  • This form of organizing queries and reports allows a strategic view that you could drilldown to a tactical view of events or resources.
  • The possible short coming is that a report may not be translated into a collection for a policy action.
    • A possible parallel drilldown to a report that can translate into a collection. See notes

General Notes

  • The use of the "LIKE" operator, can use two wild character ('%' and underscore '_') and is case insensitive (Big, big, and BIG is all the same).
    • Example: 'comp%', '%sup%' or 'comp__'.
    • The percent sign '%' is used for any number of characters including no characters
    • The underscore '_' is used for any single character, but must be one character.
  • The default to combine conditions is the AND operator.
    • Example: WHERE (Condition01 AND Condition02).
    • There is no use of the OR operator to combine conditions in a simple report.
    • You will need to use the Advanced Report Builder to change this or once created edit the SQL query directly. You can do this after saving the report
  • If you view the SQL query code …
    • In the "SELECT" clause the columns are from the Field Selector window.
    • In the "FROM" clause the tables are generated based on the fields (tables) you selected. If you have selected fields from more than one table, there will be "INNER JOIN" in the code. As well as the use of the "ON" SQL word to indicate the two unique fields to be equated.
    • In the "WHERE" clause the items are added from the Conditions Selector window.
    • In a Summary report, the summarized fields (columns) will be in the “GROUP BY” clause.
    • If you need to place condition on a “GROUP BY” clause to further filter the results, you will need to edit the report directly by adding a “HAVING” clause to the SQL query.
    • In a simple report the table aliases are (T0, T1, T2...).
    • The first table will get T0 and so on.
  • Sending report results in an email.
    • Create an Policy
      • Move to the Task Tab, then to the desired Folder
      • Right-click New > Policy > E-mail Automatic Action
    • Add this line %Results% in the Message section on the E-mail Automatic Action.
      • You may like to add a header to the first line.
    • On the main policy page, select the Source as Report
    • Open the Item Selector and select the report.
    • Select “Test Notification Policy” button and check your results
    • There is a limit to the size of the table the policy can email.
      • You wish to limit the number of columns in the report
  • If you need to save a set of custom reports in a folder, do the following
    • Right-click the folder, select properties, and collect the folder GUID.
    • Right-click any left tree pane item and select “View as XML”.
    • Replace the GUID after the ItemGuid tag in the URL with the folder GUID and press enter.
    • Save as an XML file.
  • Drilldown parameters and changing views
    • You will see this is from the Edit SQL Directly main page after you created a level 0 query.
      • The start of the queries are normally seen, but the picture is edited for place
    • The icon to configure your drilldown parameters is the table with the magnifying glass.
      • Drill down to…
        • Different Query Level (level 0 > level 1 or level 2 …)
        • Different Report (dropdown menu showing a list of reports)
        • Another Web Page
        • Resource Tool
        • Registered Drilldown
      • It is possible to have a parallel drilldown by using the different multiple drilldown parameters on the same level.
        • Once the report runs, select a row, and use the right-click function to drill down to the needed item
    • The icon to change the default (Grid or Chart) view is the pie chart with the grid.
      • This also allow you to hide and un-hide columns
        • GUIDs are hidden by default
        • Column aliases with an underscore to start with are hidden as well
      • Change the chart parameters
        • Type and Title
        • Position, Scale, Rotation
        • Color, Grayscale, and Transparency
      • Created an HTML view
      • Create a Pivot view
    • The icon to add local parameters is the red question mark in the box ([?]).
      • The parameters will be passed at run time.
-- List of Reports
select
 [Product] = vp.[Name]
,[Report]  = vr.[Name]
,vr.[Description]
,vr.[CreatedBy]
,vr.[ModifiedBy]
,vr.[CreatedDate]
,vr.[ModifiedDate]
from vReport vr
join vProduct vp on vp.[guid]=vr.[ProductGuid]
order by 1, 2
-- Field Selector Query
select
 MetaObject.[SysDbId] as DatabaseId
,T1.[name] as DatabaseName
,MetaObject.[SysObjectId] as ObjectId
,T2.[name] as ObjectName
,NULL as ServerName
,MetaObject.[DisplayName] as ObjectDisplayName
,MetaObject.[Description] as ObjectDescription
,MetaObject.[Category] as ObjectCategory
,MetaObjectColumn.[SysColumnId] as ColumnId
,MetaObjectColumn.[DisplayName] as ColumnDisplayName
,MetaObjectColumn.[KeyType] as ColumnKeyType
from MetaObject
join master.dbo.sysdatabases T1 on MetaObject.[SysDbId] = T1.[dbid]
left join MetaObjectColumn
  on MetaObject.[SysObjectId] = MetaObjectColumn.[SysObjectId]
join sysobjects T2 on MetaObject.[SysObjectId] = T2.[id]
join DataClass T3 on T2.[name] = T3.[DataTableName]
-- Item picker query
select
N'-- No filter --' as 'Name'
,'00000000-0000-0000-0000-000000000000' as 'Guid'
union
select distinct
 sc.String as 'Name'
,rt.Guid as 'Guid'
from (
-- picker report
select Guid from ResourceType t1 inner 
join ItemReference t2 on t2.ParentItemGuid = t1.Guid
where t2.Hint = 'pickerreport'
union
-- add the inheritable collection guid
select 'A725FB57-09E1-4E9F-BB13-B4600094CF61' as Guid) rt 
join StringCache sc on BaseGuid = rt.Guid 
where StringRef = 'item.name' and Culture=N'en-US'

Core Inventory Table Reference (Limited Set)

Table Names (Basic Inventory) Description
Inv_AeX_AC_Client_Agent Shows which agents and version are installed on the client.
Inv_AeX_AC_Client_Connectivity Shows connectivity (LAN, WAN, disconnected)
Inv_AeX_AC_Discovery This table show which discovery method was used (Resource discovery, AD import).
Inv_AeX_AC_Identification This show the name, domain, OS name, OS version, OS type…
Inv_AeX_AC_Location Shows the FQDN, distinguished name
Inv_AeX_AC_Machine_Usage Shows machine usage based on logon and logoff events.
Inv_AeX_AC_NT_Services Shows the services running on the client (Name, description, Startup Type, Logon as, Path)
Inv_AeX_AC_Primary_User Shows primary user per month (28 day month)
Inv_AeX_AC_TCPIP Show IP address, MAC address …

Inventory Solution Table Reference (Limited Set)

Inventory View Description
Inv_AeX_HW_%HW Name% Hardware Views
Inv_AeX_OS_%OS Name% Operating System Views
Inv_AeX_SW_%SW Name% Software Views

Hardware Views
Inv_AeX_HW_ActiveTcpUdpPorts Inv_AeX_HW_Mouse
Inv_AeX_HW_BIOS Inv_AeX_HW_NetWork_Cards
Inv_AeX_HW_CPU Inv_AeX_HW_PCI_Bus
Inv_AeX_HW_Disk_Space_Usage Inv_AeX_HW_PCMCIA_Devices
Inv_AeX_HW_Logical_Disk Inv_AeX_HW_Physical_Disk
Inv_AeX_HW_Logical_Disk_Changes Inv_AeX_HW_Printer
Inv_AeX_HW_Memory Inv_AeX_HW_SCSI
Inv_AeX_HW_Memory_Changes Inv_AeX_HW_Serial_Number
Inv_AeX_HW_Memory_Modules Inv_AeX_HW_SMBIOS
Inv_AeX_HW_Modems Inv_AeX_HW_USB_Bus
Inv_AeX_HW_Monitor Inv_AeX_HW_Video
  Inv_AeX_HW_Video_Monitor

Operating System Views
Inv_AeX_OS_ACLs Inv_AeX_OS_Quick_Fix_Engineering
Inv_AeX_OS_Add_Remove_Programs Inv_AeX_OS_RAS_Server
Inv_AeX_OS_Admin_Group Inv_AeX_OS_Results
Inv_AeX_OS_AT_Scheduler Inv_AeX_OS_System
Inv_AeX_OS_Audit_Policy Inv_AeX_OS_System_Devices
Inv_AeX_OS_Desktop Inv_AeX_OS_Task_Scheduler
Inv_AeX_OS_IIS Inv_AeX_OS_TCPIP_Changes
Inv_AeX_OS_Internet_Explorer Inv_AeX_OS_Time_Zone
Inv_AeX_OS_Memory_Management Inv_AeX_OS_Updates
Inv_AeX_OS_Network_Provider Inv_AeX_OS_User_Profiles
Inv_AeX_OS_Network_Shares Inv_AeX_OS_Win16_Subsystem
Inv_AeX_OS_Operating_System Inv_AeX_OS_Windows_9x
Inv_AeX_OS_Provider_Order Inv_AeX_OS_Windows_NT

Comments 16 CommentsJump to latest comment

riva11's picture

Just to let you know that you've done a good article.
Thanks!

+2
Login to vote
cnpalmer75's picture

Great atricle & how-to.

Does anyone know of what other operators are available other than the %Results% one you use as an example?

Add this line %Results% in the Message section on the E-mail Automatic Action.

I have an open request with my AE on it but didn't know if anyone else here may know.

Benjamin Z. Palmer
Architect | Workspace Design | The Hartford | Simsbury, CT 06082

Benjamin Palmer
Specialist | Client Design
Director | Symantec CT User Group

If you find this post helpful please give it a thumbs up!
If you find that this solves your problem please mark it as the solu

+1
Login to vote
Alex Held's picture

There is another passing parameter you can use, but to pass it on to the email you would need to edit the policy xml file directly. There is a self closing xml tag called policyActionParameters that you would need change to an open tag, add child items for the columns you need to pass, and then add a closing tag.

Other than %Results% there is %DS:ColumnName%
The column name is from the SQL query

Hope this helps!

+1
Login to vote
Andrew Souter's picture

Has anyone came across a situation where the Inventory folder moves location to under the Application Management folder when browsing for tables to add into the report builder?

+1
Login to vote
xmoreland's picture

Just wondering how much you anyone out there uses views in their reporting? I usually end up writing my new reports as a new view but not saving it. Gives me a good drop and drag way to do some things. I need to take a better fine tooth to this article... The variables and such still are clear as mud for me.

Thanks

+1
Login to vote
Eshwar's picture

Alex,
Thank you very much for your time and effort on this. I appreciate it.

Cheers,

Eshwar

+4
Login to vote
rivenburg's picture

this was in the article above.

Anyone know how to change this in NS 6.5?
I'm JR admin, I dont have rights to the NS security, my main admin is willing but....

Our old 6.o NS showed the guids in the query builder page 6 if I used the "run" button. ONLY there, it was supressed everywhere else.

VERY useful for diaging like when the SQL syntax is right but no data is displayed or getting a collection guid instead of useing the collection picker during developement.

+1
Login to vote
abamford's picture

I am trying to write a quick report for Management showing the date a computer was purchased (which will be within a few days of it being put into Altiris).

The only date field I can see which would be approx is bios release date.

Any suggestions please?

0
Login to vote
KSchroeder's picture

If you're not actually using Asset Management, then the vComputer.CreatedDate column should be fairly close and fit your purpose. 

If you need additional assistance, I would suggest creating a new Forum thread in the Client Management Suite section.

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
QuietLeni's picture

Hi,

 

Has anyone tried using HTML Views in the Rerport Builder? I need to know the syntax for including lines in the Configure Report Views dialog?!

Thanks in advance,

 

QuietLeni

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
crammag's picture

I seem to be getting a lot of garbage in my reports and I'm wondering if anyone has any good walk throughs to create a new Inventory report that will grab servers with the agent installed from only 1 OU in active directory?

This might sound a little odd but the reason for this is because I am seeing hundreds of thousands of devices in the inventory report when I should only be seeing about 200...  I have been unsuccessful finding relevant help for Notification Server 7.

I appreciate any tips!

0
Login to vote
JSzeto's picture

Any solution to this? I am looking for the same thing around.

0
Login to vote
haroldvm89's picture

Anybody has the same tutorial for the lastest version of NS??? Think NS 7? 

It would be really helpful!

Thanks

 

0
Login to vote
QuietLeni's picture

haroldvm89,

I did something later on when researching this and put it here:

https//www-secure.symantec.com/connect/forums/report-views-types

If it helps, then please vote it up!!!

Kindest regards,

QuietLeni

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
KNP's picture

From where can I download Report Builder?

Anyone can provide me?...

0
Login to vote
SK's picture

Its part of the product, and can be selected when you choose which report type you want to create.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote