Importing data from BigFix database

[grk_ravi]

Hello,
We have an use case for an application to extract users having access to Assets from BigFix. Their database connector can leverage SQL to directly read from BigFix SQL Server database.
The T-SQL is able to pull data from BES_COLUMN_HEADINGS but its not able to split the multi valued attributes like [Local Administrators] or UserNames. What is the delimiter for values within these attributes?

Name Value
ComputerID A11110
Local Administrators priv.abc A11110/domain admin A11110/web_admin

We tried delimiters like tab, CRLF, LF, space but not getting data in a proper format. We prefer to use SQL automation as its very straightforward yet powerful and widely adopted with other products.

[Aram]

Before going further down the SQL path, is REST API really not an option? That is the much preferred mechanism from a BigFix perspective…

Otherwise, the delimiter is LF… char(10)

[grk_ravi]

SQL is low code and also we don’t have developers on the team to work on the REST API. Good news, we got the SQL working with using LF as delimiter. But surprised as the data for many admins are not stored as expected on the table. We are seeing data like op* instead of Admin name and no idea what these values are -

Computer ID Computer Name Device Type Admin Type Admin User
100234 A22406 Laptop Client Admin priv.jacobs
100234 A22406 Laptop Client Admin priv.benky
100234 A22406 Laptop Client Admin _op_129
100234 A22406 Laptop Client Admin _op_131

[grk_ravi]

We got blocked here as the admin names are not showing up expected when we split the admin name attribute with LF delimiter. There are lot of admin names showing up as “op*” and we have a ticket to investigate this issue.

We now want to look at the REST API. Is there a way we can pull the multivalued admin name with one Admin on each row for the associated Server?

Thanks

[trn]

Session relevance along the lines of

	(
		name of item 0 of it , masthead operator name of item 1 of it , name of item 1 of it 
	)
	of 
	(
		item 0 of it , elements of item 1 of it 
	)
	of 
	(
		it , administrator set of it 
	)
	of bes computers 

could be plugged into your RestAPI call

Will return a lot of data…

[Aram]

For clarity, are you trying to return:

1. Which BigFix Users (operators) have access to which endpoints?
2. Which system users (local? domain users/groups? both?) that have access to which endpoints? And if so, which OSes are in scope?

[grk_ravi]

We are trying to establish visibility on “who has access to what endpoints (servers, laptops, etc)” within our IGA. So we need to import all the endpoints from BigFix along with their associated “User Names”, “Local Administrators” and “Client Administrators”. All OSes are in scope. We are able to accomplish this the SQL way but the client admins are not showing up accurately, hence wanted to try the REST API way.

[JasonWalker]

I’m afraid that’s still not clear to me.
Are you trying to retrieve the members of the local Administrators group on each target, or trying to retrieve the BigFix operator names with access to the endpoint?

[grk_ravi]

Basically whatever admin names that BigFix is able to pull/store from endpoints. If BigFix can decompose a group and store individual members, great. I am able to see names and groups in these 3 attributes “User Names”, “Local Administrators” and “Client Administrators” in Web Reports; hoping to see the same on the SQL or REST API.

[JasonWalker]

I’ve had to shut down my server for the day due to severe weather, I’ll try some queries tomorrow to firm this up. But recognize that your approach, of trying to have a separate row for each result, will result in a huge amount of data that’s probably not useful - because we would need separate rows for the cross-product of every value.

Realize that the “User Names” property only reflects the currently logged-on users - not every user who can log on, just who is logged on right now.

“Client Administrators” refers to the BigFix Operator accounts who can administer the computer in BigFix.

“Local Administrators” is not a built-in BigFix property; it sounds like a Custom Property set up for your deployment. If it shows up in Web Reports, it can certainly be queried through the API, but I can’t tell you whether it shows Users, Groups, or actually expands the members of domain groups that are nested into the local Administrators group. Any of those could be valid.

The cross-product problem is this - for a single computer, if you have two logged-on users returned in the “User Names” property, ten BigFix Operators with management rights, and twenty members of the local Administrators group - then to represent every unique combination in CSV, you’d have 2x10x20 rows, or four hundred rows for this single computer.
It’s much more likely that you’d need to either concatenate the results in each cell, or output separate spreadsheets for each property.

So, try to get some details on what is actually included in the results for each of these property types, and how you want it to appear in the output, and I can help with a query on it tomorrow if it remains unsolved until then.

[grk_ravi]

Currently we have set up 3 SQL views one each for “User Names”, “Local Administrators” and “Client Administrators” to expand the attributes by LF char(10). Looking at the data itself, “User Names” are populated for laptop with laptop owner only. There are only few groups populated for “Local Administrators”. The biggest is the “Client Administrators” with few groups and mostly admin names.
So, yes, the “Client Administrators” view has much more data in it. And this is the view that has “%op%” in most of the rows instead of the actual admin names and we would use REST API hoping to get actual names.