How to see changes in field values when comparing today vs. yesterday?

[russell120]

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") )

[woodcock]

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

[somesoni2]

Give this a try

Updated##

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.

[niketn]

@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

[russell120]

I get this error: Error in 'eval' command: The arguments to the 'strftime' function are invalid. I'm unsure why though.