(imported topic written by taltrade91)
I’m able to view groups and view machines but not able to make a relationship between the groups and machines.
We need a sql query that will return machine names by specifiying a group name as an input parameter.
Please help!
(imported comment written by taltrade91)
I should add, I’ve already read through the BESDatabaseAPIReference-7.0[1].pdf file, but was still unsuccessful.
Phone support wasn’t able to help either (although the issue has been escalated for the time being).
Any help at all is greatly appreciated! Thanks
(imported comment written by BenKus)
Hi taltrade,
Groups are implemented and stored using Fixlets and Fixlet results, which doesn’t make the SQL query easy… Do you need to use SQL? The SOAP API makes this kind of thing much easier…
Here is the session query you would use:
names of members of bes computer groups whose (name of it = “”)
Ben
(imported comment written by taltrade91)
Thanks Ben, I’ll run that by one of our developers. I’ll let you know if we’re able to use SOAP instead.
(imported comment written by taltrade91)
Isn’t there a way to list “Computer Groups” as a property of a computer? If so, this would be a snap, as far as queries are concerned.
(imported comment written by oxnumbernine91)
taltrade
Isn’t there a way to list “Computer Groups” as a property of a computer? If so, this would be a snap, as far as queries are concerned.
We also have a need for the exact same thing.
(imported comment written by SY57_Jim_Montgomery)
This is related to version 7 of the database, and it also does NOT use views, so getting this to work for yourself is up to you, definitely not the BigFix tech support guys. That being said…
If you know what the group ID is you are in good shape:
select isnull(dbo.fn_ExtractField(
'CustomSiteName', 0, p.Fields ),
'Manual') +
': ' + p.name as [name], p.id from local_object_defs p join versions v on p.version = v.latestversion and v.sitename=
'ActionSite' and p.version=v.latestversion where dbo.fn_extractfield(
'Fixlet Type', 0, p.fields)=
'ComputerGroup' AND not p.parentid=1 order by isnull(dbo.fn_ExtractField(
'CustomSiteName', 0, p.Fields ),
'Manual') +
': ' + p.name
This will show you all the custom groups, and what custom sites they come from. The funny thing is if its a manual group, it doesn’t have a Custom Site Name field value, so this query will just stick Manual in there.
After you get the group id, then comes the fun part. Ben was not kidding, the SQL query isn’t easy, but it is doable. The strategy below is to use a subquery to create our own “table” to join against. We’ll make a groups “table” that just shows the name, id, and total count of the group id you put in there. (towards the bottom you’ll see a spot to put your group id in) (Coincidentally, if you have a list of several groups you want to look at, this query works great)
After we have a “table” of groups, then we just join that to fixletresults (to see what computers are “relevant” members of the group). Then you join it to computers and questionresults (the later is so you can get the computer name back).
select c.computerid , g.name as groupname , g.groupcount , q.resultstext as Computername from computers c join questionresults q on q.computerid=c.computerid join fixletresults f1 on c.computerid=f1.computerid join (--GROUPS select p5.name, p5.id, count(*) as groupcount from local_object_defs p5 join versions v5 on p5.version=v5.latestversion join sitenamemap s5 on v5.sitename=s5.undecoratedsitename join fixletresults f5 on f5.id=p5.id where NOT p5.parentid=1 and p5.isfixlet=1 --and p5.id in (@Groups) AND (p5.id =1191) -----------------add your group id here ------------------- and dbo.fn_ExtractField(
'Fixlet Type', 0, P5.Fields ) =
'ComputerGroup' and v5.sitename=
'ActionSite' and f5.isrelevant=1 and f5.siteid=s5.siteid group by p5.id, p5.name) as g on f1.id=g.id where f1.isrelevant=1 and c.isdeleted=0 and q.analysisid=4 and q.propertyid=1