Today we will review how to create report with a drop down parameter selector using a real life example for one of my customer.
The article is divided as shown here:
- Create the SQL to display the data we need
- Create Report 1 using the SQL from (1)
- Create the SQL to display the drop-down information
- Create Report 2 using the SQL from (3)
- Modify Report 1 to add the drop down filter
You will also find attached the exported reports to this article.
1. Create the SQL to display the data we need:
Whilst trying to answer a question related to the Symantec Agent push I struggled to find a list of computers in a given target. So I decided to create a report for my customer to simplify the troubleshooting process. We need to know which computers are targeted by the agent push and where they are from in order to understand why the target computer count remain at ~150 computers.
To get the data from SQL I started by exploring the database objects that contains the name target:
select name from sys.objects where name like '%target%' order by type, name
This query returns all the objects that have the string target in their name, and I found a stored procedure that list the target membership:
Now this is a good starting point, but using a stored procedure in a query or in a SMP report is not an option, so I used another great tool from SQL Server, the 'sp_helptext' in order to review how the result is returned from the stored procedure execution.
Luckily the sp is very short and the only intelligence there is to convert the parameter guid list into a table and use that to filter resource membership accordingly. And all of the information is gathered from the TargetResourceMembershipCache table.
With the source data table we can now select the fields that interest us and extend the query to return meaningful results via 'left join'. Specially we want to see the OwnerNS (in case the resource came from replication) and the product guid (if the resource was created by another product than the SMP). Also we will display the computer guid as this is quite important when trying to uniquely identify a resource:
select c.Name, t.ResourceGuid as '_ItemGuid', c.IsManaged, s.name as 'Owner NS', p.name as 'Product name', t.LastUpdated, t.ResourceGuid as 'Computer Guid' from ResourceTargetMembershipCache t left join RM_ResourceComputer c on t.ResourceGuid = c.Guid left join vSource s on c.ownernsguid = s.guid left join vProduct p on c.ProductGuid = p.Guid where ResourceTargetGuid = '02DB572F-EA57-40F9-9631-F400E93283DF'
2. Create the Report with the SQL query from (1):
This is the easy part, now that we have the SQL. Go to the report section on the SMP console and right click on a folder where you want to create the report. Then right click, select "Create Report > SQL Report".
Double click on the report name ("New Report" by default) and add the name and description. Then on the "Data source > Parameterised query" remove the entire content and add the SQL from above.
Note that we use a hardcoded target guid there, and it should list all computer resources with no Symantec Management agent.
Note that I have named the report "View ResourceTargetMembership per target".
3. Create the SQL to display the drop-down information:
With the base report displaying a valid sample of the results we want, we can now move on to the next step: create a filter that will allow us to select a target to view the selected target membership.
Using the exploration SQL query from (1) we can see that there is a table that list all Resource Targets in the database, simply named ResourceTarget.
All the fields from the table are not of interest, and some data is missing. We need the guid as the ResourceTargetGuid in our base report, and the Resource Target Name for the report user to select a target that means something to him.
So the query for the second report is very simple:
select i.name, i.guid as 'TargetGuid' from ResourceTarget t join Item i on t.guid = i.guid order by i.name
4. Create Report 2 using the SQL from (3)
Following the usual steps to create a SQL report we simply put the SQL from (3) on the DataSource PArameterised query and save the report with a meaningful name.
I named this report "Full list of Resource Target".
5. Modify Report 1 to add the drop down filter
There are a few options to create report parameter, and today we will use a simple dropdown list, as it's a very efficient mechanism to get moderately large (no more than 500 entries) lists in front of users.
We start by opening the report created in (2) and editing it. On the tabs, we select "Report parameter" and click on "Add > New parameter...". The parameter is going to contain a TargetGuid to filter the SQL result set, so we'll name accordingly: "TargetGuid". You can also add a description as this is always good documentation.
Then select "Basic Guid Parameter" in the dropdown "Type" list. This selection adds the "Default Value" and "Test value" fields. The fields are set with blank (all 0) guids by default, and I populated them with my test target guid.
Next we select the value provider, the drop down option we want is named "Dropdown list Value Edit Control". I have set a width option to 800 pixel as target names can be a mouthful from time to time, but this is not necessary as we'll see later. I filled in the Tooltip and label to make it as clear as possible to any users what the field is for and how they should use it.
Finally we set the dropdown values using the 2nd section of the value provider: "Add Dropdown Values From A Report".
Then we select the report we created in step (4) and the fields to be used: we will work with the TargetGuid as value (for consumption in the SQL query) and we'll display the target name (in the drop down list).
With all of this done we can save the parameter and report.
Now that the report parameter is created we only have a couple more step to go through and see the results.
We have to go back to the DataSource and create a Query parameter based on our report parameter. This is simple, as the TargetGuid report parameter we just created is available in the "Add" option. Note that if you have added a description to the report parameter this field will be used in the "Add" option list, as is the case in the screenshot below (you'll also noticed that I change the description to take this into account.
To recap, we have a report SQL query, a dropdown control to select a target, a query parameter to filter the SQL result, so we can add the final touch: update the SQL query to use the TargetGuid parameter.
This is done as shown below (in bold italic), with the parameter name being used inside the SQL quotes and enclosed between percent signs:
select c.Name, t.ResourceGuid as '_ItemGuid', c.IsManaged, s.name as 'Owner NS', p.name as 'Product name', t.LastUpdated, t.ResourceGuid as 'Computer guid' from ResourceTargetMembershipCache t left join RM_ResourceComputer c on t.ResourceGuid = c.Guid left join vSource s on c.ownernsguid = s.guid left join vProduct p on c.ProductGuid = p.Guid where ResourceTargetGuid = '%TargetGuid%'
With a final save we will see the report display the default results, and we are able to select any Target to see the content in the report results: