Need some help with Rest API Relevance query

All,
I need some help with querying our BigFix db to export some data for reporting in our SQL DB. Currently i have web report but it’s a bit cumbersome and time consuming, like to automate this
I’ve tried using Rest API with some session relevance and I’m stuck at getting all our properties collected

Below will get me some Info
(ids of computers of it, hostnames of computers of it, last report times of computers of it, values of it) of results of bes properties whose (name of it = "_PG_Last_User")

I like to collect following info

  • id
  • computer name
  • Operating System
  • Group membership
    Following custom properties
  • _PG_pcserialnumber
  • _PG_Manufacturer
  • _PG_pcmodel
  • _PG_LastUser
  • _PG_ImageType

To make this even more efficinet like to call this every xx hours and only get info from machines that have updated in that period to reduce stress on our DB, if there is any other efficient way to accomplilsh this would be appreciated.

thx for all help, appreciated.

1 Like

Hello!

I would highly recommend having a look at brolly33’s post about generating efficient session relevance queries for computer properties here as a means to query the REST API for the properties of interest:

You can also add a filter to the ‘bes computers’ object to return only those endpoints that have reported in with the last X amount of time based on your query frequency to reduce the dataset to deltas (thought it may still be good to do full updates periodically).

Once you’ve had a chance to review the information in the link above, let us know if you have any further questions, or would like additional assistance.

1 Like

Hi, i’ve managed to get the session relevance to work using below query/statement using various links I could find back on various forums (Thx to all of you contributing)

https://<servername>:29450/api/query?relevance=(ids of it, names of it, operating systems of it, values of results from (BES Property "_CustProp1") of it, values of results from (BES Property "_CustProp2") of it, values of results from (BES Property "CustProp3") of it, values of results from (BES Property "CustProp4") of it, values of results from (BES Property "_CustProp5") of it, values of results from (BES Property "_CustProp6") of it, values of results from (BES Property "_CustProp7") of it,values of results from (BES Property "Full Operating System Name and Service Pack Level - Windows") of it,values of results from (BES Property "Active Directory Path") of it, agent version of it,values of results from (BES Property "_CustProp8") of it, last report time of it) of computers of results **whose (value of it = "15649485") of bes property "ID"**

I would also like to include the Computer groups that this device is member of ?
I did find some info to get all computer groups and see the membership but

?relevance=names of bes computer groups
?relevance=names of members of bes computer group whose (name of it = “ComputerGroup”)

How can I do the opposite and list all groups that computer is member of?

Follow-up questions

  • the above session relevance is that optimal way to collect this info
  • I’m planning to query our DB every xx hours and just retrieve machines that have updated in that interval so to reduce the volume of data that will be returned ==> Is this acceptable?

Alternative approach, would it be better to reduce the number of properties returned for REST API Call but do 2-3 calls vs. 1 call

I’m following same practice as when querying Active Directory and check USN value.

  • Our BigFix DB has approx. 130,000 devices

Thx again for all the help.

1 Like

Here’s some sample session relevance that returns the desired properties, and computer group information, in a more efficient single query, for devices that have reported in within the last 24 hours (again, this is based on the methods described in Efficient Session Relevance Query for Computer Properties):

(
(concatenation “;” of values of results (item 0 of it, elements of item 1 of it))
, name of item 0 of it | “missing name”
, (concatenation “;” of values of results (item 0 of it, elements of item 2 of it))
, (concatenation “;” of names of bes computer groups of item 0 of it)
, (concatenation “;” of values of results (item 0 of it, elements of item 3 of it))
, (concatenation “;” of values of results (item 0 of it, elements of item 4 of it))
, (concatenation “;” of values of results (item 0 of it, elements of item 5 of it))
, (concatenation “;” of values of results (item 0 of it, elements of item 6 of it))
, (concatenation “;” of values of results (item 0 of it, elements of item 7 of it))
) of (
elements of item 0 of it
,item 1 of it
,item 2 of it
,item 3 of it
,item 4 of it
,item 5 of it
,item 6 of it
,item 7 of it
) of (
set of BES computers whose (last report time of it > (now - 24 * hour))
, set of bes properties whose (name of it as lowercase = (“ID”) as lowercase AND reserved flag of it)
, set of bes properties whose (name of it as lowercase = (“OS”) as lowercase AND reserved flag of it)
, set of bes properties whose (name of it as lowercase = (“_PG_pcserialnumber”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“_PG_Manufacturer”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“_PG_pcmodel”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“_PG_LastUser”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“_PG_ImageType”) as lowercase)
)

And the same query collapsed:

((concatenation ";" of values of results (item 0 of it, elements of item 1 of it)), name of item 0 of it | "missing name", (concatenation ";" of values of results (item 0 of it, elements of item 2 of it)), (concatenation ";" of names of bes computer groups of item 0 of it), (concatenation ";" of values of results (item 0 of it, elements of item 3 of it)), (concatenation ";" of values of results (item 0 of it, elements of item 4 of it)), (concatenation ";" of values of results (item 0 of it, elements of item 5 of it)), (concatenation ";" of values of results (item 0 of it, elements of item 6 of it)), (concatenation ";" of values of results (item 0 of it, elements of item 7 of it))) of (elements of item 0 of it,item 1 of it,item 2 of it,item 3 of it,item 4 of it,item 5 of it,item 6 of it,item 7 of it) of (set of BES computers whose (last report time of it > (now - 24 * hour)), set of bes properties whose (name of it as lowercase = ("ID") as lowercase AND reserved flag of it), set of bes properties whose (name of it as lowercase = ("OS") as lowercase AND reserved flag of it), set of bes properties whose (name of it as lowercase = ("_PG_pcserialnumber") as lowercase), set of bes properties whose (name of it as lowercase = ("_PG_Manufacturer") as lowercase), set of bes properties whose (name of it as lowercase = ("_PG_pcmodel") as lowercase), set of bes properties whose (name of it as lowercase = ("_PG_LastUser") as lowercase), set of bes properties whose (name of it as lowercase = ("_PG_ImageType") as lowercase))

1 Like

Aram,
thx for the info I do have question, I’m trying to do this call using curl.exe I’m getting stuck with
error “A string constant had no ending quotation mark.”

When I try this using BigFix Session Relevance tester it works fine and provides the necessary output, but doing the same using curl.exe or REST API Tester.

Simple test
names+of+BES+Computer+groups

provides below output



PC1-Windows
ComputerGroup SubnetAddress


2.371ms
Plural


Trying the following

concatenation+";"+of+names+of+bes+computer+groups
I get This expression could not be parsed. Trying it BigFix Session relevance tester then it works fine.

Any help ideas would be appreciated. Thank you again

1 Like

Found the solution thx to post from @JasonWalker

For those interested below small snippet of “VBScript” code that would do following

  • run Curl.exe command and write output to xml file
  • Read XML File for processing in other applications (Import into SQL DB, WebSite)
Const GlobalLog = "C:\RESTAPI\Logs\RestAPIXMLRead.log"
Const cForReading = 1, cForWriting = 2, cForAppend = 8
Const cTristateUseDefault = -2, cTristateTrue = -1, cTristateFalse = 0

Set oShell = CreateObject("WScript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objXMLDoc = CreateObject("Microsoft.XMLDOM")

Log ""
Log "Start Program"

sCmd = "curl.exe -k --user USERNAME:PASSWORD -X POST ""https://SERVER:PORT/api/query"" --data-urlencode ""relevance@D:\RESTAPI\Test3.txt"" > C:\HP\RestOutput.xml"

Log " start REST API Query"
oShell.Run sCmd, 1, True
Log " Finished REST API Query"

If FSO.FileExists("C:\RESTAPI\RestOutput.xml") Then
  objXMLDoc.async = False
  objXMLDoc.load("C:\RESTAPI\RestOutput.xml")
  Log "Error File not found"
  WScript.Quit(0)
End If

strQuery = "/BESAPI/Query/Result/Tuple"
Set NodeList = objXMLDoc.documentElement.selectNodes(strQuery)
for i = 0 to (NodeList.length-1)
  set ChildNodes = NodeList.item(i).childNodes
  For j = 0 To (ChildNodes.length-1)
    If Len(sInfo) > 0 Then
      sInfo = sInfo + "|" + ChildNodes.item(j).Text
    Else
      sInfo = ChildNodes.item(j).Text   
    End If  
  Next
  Log "  ++ sInfo[" & i & "]: " & sInfo
  sInfo = ""  
next

Log "End Program"

Sub Log (strLog)

	If Not FSO.FolderExists ("C:\RESTAPI\Logs") Then
	  FSO.CreateFolder "C:\RESTAPI\Logs"
	End If  
	Dim LogFile: Set LogFile = FSO.OpenTextFile(GlobalLog, 8, True)
	LogFile.WriteLine Now() & " - " & strLog
	LogFile.Close
End Sub

Thx everyone for helping on this much appreciated.

3 Likes

@dgendera Did you make any edits to this script ? I cannot get it to generate the RestOutput.xml file however I can create it fine using Curl.exe called from command line using the same session relevance ?

1 Like

I haven’t tried this in VBScript honestly, but a few years back when I used VBScript heavily I seem to recall some XMLHTTP objects that could do web requests directly (without curl at all).

That said, if you’re going to script REST API stuff, I’d strongly recommend a scripting language that has REST and XML support natively. Python or PowerShell are probably more prevalent, and have some examples at https://developer.bigfix.com

(now I’m going to duck-and-cover, I don’t mean to start the script languages wars)

1 Like

Ha ! what’s strange is that this vbs calls curl.exe. It should do exactly what I need, been playing for days and not having much luck so loosing interest rapidly. End goal was to put an end to direct sql queries. Would you happen to have an equivalent to this script in PS ?

1 Like

This should work fine in vbscript I’m currently using this, it runs every hour, and takes 2-3 minutes to complete I do use template file “D:\RESTAPI\Test3.txt” (Thx @JasonWalker / @Aram ) as I was having problems with the correct syntax.
I also started of with running curl.exe manually with various switches to get the right output/fomat and then moved this into vbscript. I’m probably old school but like to keep things simple so vbscript is my preferred language but agree this can also be accomplished in PowerShell, Python etc.

1 Like

I can’t get past this error -

image

Nick,

try and replace line 26 with below code

Set objXMLDoc = CreateObject(“Msxml2.DOMDocument.6.0”)

You need to install MSXML6.0 on the machine where you want to run the script.
during my testing I noticed huge improvement when using MSXML6.0 vs. default version 3.0.

You can google for MSXML60 and install it and then script should run fine.

I wonder if it’s a path issue to curl.exe? Maybe use the full pathname?

I now get image
it still fails to create RestOutput.xml before it throws that error.

Can you post your script?
Looks like a bug in the earlier script - it saves curl output to one file path but then tries to read the file from a different path

1 Like

Const GlobalLog = "C:\temp\RESTAPI\Logs\RestAPIXMLRead.log"
Const cForReading = 1, cForWriting = 2, cForAppend = 8
Const cTristateUseDefault = -2, cTristateTrue = -1, cTristateFalse = 0

Set oShell = CreateObject(“WScript.Shell”)
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set objXMLDoc = CreateObject(“Microsoft.XMLDOM”)

Log ""
Log “Start Program”

sCmd = “c:\temp\restapi\curl.exe -k --user USER:PASSWORD -X POST ““https://SERVER:PORT/api/query”” --data-urlencode ““relevance@C:\temp\RESTAPI\query - Copy.txt”” > C:\temp\RESTAPI\RestOutput.xml”

Log " start REST API Query"
oShell.Run sCmd, 1, True
Log " Finished REST API Query"

If FSO.FileExists(“C:\temp\RESTAPI\RestOutput.xml”) Then
objXMLDoc.async = False
objXMLDoc.load(“C:\temp\RESTAPI\RestOutput.xml”)
Log "Error File not found"
WScript.Quit(0)
End If

strQuery = “/BESAPI/Query/Result/Tuple"
Set objXMLDoc = CreateObject(“Msxml2.DOMDocument.6.0”)
for i = 0 to (NodeList.length-1)
set ChildNodes = NodeList.item(i).childNodes
For j = 0 To (ChildNodes.length-1)
If Len(sInfo) > 0 Then
sInfo = sInfo + “|” + ChildNodes.item(j).Text
Else
sInfo = ChildNodes.item(j).Text
End If
Next
Log " ++ sInfo[” & i & "]: " & sInfo
sInfo = ""
next

Log “End Program”

Sub Log (strLog)

If Not FSO.FolderExists ("C:\temp\RESTAPI\Logs") Then
  FSO.CreateFolder "C:\temp\RESTAPI\Logs"
End If  
Dim LogFile: Set LogFile = FSO.OpenTextFile(GlobalLog, 8, True)
LogFile.WriteLine Now() & " - " & strLog
LogFile.Close

End Sub

I will look into this and get you updated script.

1 Like

Nick,
find below updated script, I’ve added 2 SubRoutine (not needed) but keeps the code a bit clean.
For the Routine “ProcessXML” I’m just going over the different attributes it’s up to you to decide
how you want to handle this"

Const GlobalLog = “C:\temp\RESTAPI\Logs\RestAPIXMLRead.log”
Const cForReading = 1, cForWriting = 2, cForAppend = 8
Const cTristateUseDefault = -2, cTristateTrue = -1, cTristateFalse = 0
Const XMLOut = “C:\temp\RESTAPI\RestOutput.xml”

Dim oShell, FSO, objXMLDoc, sCmd, strQuery, i, j, NodeList, ChildNodes, sInfo, sOutput, dStartTime, iDuration, iCountComputers

Set oShell = CreateObject(“WScript.Shell”)
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set objXMLDoc = CreateObject(“Msxml2.DOMDocument.6.0”)

Log “”
Log “Start Program”

sCmd = “c:\temp\restapi\curl.exe -k --user USER:PASSWORD -X POST ““https://SERVER:PORT/api/query”” --data-urlencode ““relevance@C:\temp\RESTAPI\query - Copy.txt”” > C:\temp\RESTAPI\RestOutput.xml”

Log " start REST API Query"
oShell.Run sCmd, 1, True
Log " Finished REST API Query"

If Not FSO.FileExists(XMLOut) Then
Log “Error File not found”
WScript.Quit(0)
End If

ValidateXML()
ProcessXML()

Sub ProcessXML()

Log " Start Processing File " & XMLOut
Log " Need to process " & NodeList.length-1 & " Records"
For i = 0 to (NodeList.length-1)
Set ChildNodes = NodeList.item(i).childNodes
For j = 0 To (ChildNodes.length-1)
If Len(sInfo) > 0 Then
sInfo = sInfo + “|” + ChildNodes.item(j).Text
Else
sInfo = ChildNodes.item(j).Text
End If
Next
Next
End Sub

strQuery = “/BESAPI/Query/Result/Tuple”

for i = 0 to (NodeList.length-1)
set ChildNodes = NodeList.item(i).childNodes
For j = 0 To (ChildNodes.length-1)
If Len(sInfo) > 0 Then
sInfo = sInfo + “|” + ChildNodes.item(j).Text
Else
sInfo = ChildNodes.item(j).Text
End If
Next
Log " ++ sInfo[" & i & "]: " & sInfo
sInfo = “”
next

Log “End Program”

Sub Log (strLog)

If Not FSO.FolderExists (“C:\temp\RESTAPI\Logs”) Then
FSO.CreateFolder “C:\temp\RESTAPI\Logs”
End If
Dim LogFile: Set LogFile = FSO.OpenTextFile(GlobalLog, 8, True)
LogFile.WriteLine Now() & " - " & strLog
LogFile.Close
End Sub

Sub ValidateXML()

Dim oFile, iSizeOfFile, dStart
On Error Resume Next
Log " Start Validating " & XMLOut
dStart = Now()
strQuery = “/BESAPI/Query/Result/Tuple”
Set oFile = FSO.GetFile(XMLOut)
iSizeOfFile = Round(oFile.Size/1048576,2)
If FSO.FileExists(XMLOut) Then
objXMLDoc.async = False
objXMLDoc.load(XMLOut)
Log " Duration for loading file: " & DateDiff(“s”,dStart,Now()) & " second(s) - Size: " & iSizeOfFile & " MB"
Set NodeList = objXMLDoc.documentElement.selectNodes(strQuery)
If objxmldoc.parseError.errorCode <> 0 Then
Log " Error loading " & XMLOut & " - Error on line: " & objxmlDoc.parseError.line & " - Error Description: " & objxmldoc.parseError.reason & " - Error srcText: " & objxmldoc.parseError.srcText
Log " Re-writing the file to replace invalid characters. File: " & XMLOut
RewriteXMLFile()
Else
Log " Using MSXML 6.0 to process " & XMLOut
End If
End If

Log " Finished Validating " & XMLOut
On Error GoTo 0
End Sub

Log Output
05/02/2021 12:07:00 - Start Program
05/02/2021 12:07:00 - start REST API Query
05/02/2021 12:07:18 - Finished REST API Query
05/02/2021 12:07:18 - Start Validating C:\temp\RESTAPI\RestOutput.xml
05/02/2021 12:07:18 - Duration for loading file: 0 second(s) - Size: 2.25 MB
05/02/2021 12:07:18 - Using MSXML 6.0 to process C:\temp\RESTAPI\RestOutput.xml
05/02/2021 12:07:18 - Finished Validating C:\temp\RESTAPI\RestOutput.xml
05/02/2021 12:07:32 - Start Processing File C:\temp\RESTAPI\RestOutput.xml
05/02/2021 12:07:35 - Need to process 2044 Records

1 Like

Very strange, the output file does not get created using this method

sCmd = “c:\temp\restapi\curl.exe -k --user USER:adm-uname:pword -X POST ““https://server:port/api/query”” --data-urlencode ““relevance@C:\temp\RESTAPI\query - Copy.txt”” > C:\temp\RESTAPI\RestOutput.xml”

however it generates fine when run via command line

“c:\temp\RESTAPI\curl.exe” -k --user uname:pword -X POST “https://server:port/api/query” --data-urlencode “relevance@C:\Temp\RESTAPI\query - Copy.txt” > “C:\Temp\RESTAPI\RestOutput.xml”

if I generate the output file using the command line method, your vbs processes it successfully.

You are treating the --user parameter differently between the two?

–user USER:adm-uname:pword

–user uname:pword

1 Like