Historical Data Reports with BigFix Insights

I have deployed BigFix Insights in our test environment and I have been testing it already for a while. I have a requirement from my customer to generate a bar graph that shows the number of active endpoints reporting to the platform per month during the last year.
I have not found a way to achieve that with the data that the BigFix Insights DB provides. Perhaps there is a way to do it but I am not very proficient in SQL nor BI Tools and there is not much documentation about it.
Has anyone done something like this?
This a very basic report that we expected to run with BigFix Insights.
This is the only graphical representation that I have been able to accomplish using the last report time as parameter but it doesn’t mean that the devices were not active in past months.

I haven’t done stuff in Insights, but this bit stands out to me:

IIRC, HCL provides a bundle of sample reports for both Tableau and PowerBI. I gather these don’t provide enough hints?

I always had the idea that Insights exists to deliver BigFix data to BI data wonks, and wrangling BI tools is a completely different expertise. (Which I don’t have!)

The reports that HCL BigFix provides are a good start point but my challenge is with historical data reports. I can create reports to display current data(device inventory, patch compliance) but we want to report on historical data to represent how the device inventory changes over time or how the patch compliance has been over time.
Unfortunately I feel like most of the BigFix admins and in general product specialists don’t have skills with BI Tools and that makes hard to use BigFix Insights, or at least that is my case :frowning: .

Hi @fermt - do you have BigFix Compliance? It has patch reports which may meet your needs. See here: Patch Domain

We don’t have BigFix Compliance. We purchased BigFix Insights believing it will be easy to create these reports. :melting_face:

I haven’t looked at this recently, but I believe the ‘Device Inventory’ sample PowerBI report (Power BI Reports) contains a visualization that does exactly this that might be used as reference.

1 Like

Unfortunately, it apparently doesn’t do what I need. I ran a couple of data exports( Jan 2025 and Feb 2025) and the visualizations and tables seem to report only the current count :frowning:

Is HCL BigFix Planning to release more sample reports?
The ones delivered have a released date of 2021.

I came back to report that it is indeed possible to report on historical data, it is just a matter of studying the DB schema of Insights. I am sharing an example of the query that I came up with to query the database.

WITH HistoricalDevices AS (
   
	 SELECT
		FORMAT(last_report_time, 'yyyy-MM') AS report_month,
		COUNT(DISTINCT remote_id) AS historical_device_count
	FROM
		[BFInsights].[dbo].[datasource_devices_historical]
	WHERE
		last_report_time >= DATEADD(MONTH, -12, GETDATE())
	GROUP BY
		FORMAT(last_report_time, 'yyyy-MM')
),
CurrentDevices AS (
    SELECT 
        FORMAT(last_report_time, 'yyyy-MM') AS report_month, 
        COUNT(DISTINCT remote_id) AS current_device_count
    FROM 
        dbo.datasource_devices
    WHERE 
        last_report_time >= DATEADD(MONTH, -12, GETDATE())
		AND deleted = 0
    GROUP BY 
        FORMAT(last_report_time, 'yyyy-MM')
)
SELECT 
    COALESCE(h.report_month, c.report_month) AS report_month,
    
    (COALESCE(h.historical_device_count, 0) + COALESCE(c.current_device_count, 0)) AS total_devices
FROM 
    HistoricalDevices h
FULL OUTER JOIN 
    CurrentDevices c ON h.report_month = c.report_month
ORDER BY 
    report_month;

The above query can be used to create charts in a BI solution like the following:

The query is taking in consideration endpoints that may have been already deleted and removed from the BFEnterprise but the BFInsights keeps a copy of them under the table datasource_devices_historical.

I have only started to use BigFix Insights back in January that is why I don’t have any historical data before 2025.

6 Likes