Software Inventory query

[Piotr_Kowalczyk]

Hi,

I'm using following query to get software inventory and it is working well. However to the results, as last column, I would like to add number of hosts which have particular software.  Could somebody advise how to do this please?

dataset = host_inventory
| arrayexpand applications
| alter software = json_extract(applications, "$.application_name"),software_vendor = json_extract(applications, "$.vendor")
| filter os_type = ENUM.OS_WINDOWS and product_type != ENUM.SERVER
| dedup software 
| fields software,software_vendor

[jmazzeo]

Hi @Piotr_Kowalczyk, thanks for reaching us using the Live Community.

Please try this one:

dataset = host_inventory
| arrayexpand applications
| alter software = json_extract(applications, "$.application_name"),software_vendor = json_extract(applications, "$.vendor")
| filter os_type = ENUM.OS_WINDOWS and product_type != ENUM.SERVER
| fields software,software_vendor, agent_name 
| comp count(agent_name) as installations by software,software_vendor 
| sort desc installations 

[aspatil]

Hello @Piotr_Kowalczyk ,

Agent name may not be the good field to use for your use case.

agent_name is endpoint alias value . It is not necessarily all endpoints would have value defined in endpoint alias. Instead replace it with the hostname.

dataset = host_inventory
| arrayexpand applications
| alter software = json_extract(applications, "$.application_name"),software_vendor = json_extract(applications, "$.vendor"), software_verion = json_extract(applications, "$.version")
| filter os_type = ENUM.OS_WINDOWS and product_type != ENUM.SERVER
| fields software,software_vendor,software_verion ,host_name
|comp count(host_name ) as installations by software,software_vendor , software_verion
|sort desc installations

If you feel this has answered your query, please let us know by clicking on "mark this as a Solution". Thank you.

[aspatil · Accepted solution]

Hello @Piotr_Kowalczyk ,

Using dataset which runs for 90 days, you will get multiple entries of same endpoint which may result in discrepancy in count. Instead you can call the preset.

preset = host_inventory_applications
| fields application_name , vendor , version , endpoint_name
| comp count(endpoint_name) as installations by application_name , vendor , version
| sort desc installations

If you feel this has answered your query, please let us know by clicking on "mark this as a Solution". Thank you.

[Piotr_Kowalczyk]

Aspatil, many thanks for your answer. Indeed using preset was better option as it didn't multiple values when run on 30 days. Will you be able to tell me why dataset = host_inventory gives multiple values but preset = host_inventory_application doesn't please?

[Piotr_Kowalczyk]

Jmazzeo, many thanks for your reply. It was very close to what I was looking for, however when I run it on more than 24 hours it showed multiple values.

[aspatil]

Hello @Piotr_Kowalczyk ,

The reason is dataset contains the raw data. Hence whenever scan happens the data get saved in it. The most important factor is _time field. I found that the multiple entries has different time, hence incorrect count. But, Preset has more efficient data and only contains required fields.

[Piotr_Kowalczyk]

Thank you, Aspatil!