topic Re: How to edit my search to get the last software inventory for each computer? in Splunk Search

How to edit my search to get the last software inventory for each computer?

sylbaea — Tue, 11 Oct 2016 13:23:55 GMT

Hello,

I am collecting into Splunk computer software inventory periodically sent by all my computers.

Each inventoried software is generating a Splunk event similar to:
host = COMPUTERNAME
inventoryDate = (timestamp)
SoftwarePublisher
SoftwareName
SoftwareVersion
(...)

Each computer sent its inventory every two days.

From there, I want to build the following searches into Splunk
1. Get latest inventory for a given computer at a specific point in time
2. Get latest inventory from all computers at a specific point in time

For #1, I successfully used the following search filtering time period as required:

(...) host=<COMPUTERNAME>| eventstats max(InventoryDate) as LastInventoryDate | where InventoryDate=LastInventoryDate

It works as expected, but is it the most optimized way to proceed?

For #2, I cannot find how to get the equivalent, basically removing the filter on host and getting all latest inventory.
Any suggestion ?

Thanks.

Re: How to edit my search to get the last software inventory for each computer?

rjthibod — Tue, 11 Oct 2016 13:43:34 GMT

Assuming your timestamping is correctly parsed and your events are in correct time order, the following should work for your first request

... host=<COMPUTERNAME> | head 1

For #2, I am not sure if this is the most efficient but I think it works

... | stats latest(_time) as _time latest(_raw) as _raw by host | extract | table _time host *

Re: How to edit my search to get the last software inventory for each computer?

sylbaea — Tue, 11 Oct 2016 13:52:42 GMT

@rjthibod, thanks for your suggestion but it does not work as it only returns one event by host.

Basically, a software inventory for a given computer is made of multiple "software" event (let's say ~200-500 different software per computer).
For a given inventory, I guaranty that InventoryDate is the same for software "events" part of same inventory upload. _time is also equals to InventoryDate

So for #2, I am looking for a search that:
For each computer/host:
1. Get latest inventory date
2. Return all software events having _time (or Inventory date) equals to latest inventory date

Re: How to edit my search to get the last software inventory for each computer?

sundareshr — Tue, 11 Oct 2016 14:17:12 GMT

Have you tried this?

(...) | eventstats max(InventoryDate) as LastInventoryDate by host | where InventoryDate=LastInventoryDate | table host InventoryDate SoftwarePublisher SoftwareName SoftwareVersion

Re: How to edit my search to get the last software inventory for each computer?

sylbaea — Tue, 11 Oct 2016 14:34:33 GMT

Thanks a lot. I was not far, I was just missing the "by host" 🙂

Re: How to edit my search to get the last software inventory for each computer?

rjthibod — Tue, 11 Oct 2016 14:39:22 GMT

Sorry for the confusion on my part. Glad you got what you needed in the other answer.