Hi,I had a situation to your issue and found a way to resolve what I needed.
I wanted to flag the amount of errors that occured in the past week within certain categories that exceeded 100. I wanted to compare this weeks number to last weeks number. The number is chosen based off of a time range picker used by the product owner at the top.
index="your_search" |eval early=$timerange.latest$-604800| eval late=$timerange.latest$ |where _time > early AND _time< late| | stats SUM(SQLERR_CNT) as Total by PGM_NM SSID _time | where Total > 100 |stats count AS 1Week | appendcols [search index="your_search" |eval early=$timerange.latest$-604800*2| eval late=$timerange.latest$ |where _time > early AND _time< late| | stats SUM(SQLERR_CNT) as Total by PGM_NM SSID _time | where Total > 100 |stats count AS 2Week]
by creating an early time used the time range token and subtracting the epoch time of the bucket. 604800 is the epoch time for 1 week. Then I appended a second search where I made the earliest time two weeks ago. and the latest time one week ago.
As far as putting this trend into a single value visualization. I have found the difference between the two numbers. and then displayed that number as a single value. Green if errors went down, red if they went up drastically! I am still working on how to best visually display my info.
... View more
I am measuring stored procedure hits by system codes. I am trying to implement 5 panels in one row that show graphs and single values for each top 5 system code in the data based on the number of Hits (sum of the SQL count). I would like to populate the drop-down values dynamically based on which subsystem is picked and what time range is chosen.
For each of the 5 drop-down values to be selected from first to fifth in order, my first solution was to find the top system code and append the rest of the system codes. Then in the next panel find the second top system code and append the rest, and so on for all 5 panels. This caused my search to find the results twice as it ran through the results to pick out one system code, then another time to append the rest of the system codes after.
Is there a way I can organize the data to have the X row become the first row in the table?
Or in the drop-down code, am I able to reference the second row? Ex. $row.SysCode$ will select the value in field SysCode on the first row, however, I need this to be done for the second row.
| savedsearch which uses subsystem and timerange filters and returns SysCodes... | stats sum(SQL_CNT) as Hits by SysCode | sort -num(Hits) | head $head_num$ | tail 1 | append [| savedsearch which uses subsystem and timerange filters and returns SysCodes... | stats sum(SQL_CNT) as Hits by SysCode | sort -num(Hits) ] | dedup SysCode
... View more