help on a max or a latest function which doent works

[jip31]

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

[FrankVl · Accepted solution]

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.

[jip31]

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

[FrankVl]

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.

[jip31]

Sorry I dont understand

[FrankVl]

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

[jip31]

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

[richgalloway]

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.

[jkat54]

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

[jip31]

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