How to create report to highlight systems with a decreased measurement value compared to yesterday?

[uhaba]

Hi,

Looking for help on how to detect systems where a monitored value has decreased compared to yesterday's average value, and if so, by how much. We have a fleet of several thousand systems which are monitored, and we'd like to locate any systems which have a value drop since yesterday. Note: Due to some lag in delivery of the values due to long haul comms, we also include the origin timestamp when sending the event to splunk to get the correct order of events instead of using index time. (for now...)

The base search looks like this to return events containing the relevant values across the fleet:
index=foo sourcetype=bar system_name="*_volume"
| eval tstamp=strptime(local_time, "%Y-%m-%dT%H:%M:%S.%Q")
| eval _time = tstamp

A typical event resulting from the search above from last hour looks like this (with some unused fields removed):
{
   local_time: 2023-05-10T15:05:07.617Z
   system_name: location/sublocation/widget 01/widget_volume
   value: 201.58281
}

A table of the above across one sublocation would look like this.

_time	system_name	value
2023-05-10 15:05:07.617	location 01/sublocation 01/widget 01/widget_volume	16.18125
2023-05-10 13:48:02.010	location 01/sublocation 01/widget 02/widget_volume	53.16573
2023-05-10 13:41:01.497	location 01/sublocation 01/widget 03/widget_volume	108.99990
2023-05-10 11:48:53.687	location 01/sublocation 01/widget 04/widget_volume	200.73786

So, my challenge is we have thousands of "system_name"s feeding into splunk and we'd really like to spot the handful that are decreasing compared to yesterday. If we identify any that have decreased since yesterday, as a starting reference point in time to compare with, we'd like to get a table format report run daily with the list of system_names, todays_avg_value, yesterdays_avg_value, value_delta (which should be a negative number and not percent). Eventually we'd like to run this as an hourly comparison as this value is so important to catch a decrease in a timely manner from an alarming perspective.

Thank you!

[VatsalJagani]

You can try query something like this:

index=foo sourcetype=bar system_name="*_volume" 
| eval tstamp=strptime(local_time, "%Y-%m-%dT%H:%M:%S.%Q") 
| eval _time = tstamp 
| bucket span=1d _time 
| stats avg(value) as avg_value by system_name, _time 
| streamstats window=2 current=f global=f avg(avg_value) as yesterday_avg by system_name 
| where avg_value < yesterday_avg 
| eval value_delta = (avg_value - yesterday_avg)
| table system_name, avg_value, yesterday_avg, value_delta

Kindly upvote if you find it useful!!!

[TrangCIC81]

Try this and let me know if it works.

index=foo sourcetype=bar system_name="*_volume"
| eval tstamp=strptime(local_time, "%Y-%m-%dT%H:%M:%S.%Q")
| eval _time = tstamp
| bin _time span=1d
| stats avg(value) as avg_today by system_name, _time
| eval yesterday=strftime(_time-86400, "%Y-%m-%d")
| join system_name [search index=foo sourcetype=bar system_name="*_volume"
| eval tstamp=strptime(local_time, "%Y-%m-%dT%H:%M:%S.%Q")
| eval _time = tstamp
| bin _time span=1d
| where strftime(_time, "%Y-%m-%d")=yesterday
| stats avg(value) as avg_yesterday by system_name]
| where avg_today < avg_yesterday
| eval value_delta = avg_today - avg_yesterday
| table system_name, avg_today, avg_yesterday, value_delta