Asset Management Suite

Since we finished our implementation of Asset Management Suite I have had more requests for reports than ever before. The information can sometimes seem difficult to get to, especially with the modular nature in which Asset Management Suite and CMDB seem to have been designed. However, by taking a little bit of time to understand how to look at things it turns out to be much easier than an initial inspection might lead you to believe.

After this article I hope that most people will have a better understanding of how to write reports for Asset Management Suite and maybe even a handy way to help their Asset Administrators get more out of the tool than ever before.

Step 1. Decide what resource you will need to report on and gather information.

Most every time you will need to report on a particular resource type, because of a request from the business unit. However, there can be other reasons to write reports, including, providing a customized toolset for your Asset Administrators to track and modify asset properties.

Before you begin to write your report you will need to know a few key things about the resource type on which you are reporting. First and foremost you will need to know the Guid for the resource type. Getting a list of these guids is an easy task, just run the following query to get a list of resource types, their guid, and the description that was given to the resource. The result is, from what I can tell, a very comprehensive list of resource types.

Select [Guid], [Name], [Description]
From ResourceType

Step 2. Gather what information you will be displaying.

Data Class Information

Once you have a resource and a guid for that resource you now need to decide what information you will need to display on your report. There are a couple of ways to see exactly what is available to you. The first thing that you will want to do is see exactly what Data Classes apply to your resource type. Another quick query can display this information for us.

Select DC.[Guid], DC.[Name], DC.[DataTableName]
From ResourceTypeDataClass RTDC
	Join DataClass DC on DC.[Guid] = RTDC.[ResourceDataClassGuid]
Where RTDC.ResourceTypeGuid = '{Target Resource Type Guid Here}'

After running this report you will get some more important information, in your quest. The guid for each applicable data class, the name of the data class as you would see it on your notification server console, and the database table name for each of those data classes will be displayed on the results of this query.

Resource Association Information

Knowing the data classes associated with resource type is important, but what is often equally as important is information on other resource types that are associated to your resource type. This information again can be gotten from a query directly within the Altiris database. The one I have written looks like this.

Select rt1.[Name] 'Parent', rt2.[Name] 'Child', rat.[guid]
From ResourceAssociationType rat
	Join ResourceType rt1 on rt1.[guid] = rat.[ParentResourceTypeGuid]
	Join ResourceType rt2 on rt2.[guid] = rat.[childResourceTypeGuid]
Where rt1.[guid] = '{Target Resource Type Guid Here}'

Of course this is if your resource type is the Parent. If it is not then your target association will not show on that report. It is easy enough to fix just swap your "where" to match the Child field.

Select rt1.[Name] 'Parent', rt2.[Name] 'Child', rat.[guid]
From ResourceAssociationType rat
	Join ResourceType rt1 on rt1.[guid] = rat.[ParentResourceTypeGuid]
	Join ResourceType rt2 on rt2.[guid] = rat.[childResourceTypeGuid]
Where rt2.[guid] = '{Target Resource Type Guid Here}'

You may see that you have two different guids for some associations, I have found this to happen when we have created a custom resource association when the out of the box one did not quite fit our need or when we needed to define an "Assigned" user and possibly an "Approver" user. There could be multiple reasons. If you find the association type you are looking for has two guids you will want to visually verify the guid of that association type on your configuration tab in the Notification Server console.

Step 3. The "other" stuff

You should now have a good number of tables that you might want to pull information. We have outlined how to get the data class tables for your target resource type and how to get other resource types and the information about the association.

However, there is still information that can be gathered about your resource.

The table name "Item" has some good information including a name and a date the resource was last created, or modified, as well as how it was created of modified. Other views can provide some good information aggregated together. I often use vAsset to get some basic information like Serial Number or Asset Tag. Sometimes the tables are even more descriptive like vFixedAssetResourceStatus.

Unfortunately I have not found a concrete way to search through all of these and find ways to gather all of this information together, perhaps someone with more time, patience, and SQL skill knows a way. However I would instead recommend for those of you like me who know, just too much to be dangerous, to take some time and browse through the user tables and views that were created in the Altiris database on your Notification Server. Again, I recommend if you are using SQL 2000 to just use Query Analyzer.

Step 4. Bring it together

Bringing it together can sometimes be the hardest part. Often it can be easiest if you have seen it in action. So below you will find an example of what we are using in our organization.

SELECT distinct vA.Guid, 
	Item.Name,    
	ASMM.[Model],   
	TypeItem.[Name] as 'Category',  
	gd.[Display Name] as 'Owner',  
	ECLD.[cubicle] as 'Location', 
	gd.[Department] as 'Owner Department',   
	ed.[Acquisition Date] as 'Date Purchased'   
   
FROM vFixedAssetResourceStatus vA    
join Item on Item.Guid=vA.Guid     
join ResourceType rt on rt.Guid = vA.ResourceTypeGuid   
	LEFT OUTER JOIN Inv_Serial_Number ASN on ASN._ResourceGuid = vA.Guid   
	LEFT OUTER JOIN Inv_Manufacturer ASMM on ASMM._ResourceGuid = vA.Guid   
	LEFT OUTER JOIN ResourceAssociation TypeRA ON TypeRA.ParentResourceGuid = vA.Guid    
		AND TypeRA.ResourceAssociationTypeGuid = '{computer to type guid}'   
	LEFT OUTER JOIN Item TypeItem ON TypeItem.Guid = TypeRA.ChildResourceGuid   
	LEFT OUTER JOIN inv_Extended_Computer_Details ed ON ed._ResourceGuid = ITEM.guid   
	LEFT OUTER JOIN ResourceAssociation OwnerRA ON OwnerRA.ParentResourceGuid = vA.Guid    
		AND OwnerRA.ResourceAssociationTypeGuid = '{owner to computer guid}'
	LEFT OUTER JOIN Inv_Global_User_General_Details gd ON gd._ResourceGuid = OwnerRA.ChildResourceGuid  
	LEFT OUTER JOIN Inv_Extended_Computer_Location_Details ECLD ON ECLD._ResourceGuid = vA.Guid 
   
WHERE rt.Guid= '{computer resource guid}'   
	AND vA.Status = 'Active'  
	AND gd.[Department] like '%_dep%'  
   
ORDER BY gd.[department], Item.Name

In this example we are building a report on our "Computer" resource type. It will be filtered by status and department for the "Owner". Each resource association type's guid has been specified in this case to make sure that the correct resource association.

You'll need to fill in the guids for the three following items the first is for your "Computer to Computer Type resource Association". The second guid that needs to be filled out is for the "Computer to Owner Association" and the final one is the guid for your "Computer Resource Type".

Our report also contains a "Parameter" in the where section. If you have not used parameters before on your custom reports, these are the items on a report that prompt people when running it. To get this one to work properly click the box with a red question mark on your "edit report" section. This will bring up the "Parameter Selector". Choose "New Local Parameter". This one has been named _dep, and the parameter type of "Basic" has been chosen. To get it to pop up when you run the report make sure to check "Prompt user for value". Fill in a prompt, choose string for your type, and fill in a standard value, though that part is optional. I have found that adding a % sign to that field has been a good idea since it often helps people who do not know what to put in.

Step 5. Extras

I have one last little tip to share. Did you know that after you have built a report that your asset guys really, really like you can add it as a "Tool" on the Resources Screen on the NS console.

For example when browsing Resources > Configuration Items > Asset Types > IT > "Computer" on our "Resources" tab we see the default list of all items that fit that Resource Type, but in some environments it can be a bit overwhelming. Once you have a good report you can add it as an option on this screen. By clicking the small "Pick Reports" button on the right most part of the bar a report picker will be opened.

After you browse through your list of reports and choose those that work for you they will be listed on a dropdown labeled "Show".

Click to view.

Now to sit back and reap the wonderful rewards of your very thankful Asset Administration staff.