BigFix Insights and analyses properties

[jgallas]

Has anyone reported on analyses properties in Insights? We are trying to navigate the behemoth that is Insights via Power BI and can’t seem to find where to look. Any help with Insights and Power BI would be greatly appreciated.

[dmccalla]

We have the Insights schema published on the developer.bigfix.com site under Tools > Insights Schema and this should help you (somewhat). I don’t have an example for reporting on analyses properties handy, but thought I would post this anyway in case it does help you --> https://developer.bigfix.com/insights_schema/#/

[jgallas]

Unfortunately, it seems that you can only get the ID and name of analyses but not the data unless I’m missing something. For example, if I have an analysis that shows what version of Microsoft Office that a computer has installed.

[itsmpro92]

Have a look at the datasource_property_map and datasource_property_results tables.

[Aram]

You should be able to return analysis property data associated with an external site so long as the site in question is included in the Import/ETL. If the analysis is in a custom site, then it should be included by default.

Here’s a sample query for BFInsights that will return results associated with an external analysis (Uninstallable Patches and Updates) from an external site (Patching Support):

select *
from datasource_property_results
where remote_content_id=(select remote_content_id from datasource_property_map where name like ‘Uninstallable Patches and Updates’)
and remote_site_id=(select remote_id from datasource_sites where name like ‘Patching Support’)

[jgallas]

Can you return properties for custom sites?

[Aram]

Yes

Essentially the same approach, but here’s a sample (replace ‘MyCustomSite’ and ‘MyAnalysis’ with their corresponding names for your usecase):

select *
from datasource_property_results
where remote_site_id=(select remote_id from datasource_sites where name like ‘MyCustomSite’)
AND remote_content_id=(select remote_id from datasource_analyses where name like ‘MyAnalysis’)

[ageorgiev]

This is actually brings up a good topic we have discussed previously with @Aram, namely I do not think Insights database’s schema is very well designed/structured… I get why it is like that cause it essentially mirrors the structure of the BFEnterprise database (only merging QuestionsResults & LongQuestionsResults, and even that has a lot to be desired by cutting the data at 4000 characters) and that does make it better in a sense but if you try to build a complex report with a large number of custom properties where you expect each property to be a column and the devices in separate rows (just like you would in Web Reports with a table-like output) - it becomes EXTREMELY painful in a hurry and processing times start becoming a problem, especially if any of the properties contain more data just because each of those custom properties you have to do another join that goes through [datasource_property_results]. I am not DBA, so maybe I am missing something (there may be a different way to achieve this) but based on my own knowledge here I stand. Consider this example where it retrieves - 3 default properties (Computer Name, Agent Type & Last Report time) and 4 custom properties, where the filter is to display non-deleted machines of agent type = Native, and notice how complicated the query is and what it’s execution time was over 8 hours across 20k devices (that’s not even touching the historical data at all).

I even specifically watched the replay of the PowerBI with Insights data source webinar from September to see if maybe I am doing something completely wrong and the way the same exact scenario (retrieving 4-5 custom properties) they don’t even bother to attempt it as a single query (most-likely for the afore-mentioned reasons) and instead build 5 separate data sources where each runs the same exact query but for a different custom property and then let PowerBI data modelling take care of making the relationship between each separate data source… If we have to do this each time that is not a good situation to be in though, is it?