cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Looking for a specific field in the Compliance database

Dear Flexera Community members,

We intent to compare our CMDB with the FNMS DB. We specifically want to check if an system is found in the CMDB and it is active, is it also present in FNMS in the "All Inventory" page. I have found the below URL describing the required db table for the all inventory data:

https://community.flexera.com/t5/FlexNet-Manager-Forum/All-Inventory-info-in-FNMS-DB/m-p/170111

The problem right now is that I find more than just the ComplianceComputer table:

 

tables.png

I am now looking for the fields Name and Domain name of each record shown in the All Inventory page, but I don't know where to find this information. Which table do I need for this?

 

Thanks

 

(1) Solution

@bleepie  - Please try the following script.  It returns the Computer Name, Serial Number, Domain, Manufacturer, Model Number, Status and Computer Type.  Only those devices that are shown in the All Inventory page are included.

SELECT
c.ComputerName,
c.SerialNo as 'SerialNumber',
c.Manufacturer,
c.ModelNo,
d.FlatName as 'Domain',
d.QualifiedName as 'FullDomain',
REPLACE(REPLACE(s.DefaultValue,'[',''),']','') as 'Status',
t.DefaultValue
FROM ComplianceComputer c
JOIN ComplianceDomain d on d.ComplianceDomainID=c.ComplianceDomainID
JOIN ComplianceComputerStatus s on s.ComplianceComputerStatusID=c.ComplianceComputerStatusID
JOIN ComplianceComputerType t on t.ComplianceComputerTypeID=c.ComplianceComputerTypeID
WHERE c.ComplianceComputerStatusID In(1,2) --Ignore any internal status, only include Active or Ignored
AND c.ComplianceComputerTypeID In(1,2,3) --Ignore Remote Devices, VDI Templates and Containers, only include Computers, Virtual Machines and VM Hosts

View solution in original post

(1) Reply

@bleepie  - Please try the following script.  It returns the Computer Name, Serial Number, Domain, Manufacturer, Model Number, Status and Computer Type.  Only those devices that are shown in the All Inventory page are included.

SELECT
c.ComputerName,
c.SerialNo as 'SerialNumber',
c.Manufacturer,
c.ModelNo,
d.FlatName as 'Domain',
d.QualifiedName as 'FullDomain',
REPLACE(REPLACE(s.DefaultValue,'[',''),']','') as 'Status',
t.DefaultValue
FROM ComplianceComputer c
JOIN ComplianceDomain d on d.ComplianceDomainID=c.ComplianceDomainID
JOIN ComplianceComputerStatus s on s.ComplianceComputerStatusID=c.ComplianceComputerStatusID
JOIN ComplianceComputerType t on t.ComplianceComputerTypeID=c.ComplianceComputerTypeID
WHERE c.ComplianceComputerStatusID In(1,2) --Ignore any internal status, only include Active or Ignored
AND c.ComplianceComputerTypeID In(1,2,3) --Ignore Remote Devices, VDI Templates and Containers, only include Computers, Virtual Machines and VM Hosts