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.
Thanks for providing us with the info about getting a JSON response using REST APIs. Now, we are using the REST APIs with relevance queries and json output to fetch the different information.
Here are the queries that we will use for fetching data:
- Computer Properties: elevance=(ids of computer of it, names of property of it, values of it) of results whose ( id of computer of it is contained by set of (361288; 16730123; 546038964; 1613484056; 549812563) and name of property of it is contained by set of (“OS”; “IP Address”; “Device Type”; “Computer Name”; “DNS Name”; “OS Family”; “OS Name”; “OS Version”; “RAM”; “BIOS”; “Active Directory Path”; “Computer Manufacturer - Windows”; “Computer Model - Windows”; “Machine Model - Mac OS X”; “Identifying Number - Windows”; “Serial Number - Mac OS X”; “Brand String of CPU - Windows”; “Brand String of CPU - Linux”; “Number of Processors - Windows”; “Number of Processors - Linux”; “Number of Processor Cores - Windows”; “Number of Processor Cores - Mac OS X”; “Number of Processor Cores - Linux”; “Agent Type”; “Last Report Time”; “License Type”; “Locked”; “Subscribed Sites”; “User Name”; “Client Settings”; “MAC Address”; “IPv6 Address”)) of bes properties
- Installed Applications: relevance=(ids of computer of it, values of it) of results whose (id of computer of it = 361288) of bes properties whose (name of it starts with "Installed Applications - ")
- Groups: relevance=(id of it, (name of it) of bes computer groups of it) of bes computers whose (id of it is contained by set of (361288; 16730123; 546038964; 549812563; 1613484056))
- Compliance Findings: relevance=(id of computer of it, relevant flag of it, name of fixlet of it, name of site of fixlet of it, last became relevant of it) of results whose (id of computer of it = 361288) of bes fixlets whose ((name of site of it as lowercase contains “stig”) or (name of site of it as lowercase contains “cis”))
Let us know if we can optimize the query