We are currently using the Database API approach to retrieve computer data from the BigFix SQL Server database. Our goal is to collect Application and Hardware information for Windows, Linux, and macOS systems.
To achieve this, we’ve identified a relationship between the **[dbo].[QUESTIONRESULTS]** and **[dbo].[EXTERNAL_ANALYSES_TRANSLATIONS]** tables using *SiteID* and *AnalysisID*. For example, in the **EXTERNAL_ANALYSES_TRANSLATIONS** table, an entry with Name = 'Application for Windows', SiteID = 3093, and ContentID = 34 corresponds to application data stored in the ResultsText column of the **QUESTIONRESULTS** table with the same SiteID = 3093 and AnalysisID = 34. We’ve also identified similar IDs for other application and hardware information.
Based on this, we are currently assuming that these IDs remain consistent across environments and are using them directly in queries against the **QUESTIONRESULTS** table to fetch the relevant data.
Is there an alternative or a more reliable method to retrieve Application and Hardware information from BigFix?
Welcome @nirali.shah !
The approach you’re considering is not recommended, as it is neither scalable nor maintainable. It introduces long-term overhead and dependencies on SQL experts or individuals with deep knowledge of the BigFix database schema. Direct interaction with the SQL database can also pose a risk to data integrity and is discouraged by HCL.
Recommended Best Practices:
- Use the BigFix REST API: This is the officially supported and scalable method for extracting data. The API allows for secure, flexible, and efficient access to all endpoint and configuration data.
- Leverage Web Reports: BigFix Web Reports provides built-in capabilities to generate reports using existing retrieved properties, especially for application and hardware inventory. This eliminates the need for complex queries and supports export in various formats (CSV, PDF, etc.).
- Avoid direct SQL access: As per HCL documentation, direct queries against the BigFix database are unsupported and can be risky. Schema changes or upgrades may break your logic unexpectedly.
As a follow up, I too would highly recommend avoiding the use of the Database API for this use case. There are several reasons for this including (but not limited to):
- Performance and functional impact to the BigFix system as you’d be querying the core database (including high potential for locks)
- The database schema can (and has) changed in the past…this means that queries may need to be written as part of upgrades, and that is clearly not ideal
- The IDs in question are not guaranteed to be consistent across environments
- The BigFix database is optimized for speed of data insertion rather than these sorts of queries
Instead, I’d recommend leveraging the REST API. More specifically, I’d recommend using the query method (https://developer.bigfix.com/rest-api/api/query.html) with Session Relevance.
Depending on your version of BigFix, consider also BigFix Explorer.
We can help with potential Session Relevance queries, but the following post is a great one for reference on how to generate efficient queries for Computer properties: Efficient Session Relevance Query for Computer Properties
Currently, we are using the BigFix REST API, specifically leveraging relevance queries to retrieve application and compliance findings. However, since the API responses are in XML format, parsing the data—especially in large environments—takes considerable time, which impacts the performance of our application.
To address this performance issue, we have transitioned to using the Database API approach to fetch the data more efficiently.
Just to clarify, while the default BigFix REST API response is in XML, you can configure your queries to return data in JSON format as well, which is often easier to parse and more efficient for modern applications.
Having worked in environments with over 200K endpoints and currently managing an infrastructure of 85K+ devices, I can confirm that efficient use of the REST API is very much feasible even at scale. We’ve successfully built data lakes for patching, compliance, and ad hoc reporting, all using the REST API with JSON responses.
The key is to optimize your relevance queries to retrieve only the necessary data instead of pulling everything. Performance bottlenecks typically occur when queries are not properly scoped or when too much data is requested at once.
See below:
There are several community experts here, and also HCL’s Professional Services and Support channels, who can help review and fine-tune your API queries if you’re facing delays.
In short:
The BigFix REST API is the recommended approach and when used efficiently, should not result in performance issues. I’d encourage reviewing your current implementation and optimizing it before moving to unsupported or less scalable alternatives.