XQL Query to find all Mac_OS endpoints and return the version of Netskope installed on the hosts.

[rhodgkins1]

I am new to using xql and I am having trouble getting the information I need using a search query. I need to pull a list of all Mac_OS hosts and then within the applications field return the version of NetSkope installed on the client. I have started with the following query which returns the basic data, but I am lost as to how to extract the information from the applications field which is in a json format. Here is the query along with the sample block from the json data that I need to get. This JSON data is located within the Applications field. 

dataset = host_inventory
| filter os_type contains "OS_MAC"
| filter applications contains "netskope"
| fields host_name, applications

{
"identifier": "com.netskope.client.Netskope-Client",
"install_date": null,
"installed_for_sid": "global",
"is_from_appstore": false,
"key_name": null,
"manager_name": "macOS",
"application_name": "Netskope Client",
"raw_version": "118.1.1.2131 (118.1.1.2131)",
"uninstall_string": null,
"url_info_about": null,
"url_update_info": null,
"vendor": "netSkope, Inc.",
"version": "118.1.1.2131",
"win_installer": null

[nsinghvirk]

Hello @rhodgkins1 

Thanks for reaching out on LiveCommunity!

Please try using below query. json_extract() function can be used to extract JSON fields.

dataset = host_inventory
| filter os_type contains "OS_MAC"
| filter applications contains "netskope"
| arrayexpand applications
| alter abc = json_extract(applications ,"$.version")
| fields host_name, abc as Application_version

Please click Accept as Solution to acknowledge that the answer to your question has been provided.

[rhodgkins1]

Hello @nsinghvirk thank you very much for your reply. This is getting much closer. However this is returning the version of every piece of software on the endpoints. I just need to have each host return the version of NetSkope installed on the host. In the code block below is the data I need to extract. If "Netskope Client" is the application_name, then I need the version. 

{
"identifier": "com.netskope.client.Netskope-Client",
"install_date": null,
"installed_for_sid": "global",
"is_from_appstore": false,
"key_name": null,
"manager_name": "macOS",
"application_name": "Netskope Client",
"raw_version": "118.1.1.2131 (118.1.1.2131)",
"uninstall_string": null,
"url_info_about": null,
"url_update_info": null,
"vendor": "netSkope, Inc.",
"version": "118.1.1.2131",
"win_installer": null