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

How to Create a Report to Return ResourceTarget Membership to a User?

Created: 14 Aug 2012 • Updated: 14 Aug 2012 | 7 comments
Language Translations
Ludovic Ferre's picture
+2 2 Votes
Login to vote

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:

  1. Create the SQL to display the data we need
  2. Create Report 1 using the SQL from (1)
  3. Create the SQL to display the drop-down information
  4. Create Report 2 using the SQL from (3)
  5. 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:

spGetResourceTargetsMembership

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:

Comments 7 CommentsJump to latest comment

Luke Grimm's picture

This is really nice. I was able to set up the report fairly quickly with these instructions. And it is interesting to see our targets in one place. As you say, it is helpful when you need to search for a specific machine while troubleshooting. Thanks!

 Question; I wondered why some are listed in single quotes and some are not?

0
Login to vote
Ludovic Ferre's picture

Can you specify which entries are quoted, or better add a screenshot? This would make it much easier to understand, but as far as the SQL is concerned we are not adding anything, so it would appear to be the target name.

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Ludovic Ferre's picture

I have the answer now!

Targets are automatically created when you use a filter with a policy. When you do not save the target it will be saved with the filter name under enclosed in single quotes.

Voila.

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

+1
Login to vote
Luke Grimm's picture

I came to the same conclusion after closer inspection. Thanks, really like this report BTW.

0
Login to vote
Tomasz Wozniak's picture

Thank you for the step-by-step guide. I had no troubles following it.

Do you think you could add something about drilldowns ?

0
Login to vote
Ludovic Ferre's picture

I'm sure I can try to help you. What do you need?

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Tomasz Wozniak's picture

Ludovic,

I meant similar article to this one. Something like 'How to create a drill down report' .Surely there is a documentation but with the screenshots included it is much easier for beginners in the reporting section.

For example when I click the computer I could drill down 'Show the installed software' or 'Show the disk usage by file' etc from the context menu.

I query  DB a lot directly in sql studio but I am not very familiar with the NS builder, drilldowns etc. at the same time :).

I would like to be able to create some useful reports for the console end users.

thanks

Tomasz

0
Login to vote