How to get time difference between the current event and another event of the same host

[power12]

Hello Splunkers ,

I am trying to find the up time of hosts by calculating the difference between the latest event for that host and last time it booted .

The following event describes that particular host has been booted.

2023-02-24T08:58:38.796336-08:00 hostabc kernel: [ 0.000000] Linux version 5.15.0-58-generic (buildd@lcy02-amd64-101) (gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, GNU ld (GNU Binutils for Ubuntu) 2.38) #64-Ubuntu SMP Thu Jan 5 11:43:13 UTC 2023 (Ubuntu 5.15.0-58.64-generic 5.15.74)

The following event is the latest event of that host
2023-02-24T14:04:51.115717-08:00 hostabc sssd_nss[248054]: Starting up 

Firstly I want to get the difference between 2023-02-24T14:04:51.115717-08:00 - 2023-02-24T08:58:38.796336-08:00 
Secondly If the difference is greater than 60 minutes create a new file called status and say it as down


Thanks in Advance 

[yuanliu]

You are correct that join is nonperformant and is best avoided.  I'd like to first illustrate a literal interpretation of your requirement, then a more straight-forward, much simpler solution based on the nature of your events.

Literal interpretation

Here we used eventstats to figure out when the server last booted.

| rex "kernel:\s*\[\s*0.0+\]\s*(?<booted>[^\(]+)\s*\(" ``` 0.000000 is boot time ```
| eventstats max(_time) as last_boot by booted host
| stats max(_time) as _time by last_boot host
| eval UP_Time = _time - last_boot
```| where _time - lastboot > 3600```

In the above, I extracted kernel version into booted as bonus information, assuming the kernel did not change during the search period.  If there is a chance that kernel version changes, you can narrow the regex match to only extract the string "Linux", for example.

I don't know why you want to convert UP_Time to string if it is intended for any purpose other than/in addition to display.  So, that function is skipped.

Linux syslog

Ultimately, however, the data you have seems to have come from Linux syslog, in which every event already carries UP_Time in that first bracket.  No need to perform any calculation at all.

| rex "^[^\[]+\s(?<process>\S+)[:\s+]*\[\s*(?<UP_Time>\d+)"
| stats max(UP_Time) as UP_Time by host
``` |  where UP_Time > 3600 ```

Hope this helps.

[PickleRick]

The most obvious approach would be to use transaction to track your events from the same host with a proper starts_with option so that it begins on each boot. But the transaction command is a "heavy" command performancewise and might not work well with your case. Especially if you have no time constraints.

You can probably use streamstats to "carry over" last boot time to the subsequent results.

[acharlieh]

So for the idea of correlating multiple events together, you can do this in a single pass without a join e.g. 

index=abc sourcetype=foo host=hostabc
| eval boot_time=case(searchmatch("Linux version"),_time)
| stats latest(_time) latest(boot_time) by host
| rename latest(*) -> *
| convert timeformat="%F %T" ctime(_time) as Latest_Event_Time ctime(btoot_time) as Boot_Time
| eval delta=_time-boot_time, UP_Time = tostring(delta,"duration")
| fields host Boot_Time Latest_Event_Time UP_Time

 
But the more practical problem you'll run into is the unbounded nature of how far in the past boot time can be... thus requiring this search to become almost an All Time search which doesn't scale well at all. 

If you can add data sources... instead of relying just on this log, you could have a scripted input that captures the output of `uptime` on a regular basis. 

But if not, another option may be to maintain a lookup containing the last boot time of a host, and pull that data in at search time instead... that way your search for looking at the latest events can be a much smaller window. Doing this off the top of my head, assuming a KVStore host_boots keyed by host, something like:

index=abc sourcetype=foo host=hostabc
| eval boot_time=case(searchmatch("Linux version"),_time)
| stats latest(_time) latest(boot_time) by host
| rename latest(*) -> *
| lookup host_boots host OUTPUT boot_time AS last_boot
| eval boot_time=coalesce(boot_time,last_boot)
| fields - last_boot
| outputlookup append=t key_field=host host_boots
| convert timeformat="%F %T" ctime(_time) as Latest_Event_Time ctime(btoot_time) as Boot_Time
| eval delta=_time-boot_time, UP_Time = tostring(delta,"duration")
| fields host Boot_Time Latest_Event_Time UP_Time

The question then becomes if you pull back this lookup for unseen hosts or not... and or if updating in this way makes sense (since the _time would get updated as frequently as the boot_time field...) and some other nuances...

[power12]

Hello @acharlieh  I ran your first search...it shows me the UP_Time but the boot_time output is blank or  it shows empty results

[acharlieh]

If there isn't a boot event (i.e. an event with the words "Linux version" in it) for a particular host in your time window, boot_time will come back as blank... 

This is the problem I was mentioning:

But the more practical problem you'll run into is the unbounded nature of how far in the past boot time can be... thus requiring this search to become almost an All Time search which doesn't scale well at all. 

---

[power12]

I tried the below search 

index=abc host=hostabc
| stats latest(_raw) as raw latest(_time) as time by host
| eval Latest_Event_Time=strftime(time,"%Y-%m-%d %H:%M:%S")
| join host
    [search index=abc host=hostabc "Linux version" 
| stats latest(_time) as btime by host
| eval Boot_Time=strftime(btime,"%Y-%m-%d %H:%M:%S")]
| eval UP_Time=tostring(time-btime,"duration")
| fields host Boot_Time Latest_Event_Time  UP_Time

.Is there any other more faster and easy way ?