Splunk Search

help on a max or a latest function which doent works

jip31
Builder

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.

View solution in original post

0 Karma

jip31
Builder

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
Builder

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
Builder

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, an upvote 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
Builder

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!