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
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
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
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.
"[| 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.
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
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)
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
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??
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)
thanks for your help!
MAX dont work anymore but I m going to open a specific topic on this