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.

Click to view.

• 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

Click to view.

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.

Click to view.

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

Click to view.

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.

Click to view.

• 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

Click to view.

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.

Click to view.

• 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
  

  Click to view.

  • 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
  

  Click to view.

  • 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)

Inventory Solution Table Reference (Limited Set)