topic Re: How to see changes in field values when comparing today vs. yesterday? in Splunk Search

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

russell120 — Mon, 06 May 2019 15:20:58 GMT

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

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

somesoni2 — Mon, 06 May 2019 16:14:47 GMT

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.

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

russell120 — Mon, 06 May 2019 16:44:33 GMT

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

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

niketn — Mon, 06 May 2019 17:24:20 GMT

@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

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

woodcock — Sat, 11 May 2019 21:29:54 GMT

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