Hello,
I have a scheduled search that populates a CSV with data each day, including the current date. Here is an example:
device battery date
desktop 100 05/04/19
laptop 100 05/04/19
printer 100 05/04/19
desktop 100 05/05/19
laptop 100 05/05/19
printer 100 05/05/19
desktop 100 05/06/19
laptop 90 05/06/19
printer 90 05/06/19
What query would I need to put into an alert that would notify me when a device that had 100
in its battery
field the previous day is below 100 on the current day? The result should return just the device
names, which here would be laptop
and printer
.
Note: I do know this could be helpful in the query where it shows data for the current day and the previous day. Feel free to use it if you'd like: | where (date == strftime(relative_time(now(), "-1d@d"), "%m/%d/%y")) OR (date == strftime(now(), "%m/%d/%y") )
Like this:
| makeresults
| eval raw="device=desktop,battery=100,date=05/04/19 device=laptop,battery=100,date=05/04/19 device=printer,battery=100,date=05/04/19 device=desktop,battery=80,date=05/05/19 device=laptop,battery=100,date=05/05/19 device=printer,battery=100,date=05/05/19 device=desktop,battery=100,date=05/06/19 device=laptop,battery=90,date=05/06/19 device=printer,battery=90,date=05/06/19"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| fields - _*
| rename COMMENT AS "Everything above generates sample data; everything below is your solution"
| eval _time = strptime(date, "%m/%d/%y")
| fields - date
| sort 0 - _time
| dedup 2 device
| reverse
| streamstats current=f last(battery) AS prev_battery BY device
| eval change = battery - prev_battery
| where isnotnull(change) AND battery<100 AND prev_battery=100
Give this a try
Thanks @niketnilay for correcting. Updating original response with different check in eval.
| inputlookup yourlookup.csv
| where (date == strftime(relative_time(now(), "-1d@d"), "%m/%d/%y")) OR (date == strftime(now(), "%m/%d/%y") )
| eval Period=if(date == strftime(date == strftime(now(), "%m/%d/%y"),"Today","Yesterday")
| chart max(battery) over device by Period
| where Yesterday=100 AND Today<100
Set your alert condition to raise an alert when number of events from above search is greater than 0.
@somesoni2 I think you missed, "%m/%d/%y"
for strftime()
eval function while determining Period
| inputlookup yourlookup.csv
| where (date == strftime(relative_time(now(), "-1d@d"), "%m/%d/%y")) OR (date == strftime(now(), "%m/%d/%y") )
| eval Period=if(date == strftime(relative_time(now(), "-1d@d"),"%m/%d/%y"),"Yesterday","Today")
| chart max(battery) over device by Period
| where Yesterday=100 AND Today<100
I get this error: Error in 'eval' command: The arguments to the 'strftime' function are invalid.
I'm unsure why though.