help on eval command for calculating the difference between now() and a date field

[jip31]

hi
I use the search below
"LAST_SEEN" is a field with a date format like "2019-06-07 09:12:40.0"
I need to add an eval command in the search in order to be calculate the events which are older than a month
So I need to do something like eval delta =(now() - LAST_SEEN) because after I want to add a where command in order to be able to display the events < one month

index=x sourcetype=wireless
    [| inputlookup host.csv 
    | table host 
    | rename host as USERNAME ] | stats latest(LAST_SEEN) by USERNAME

Could you help me please

[whrg · Accepted solution]

Use strptime() to convert LAST_SEEN into a UNIX timestamp.

Check this out. This will get all events which are less than 30 days old:

| makeresults
| eval LAST_SEEN="2019-06-07 09:12:40.0"
| eval LAST_SEEN_EPOCH=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N")
| eval diff_seconds=now()-LAST_SEEN_EPOCH
| where diff_seconds<60*60*24*30

[jip31]

hi
I done this
index=x sourcetype=wireless_client_val
| eval LAST_SEEN_EPOCH=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N")
| eval diff_seconds=now()-LAST_SEEN_EPOCH
| where diff_seconds<(60*60*24*30)
[| inputlookup host.csv
| table host
| rename host as USERNAME ]
| lookup lookup_cmdb_fo_all.csv HOSTNAME as USERNAME output SITE
| search SITE="*"
| stats values(SITE) as SITE, latest(LAST_SEEN_EPOCH) by USERNAME

But I have 2 issues :
1) Error in 'where' command: The operator at is invalid
2) Error in 'eval' command: The 'last_seen_epoch' function is unsupported or undefined.

[whrg]

"[| inputlookup host.csv ..." is a subsearch. It requires the "search" command. Try it like this:

...
| where diff_seconds<(60*60*24*30)
| search [inputlookup host.csv |table host | rename host as USERNAME ]
| lookup ...

I removed the pipe symbol | at the beginning of the subsearch. It is not necessary.

In your original post, the subsearch belonged to the initial search. (When you write index=x it translates to | search index=x.) That is why there was no error before.

[jip31]

does it seems correct now?
LAST_SEEN_EPOCH result has to be a number of days
actually its 1562572180.000000 format

index=X sourcetype=wireless_client_val 
| eval LAST_SEEN_EPOCH=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| eval diff_seconds=now()-LAST_SEEN_EPOCH 
| where diff_seconds<(60*60*24*30) 
| search 
    [ inputlookup host.csv 
    | table host 
    | rename host as USERNAME ] 
| lookup lookup_cmdb_fo_all.csv HOSTNAME as USERNAME output SITE 
| search SITE="*" 
| stats values(SITE) as SITE, latest(LAST_SEEN_EPOCH) by USERNAME

[whrg]

Looks fine to me.
If you want the number of days, then try something like this:

| eval age_in_days=floor((now()-LAST_SEEN_EPOCH)/60/60/24)

[jip31]

So I have done :

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

[jip31]

But I have questions
1) Could you please explain me /60/60/24?
2) Is it possible to have the number of days with a decimal?
2) Are you sure that the formatting works fine because when I execute the request I have a last seen value of 13 days and when I look the events directly I see that the last seen is 3 days
Normally latest(LAST_SEEN_EPOCH) by USERNAME catch well the last events? So why I have 13 days instead 3 days??

[whrg]

1) now() and LAST_SEEN_EPOCH are unix time stamp, which is measured in seconds since Jan 01 1970. So now()-LAST_SEEN_DAYS gives you the time difference in seconds. Now /60 gives you the difference in minutes; /60/60 is the difference in hours; and /60/60/24 is the difference in days.
2) Use round((now()-LAST_SEEN_EPOCH)/60/60/24, 2)
3) Try max(LAST_SEEN_DAYS) instead of latest(LAST_SEEN_DAYS)

[jip31]

thanks for your help!
MAX dont work anymore but I m going to open a specific topic on this