How to write SQL query to get BigFix client custom properties

Content Authoring
1

How to write a SQL query to get BigFix Client custom properties? For example I have tried this query but did not get correct data.

SELECT
c.ComputerID,
c.ComputerName,
c.LastReportTime,
osProp.ResultsText AS OS,
ipProp.ResultsText AS IPAddress,
CoProp.ResultsText AS CMDB_Company
FROM
dbo.COMPUTERS c
LEFT JOIN dbo.QUESTIONRESULTS osProp
ON c.ComputerID = osProp.ComputerID AND osProp.PropertyID = ( SELECT ContentID FROM dbo.PROPERTIES WHERE Name = ‘OS’ )

LEFT JOIN dbo.QUESTIONRESULTS ipProp
ON c.ComputerID = ipProp.ComputerID AND ipProp.PropertyID = ( SELECT ContentID FROM dbo.PROPERTIES WHERE Name = ‘IP Address’ )

LEFT JOIN dbo.QUESTIONRESULTS CoProp
ON c.ComputerID = CoProp.ComputerID AND CoProp.PropertyID = ( SELECT ContentID FROM dbo.PROPERTIES WHERE Name = ‘CMDB_Company’ )

WHERE
c.IsDeleted = 0
ORDER BY
c.LastReportTime DESC

Any help would be appreciated. Thanks.

2

We’d strongly discourage using direct SQL to retrieve data from the database - as you can see, it’s complex to query, and also likely to introduce locking issues that might interrupt the BigFix server itself.

Instead I’d recommend using Web Reports for this, where you can just select those properties in the column picker of the ‘Explore Computers’ interface.

If you need to script it, I’d recommend REST API, probably on the /query resource where you can send it Session Relevance queries.

3

Thanks for your response Jason. I was trying to use the SQL query to retrieve BigFix Client data in Power BI for dashboard purposes.

4

Ah, ok. In that case, you could create a secondary copy of the database, either by backup/restore or a SQL Always-On replica (to prevent interfering with the root server), but the preferred path would probably be to set up Insights. Insights provides a “normalized” version of the data that is easier to query, as well as row-versioned records to query results based on points in time.

I’m afraid I can’t help much with the SQL query either way, but maybe that’s helpful to point a direction. You may get some better response on the SQL query here as well

5

I’ve only limited knowledge about PowerBI. As far as I’ve seen, it should be able to import json format.
Maybe BigFix Explorer might be an alternative as it is able to provide json output?

6

Maybe check out this webinar on BigFix with PowerBI using Insights:
https://register.gotowebinar.com/register/9085707012551495512
(yes we ask for your e-mail, but we don’t bug you…)

We also have this set of docs, including some sample dashboard files:

1 Like