Splunk Search

help on a max or a latest function which doent works

jip31
Motivator

hello

The max function in this search doesnt works. Idem with latest!
Its not the latest or max event taked into account but the min or the oldest!
what is the problem please??

index=x sourcetype=wireless_client_val 
| eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| eval diff_seconds=now()-LAST_SEEN
| where diff_seconds>(60*60*24*5) 
| search 
    [ inputlookup host.csv 
    | table host 
    | rename host as USERNAME] 
| lookup lookup_cmdb_fo_all.csv HOSTNAME as USERNAME output SITE 
| search SITE="*" 
| eval LAST_SEEN_DAYS=round((now()-LAST_SEEN)/60/60/24,1) 
| stats values(SITE) as SITE, max(LAST_SEEN_DAYS) as Days_of_last_seen by USERNAME 
| sort -Days_of_last_seen
Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

Can you perhaps share some data going into the stats command (a table with a number of rows with at least these fields: _time, LAST_SEEN_DAYS,username) and data coming out and how that is different from what you expect?

I cannot imagine max(LAST_SEEN_DAYS) returning the events with min(LAST_SEEN_DAYS) instead. Same for latest(), unless you have been messing with the _time field (which doesn't seem to be the case from the search you are sharing), that should also behave as expected.

View solution in original post

0 Karma

FrankVl
Ultra Champion

Can you perhaps share some data going into the stats command (a table with a number of rows with at least these fields: _time, LAST_SEEN_DAYS,username) and data coming out and how that is different from what you expect?

I cannot imagine max(LAST_SEEN_DAYS) returning the events with min(LAST_SEEN_DAYS) instead. Same for latest(), unless you have been messing with the _time field (which doesn't seem to be the case from the search you are sharing), that should also behave as expected.

0 Karma

jip31
Motivator

Hi
What I want to say is that the events returned by my stats command correspond to the difference between now() and the oldest LAST_SEEN events instead the latest
Example:
For example, for the first event returned by the search, the latest _time field value is 08/0719 13:29 and the oldest _time field value is 01/07/19 17:59
So normally,LAST_SEEN_DAYS for this USERNAME hast to be now() - 08/0719 13:29 so 0,1 days
But instead this I have 7 days so it means now() - 01/07/19 17:59
Very strange

0 Karma

FrankVl
Ultra Champion

I don't completely follow, but it sounds like you may want to do a | stats values(SITE) as SITE latest(LAST_SEEN) as LAST_SEEN by USERNAME before doing any of the calculations instead of doing that stats at the end.

0 Karma

jip31
Motivator

Sorry I dont understand

0 Karma

FrankVl
Ultra Champion

Something like this (I also moved the subsearch into the main search instead of a separate search command):

index=x sourcetype=wireless_client_val [ inputlookup host.csv | table host | rename host as USERNAME] 
 | lookup lookup_cmdb_fo_all.csv HOSTNAME as USERNAME output SITE 
 | search SITE="*" 
 | eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
 | stats values(SITE) as SITE, latest(LAST_SEEN) as LAST_SEEN by USERNAME 
 | eval diff_seconds=now()-LAST_SEEN
 | where diff_seconds>(60*60*24*5) 
 | eval LAST_SEEN_DAYS=round((now()-LAST_SEEN)/60/60/24,1) 
 | sort -Days_of_last_seen
0 Karma

jip31
Motivator

Oh now it seems to be correct!
I just dont clearly well why we have to put the eval after the stats

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If you replace max(LAST_SEEN_DAYS) with values(LAST_SEEN_DAYS) you'll see all the LAST_SEEN_DAYS values for each host. You may see something like 1,7. The MAX of those values is 7, which is what you are getting.

---
If this reply helps you, Karma would be appreciated.
0 Karma

jkat54
SplunkTrust
SplunkTrust

You would want latest too because max over a large time period may not be what you're expecting

0 Karma

jip31
Motivator

yes but like I said previously if my last or my max events _time is 08/0719 13:29 , I need to have 0,1 days displayed instead 7 days
my issue is on the LAST_SEEN_DAYS calculation
and when I use max or latest, normally it the last event that is taken into account
and for me no

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...