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 .
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.
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.
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
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.