How to find out the first occurrence of an event with a search?

[jmcaloon]

Currently I am trying to figure out a way to pull the first time an event occurred. Specifically when one of our programs check in for the first time with the latest update.

Currently I can pull the most recent event, but it would be better for troubleshooting to pull the first event if an issue occurred due to a new version.

Here is the current code I have:

ComputerName= * event_platform=Win| spath event_simpleName | search event_simpleName=SensorHeartbeat| spath ConfigBuild | search ConfigBuild!="(Whatever Verison its on)"|dedup ComputerName

What I would like it to do is to pull the first time the computer checked in with a version of config build. I tried using the stats command, but had no luck. Any suggestions?

Thank you,
Jack

[richgalloway]

Did you try ComputerName= * event_platform=Win| spath event_simpleName | search event_simpleName=SensorHeartbeat| spath ConfigBuild | search ConfigBuild!="(Whatever Version its on)"|dedup ComputerName | stats earliest(Version) by ComputerName ?

[DalJeanis · Accepted solution]

The spath command is going to be extracting data from a json or html field called ConfigBuild.

Try this and inspect the event returned in order to see what the name of the version field is.

ComputerName= * event_platform=Win index=myindex
| spath event_simpleName 
| search event_simpleName=SensorHeartbeat
| spath ConfigBuild 
| head 1

Let's assume the name is something like "myversion"

ComputerName= * event_platform=Win index=myindex
| spath event_simpleName 
| search event_simpleName=SensorHeartbeat
| spath ConfigBuild 
| stats earliest(_time) as FirstBuild latest(_time) as LastBuild by ComputerName myversion
| eval FirstBuild=strftime(FirstBuild,"%Y-%m-%d %H:%M:%S")
| eval LastBuild=strftime(LastBuild,"%Y-%m-%d %H:%M:%S")
| sort 0 ComputerName -LastBuild

That should give you a table of the first and last times that each particular build was present on each particular computer. Personally, I would not limit the search to the most recent version, since it might be relevant what version it was upgraded from, as well.

And, one more little filigree here. If you want only one computer, obviously you'd put it in place of the the * above. But if you want a small list, then you can do it this way -

ComputerName= * event_platform=Win index=myindex
 [|makeresults | eval ComputerName="name1 name2 name3 name4" | makemv ComputerName | mvexpand ComputerName | table ComputerName]
 | spath event_simpleName 
 | search event_simpleName=SensorHeartbeat
 | spath ConfigBuild 
 | stats earliest(_time) as FirstBuild latest(_time) as LastBuild by ComputerName myversion
 | eval FirstBuild=strftime(FirstBuild,"%Y-%m-%d %H:%M:%S")
 | eval LastBuild=strftime(LastBuild,"%Y-%m-%d %H:%M:%S")
 | sort 0 ComputerName -LastBuild

If you wanted a large list, then you'd probably use a join to a loadcsv.

[jmcaloon]

These suggestions got me to exactly what I needed. Thank you !

[DalJeanis]

You are quite welcome!