How do I get the status of an asset from the NS database?
There are a few things to note when it comes to the status of an Asset.
- The status of an asset is actually an association, not a data class. There are a couple of reasons why this is important. First, all associations are found in the resource association table. So you have to use the resource association type guid to distinguish the status association. Second, this association is from Asset to Status. So if you are looking for the status of a computer, you have to use the resource type of asset instead of computer to get the resource association type guid.
- Often there is no status set at all. When this happens the canned reports are built to see the status of an Asset that has no status as Active. So really there are two types of the Active status. You have assets that are seen as active that have an entry in the resource association table for status and assets that are seen as active that do not have an entry in the resource association table for status.
- The status of an asset is not forwarded by inventory forwarding. So if you change the status of a computer on the NS that is forwarding the data, the status will not be changed on the one that is having the data forwarded to it. To forward the status of an asset you can create a rule using Connector Solution on the NS that is having the data forwarded to it.
To help with understanding this better, below is a sample SQL query that pulls the status of Assets:
select i1.[name]as Asset, rt.name as [resource type], isnull(i2.[name], 'Active') as Status
from vasset va
left join ResourceAssociation ra on ra.ParentResourceGuid = va._ResourceGuid
and ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
join item i1 on i1.guid = va._ResourceGuid
left join Item i2 on i2.guid = ra.ChildResourceGuid
join itemresource ir on va._resourceguid = ir.guid
join resourcetype rt on rt.guid = ir.resourcetypeguid