Resolving operating system identifiers to names

Resolving software and operating system identifiers to names

When using QRadar's API to get asset data, in the operating system field and in other software related attributes of the returned assets, only an identifier is returned. You can observe an attribute in the JSON like this:

{
  "last_reported": 1566479972310,
  "name": "Primary OS ID",
  "type_id": 1033,
  "id": 1183,
  "last_reported_by": "USER:admin",
  "value": "76030"
}

You find the value of interest in the value attribute. Here is how to resolve these IDs to names as they appear on the UI. You need to open the command line and run a query against the postgresql database.

psql -U qradar -c "select q_catalog.productvariant.id as osid, q_catalog.productvendorvariant.variant, 
q_catalog.productnamevariant.variant, q_catalog.productversionvariant.variant, q_catalog.productversion.major, 
q_catalog.productversion.minor, q_catalog.productversion.sub   from q_catalog.productvariant, q_catalog.productnamevariant, 
q_catalog.productvendorvariant, q_catalog.productversionvariant, q_catalog.productversion where 
productvariant.productnamevariantid=q_catalog.productnamevariant.id and 
productvariant.productvendorvariantid=productvendorvariant.id and 
q_catalog.productversionvariant.id=productvariant.productversionvariantid and 
q_catalog.productversion.id=q_catalog.productversionvariant.productversionid"

This will spit out all entries, it is a good idea to put it into a file. Or if you want to resolve just a specific id, e.g. 76030, just add one more where clause:

psql -U qradar -c "select q_catalog.productvariant.id as osid, q_catalog.productvendorvariant.variant, 
q_catalog.productnamevariant.variant, q_catalog.productversionvariant.variant, q_catalog.productversion.major, 
q_catalog.productversion.minor, q_catalog.productversion.sub   from q_catalog.productvariant, q_catalog.productnamevariant, 
q_catalog.productvendorvariant, q_catalog.productversionvariant, q_catalog.productversion where 
productvariant.productnamevariantid=q_catalog.productnamevariant.id and 
productvariant.productvendorvariantid=productvendorvariant.id and 
q_catalog.productversionvariant.id=productvariant.productversionvariantid and 
q_catalog.productversion.id=q_catalog.productversionvariant.productversionid and q_catalog.productvariant.id=76030"

Note that this list is dynamic and specific to the installation and update level, moreover, the list expands when a VA scanner reports a not-yet-seen operating system type.

The query above was tested on version 7.3.2., table structure might change without notice.