topic Re: help on a max or a latest function which doent works in Splunk Search

help on a max or a latest function which doent works

jip31 — Mon, 08 Jul 2019 11:41:43 GMT

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

Re: help on a max or a latest function which doent works

FrankVl — Wed, 30 Sep 2020 01:15:56 GMT

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.

Re: help on a max or a latest function which doent works

jip31 — Wed, 30 Sep 2020 01:15:32 GMT

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

Re: help on a max or a latest function which doent works

richgalloway — Wed, 30 Sep 2020 01:15:35 GMT

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.

Re: help on a max or a latest function which doent works

jip31 — Wed, 30 Sep 2020 01:15:37 GMT

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

Re: help on a max or a latest function which doent works

jkat54 — Mon, 08 Jul 2019 13:17:36 GMT

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

Re: help on a max or a latest function which doent works

FrankVl — Mon, 08 Jul 2019 13:25:41 GMT

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.

Re: help on a max or a latest function which doent works

jip31 — Mon, 08 Jul 2019 13:34:14 GMT

Sorry I dont understand

Re: help on a max or a latest function which doent works

FrankVl — Mon, 08 Jul 2019 13:55:27 GMT

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

Re: help on a max or a latest function which doent works

jip31 — Mon, 08 Jul 2019 15:16:31 GMT

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