BigFix API - Query - Results with JSON format that is unacceptable for the consumer

1

Hi,

I’m running the following API Request:

https://BIGFIXURL:52311/api/query?relevance=((if (not exists bes properties “Computer Name”) then “No Relevant” else (if (not exists values of results from (bes property “Computer Name”) of it) then “No Relevant” else (“Computer Name”,values of result from (bes property “Computer Name”) of it) as string)),(if (not exists bes properties “Yahav Number”) then “No Relevant” else (if (not exists values of results from (bes property “Yahav Number”) of it) then “No Relevant” else (“Yahav Number”,values of result from (bes property “Yahav Number”) of it) as string)),(if (not exists bes properties “Device Type”) then “No Relevant” else (if (not exists values of results from (bes property “Device Type”) of it) then “No Relevant” else (“Device Type”,values of result from (bes property “Device Type”) of it) as string)),(if (not exists bes properties “Manufacturer”) then “No Relevant” else (if (not exists values of results from (bes property “Manufacturer”) of it) then “No Relevant” else (“Manufacturer”,values of result from (bes property “Manufacturer”) of it) as string)),(if (not exists bes properties “Computer Model -Windows”) then “No Relevant” else (if (not exists values of results from (bes property “Computer Model -Windows”) of it) then “No Relevant” else (“Computer Model -Windows”,values of result from (bes property “Computer Model -Windows”) of it) as string)),(if (not exists bes properties “Computer Serial Number”) then “No Relevant” else (if (not exists values of results from (bes property “Computer Serial Number”) of it) then “No Relevant” else (“Computer Serial Number”,values of result from (bes property “Computer Serial Number”) of it) as string)),(if (not exists bes properties “Ip Address”) then “No Relevant” else (if (not exists values of results from (bes property “Ip Address”) of it) then “No Relevant” else (“Ip Address”,values of result from (bes property “Ip Address”) of it) as string)),(if (not exists bes properties “Full Operating System Name and Service”) then “No Relevant” else (if (not exists values of results from (bes property “Full Operating System Name and Service”) of it) then “No Relevant” else (“Full Operating System Name and Service”,values of result from (bes property “Full Operating System Name and Service”) of it) as string)),(if (not exists bes properties “Version Of Operating System”) then “No Relevant” else (if (not exists values of results from (bes property “Version Of Operating System”) of it) then “No Relevant” else (“Version Of Operating System”,values of result from (bes property “Version Of Operating System”) of it) as string)),(if (not exists bes properties “Architecture Of Operating System”) then “No Relevant” else (if (not exists values of results from (bes property “Architecture Of Operating System”) of it) then “No Relevant” else (“Architecture Of Operating System”,values of result from (bes property “Architecture Of Operating System”) of it) as string)),(if (not exists bes properties “RAM”) then “No Relevant” else (if (not exists values of results from (bes property “RAM”) of it) then “No Relevant” else (“RAM”,values of result from (bes property “RAM”) of it) as string)),(if (not exists bes properties “CPU”) then “No Relevant” else (if (not exists values of results from (bes property “CPU”) of it) then “No Relevant” else (“CPU”,values of result from (bes property “CPU”) of it) as string)),(if (not exists bes properties “Total Space on System Drive”) then “No Relevant” else (if (not exists values of results from (bes property “Total Space on System Drive”) of it) then “No Relevant” else (“Total Space on System Drive”,values of result from (bes property “Total Space on System Drive”) of it) as string)),(if (not exists bes properties “Free Space on System Drive”) then “No Relevant” else (if (not exists values of results from (bes property “Free Space on System Drive”) of it) then “No Relevant” else (“Free Space on System Drive”,values of result from (bes property “Free Space on System Drive”) of it) as string)),(if (not exists bes properties “Boot Time”) then “No Relevant” else (if (not exists values of results from (bes property “Boot Time”) of it) then “No Relevant” else (“Boot Time”,values of result from (bes property “Boot Time”) of it) as string)),(if (not exists bes properties “User Name”) then “No Relevant” else (if (not exists values of results from (bes property “User Name”) of it) then “No Relevant” else (“User Name”,values of result from (bes property “User Name”) of it) as string))) of bes computers&output=json

The result JSON file shows as the following example:

{

“result”: [

{

  "type, home",

  "number, 212 555-1234"

},

{

  "type, office",

  "number, 646 555-4567"

}

]

}

The consumer of the JSON file wants the Format to be as follows:

{

“result”: [

{

  "type": "home",

  "number": "212 555-1234"

},

{

  "type": "office",

  "number": "646 555-4567"

}

]

}

Is it possible?

1 Like
2

I don’t see what that giant query is trying to do. Can you shorten it to a simpler example to demonstrate what you’re looking for?

3

@JasonWalker Sorry about the late response

So about the Query itself - I’ve shorten it to 4 properties , one of there does not exists on my lab

This is how it is shown on the Web Reports QNA page:

q: ((if (not exists bes properties “Computer Name”) then “No Relevant” else (if (not exists values of results from (bes property “Computer Name”) of it) then “No Relevant” else (“Computer Name”,values of result from (bes property “Computer Name”) of it) as string)),(if (not exists bes properties “CPU”) then “No Relevant” else (if (not exists values of results from (bes property “CPU”) of it) then “No Relevant” else (“CPU”,values of result from (bes property “CPU”) of it) as string)),(if (not exists bes properties “Total Space on System Drive”) then “No Relevant” else (if (not exists values of results from (bes property “Total Space on System Drive”) of it) then “No Relevant” else (“Total Space on System Drive”,values of result from (bes property “Total Space on System Drive”) of it) as string)),(if (not exists bes properties “Free Space on System Drive”) then “No Relevant” else (if (not exists values of results from (bes property “Free Space on System Drive”) of it) then “No Relevant” else (“Free Space on System Drive”,values of result from (bes property “Free Space on System Drive”) of it) as string))) of bes computers
A: ( Computer Name, ECTXPVS01 ), ( CPU, 2200 MHz Xeon ), No Relevant, ( Free Space on System Drive, 32283 MB )
A: ( Computer Name, WIN-B8KTNBA20T1 ), ( CPU, 2200 MHz Xeon ), No Relevant, ( Free Space on System Drive, 36005 MB )

Attached Query API with XML and JSON outputOutput.pdf (110.1 KB)

I would like the JSON Output to be as followed: excpected-Output.pdf (97.4 KB)

4

Ah, ok, think I understand.
The JSON result of a REST API query is simply going to be an array of arrays. The result itself will not be a Dictionary type that would include column headers - to get the output you want, you will need to do that in your scripting language, not in the API query itself.

It’s just a matter of looping through the results and converting the arrays into whatever dictionary format you want.

5

@JasonWalker I’ve talked with the consumer of the content it self he wanted an API query that will return as a JSON result as I’ve written down.

At first, I told him that he could get the XML result and make the adjustments he need on his side. (Like you said, with scripting language)

He countered that the Query API does not return the result with the ‘Right’ JSON syntax.

I thought about using the Json Value and with that returning a ‘valid’ JSON like he wanted

Have you encountered such requests of that nature?

6

Yes, I’ve encountered such requests. You need to handle that in the client-side code. Anything you do to the query itself is going to be considered a Result of the query, not inserting metadata like column names or dictionary keys.
This is fairly simple to do in Python. I’ll try to find some time to post something later, but it’s fairly easy to find examples of parsing arrays into dictionaries in Python. Other languages like PowerShell I’m sure can handle it as well.

7

I know how to do that - Thanks for your Help!

I will make a follow up with this response :slight_smile:

8

I just posted an example to my GitHub page of doing this in Python at https://github.com/Jwalker107/BigFix/blob/master/API%20Samples/Session%20Relevance%20to%20JSON%20Dictionary.py

The example there includes a REST, reading/writing JSON, etc. but the important parts for this discussion is mapping a list of columns to create dictionaries from the result:

result_columns=[
 "Computer ID", 
 "Computer Name", 
 "CPU", 
 "Total Size of System Drive", 
 "Free Space on System Drive"
]
# After the query has been run and results stored in 'response':
response_json=json.loads(response.text)
results_array=[]
column_count=len(result_columns)
# Loop through result rows
for result in response_json["result"]:
    # Loop through each column of the result, creating a dictionary with key from column map and value from query result.
    result_dict={}
    for i in range(0, column_count):
        result_dict[result_columns[i]]=result[i]
    results_array.append(result_dict)
1 Like