Discovery and Inventory Group

Software inventory consists of at least two types of details. They are: 

1. File details – provides basic file information, i.e., name, size, location, version, etc.
2. Software component details – Allows for managing software products in the Software Catalog.

Different inventory processes populate these two types of details. On the Unix, Linux and Mac (ULM) systems, there are three methods for gathering software inventory. They are:  

Tables, Views and their Relationships

Following are a few tables and views containing the file details and software component details. Also, the table that associates the two types of data is listed. 

File detail tables: 

• Inv_Installed_File_Details
• Inv_File_Details
• RM_Resourcefile

Software Component tables: and views 

• Inv_InstalledSoftware
• Inv_Software_Component
• vSoftwareComponent

The association table between the two types of tables is: 

• ResourceAssociation.ChildResourceGuid  - contains the file details guid.
• ResourceAssociation.ParentResourceGuid – contains the software component guid. 

 Note: By querying for a file detail guid in the ResourceAssociation.ChildResourceGuid attribute, the corresponding software component guid can be found in the db record's ParentResourceGuid attribute. The ParentResourceGuid can then be used to find additional software component data. 

File Detail and Software Component Table Relationships

Following is a description of these tables showing their related attributes (columns). Note that the attributes in the same column have the same guid value. Therefore, tables can be joined using the attribute names in the same column. 

File and software component data

File and software component data 

Software Component data only

Other tables 

 This is a sample of other tables related to file and software component data: 

• Inv_symantec_common_agent_discovery_details._resourceguid
• Inv_symantec_UNIX_RPMPackageInfo.softwarecomponentguid
• Inv_software_component_state._resourceguid (contains IsManaged flag)
• RM_ResourceSoftware_Product.guid

Sample SQL queries

Note that left outer joins were used in case software components and resource associations are not yet created. 

Starting with inv_installed_file_details (File and software component data)

select vc.name, vsc.name, ifd.name, isc.version, ra.*, ifd.fileresourceguid, ifdet.filesize, ifdet.fileextension
   from inv_installed_file_details ifd 
   join vcomputer vc on vc.guid = ifd._resourceguid
   join resourceassociation ra on ifd.fileresourceguid = ra.childresourceguid
   join vsoftwarecomponent vsc on vsc.guid = ra.parentresourceguid
   left outer join inv_software_component isc on isc._resourceguid = vsc.guid
   left outer join inv_file_details ifdet on ifdet._resourceguid = ifd.fileresourceguid  
   where vsc.name like 'websphere%' 
   order by ifd.fileresourceguid

Starting with RM_ResourceFile (File and software component data) 

select vc.name, rf.guid, vsc.name, ifd.name
   from [RM_ResourceFile] rf 
   left outer join resourceassociation ra on ra.childresourceguid = rf.guid
   left outer join vsoftwarecomponent vsc on vsc.guid = ra.parentresourceguid
   left outer join inv_installed_file_details ifd on ifd.fileresourceguid = rf.guid
   join vcomputer vc on vc.guid = ifd._resourceguid
   where rf.NAME like '%websphere%'

Starting with Inv_InstalledSoftware (Software Component data only)

select vc.name, vsc.name, isc.version, iis.installdate, iis._softwarecomponentguid, vc.[os name] 
   from vcomputer  vc
   join inv_installedsoftware  iis on iis._resourceguid = vc.guid
   left outer join inv_software_component isc on isc._resourceguid = iis._softwarecomponentguid
   left outer join vsoftwarecomponent     vsc on vsc.guid = iis._softwarecomponentguid
   where vsc.name like 'websphere%'
   order by vc.name, vsc.name, isc.version