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

[sylbaea]

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.

[sundareshr · Accepted solution]

Have you tried this?

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

[sylbaea]

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

[rjthibod]

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 *

[sylbaea]

@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

[rjthibod]

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