The following query maps item names to the three different guids found within the ResourceAssociation table, allowing you to understand its contents better:
SELECT vi1.[Guid] AS ResourceAssociatiopnTypeGuid,vi1.Name AS ResourceAssociatiopnType, vi2.[Guid] AS ParentResourceGuid,vi2.Name AS ParentResource, vi3.[Guid] AS ChildResourceGuid,vi3.Name AS ChildResource FROM vItem vi1 JOIN ResourceAssociation ra ON ra.ResourceAssociationTypeGuid = vi1.[Guid] JOIN vItem vi2 ON vi2.[Guid] = ra.ParentResourceGuid JOIN vItem vi3 ON vi3.[Guid] = ra.ChildResourceGuid ORDER BY vi1.Name ASC
hmmm ive never used those.. we all have our favorite tables to join, and grab info from... very interesting
Ditto. I'm working on an ServiceNow/Altiris integration project to automate software delivery. This query helped tremendously as the relationships between command lines, packages, software resources, and quick delivery tasks within the CMDB were unclear to put it mildly. Glad I found it; wished I had found it two days earlier. Big time saver.
Very nice query. Thanks to it I understand better the relationships between resources and can create better queries.