I am writing a query to look for rises in error messages over the past hour. It looks in 15 minute chunks from 0 to 60 minutes ago. Rows where there are 0 error messages are missing from the table,...
See more...
I am writing a query to look for rises in error messages over the past hour. It looks in 15 minute chunks from 0 to 60 minutes ago. Rows where there are 0 error messages are missing from the table, but i need to keep them there so when i run a median over the last 3 time bins, it includes the 0s. Each API has their own error messages when it fails, and not every failure occurs in every 15 minute block of time for their API. So far I have this, in run-anywhere spl, but it's not correct | makeresults 1 | eval api="api1", errorMsg="msg1", Minute=0, Traffic="1234", Failures="5"
| append [| makeresults 1 | eval api="api1", errorMsg="msg2", Minute=15, Traffic="1786", Failures="2"]
| append [| makeresults 1 | eval api="api1", errorMsg="msg2", Minute=30, Traffic="1842", Failures="1"]
| append [| makeresults 1 | eval api="api1", errorMsg="msg2", Minute=45, Traffic="1619", Failures="7"]
| append [| makeresults 1 | eval api="api1", errorMsg="msg3", Minute=0, Traffic="1234", Failures="15"]
| append [| makeresults 1 | eval api="api1", errorMsg="msg3", Minute=45, Traffic="1619", Failures="12"]
| append [| makeresults 1 | eval api="api2", errorMsg="msg10", Minute=15, Traffic="7856", Failures="110"]
| fields api, errorMsg, Minute, Traffic, Failures
| appendpipe
[| stats count by api, errorMsg
| eval Minute=split("0,15,30,45", ",")
| mvexpand apiErrorMsg
| mvexpand Minute]
| stats sum(Traffic) as Traffic, sum(Failures) as Failures by api, errorMsg, Minute
| fillnull value=0 Failures Table is too large to show, but it doesn't carry with it the total traffic values for each 15 minute bin. I looked at the following solutions, but they are each different enough that their solutions only partially worked as I have 2 group by fields and the APIs and Error Messages are not yet known until the query runs. https://community.splunk.com/t5/Dashboards-Visualizations/how-to-insert-row-on-zero-count-and-still-use-group-by-multiple/m-p/144172#M8722 https://community.splunk.com/t5/Splunk-Search/Any-way-to-return-zero-result-count-stats-of-a-field-such-as-the/td-p/92149 https://community.splunk.com/t5/Security/Search-events-against-a-lookup-table-and-show-matching-count/td-p/47408 https://community.splunk.com/t5/Dashboards-Visualizations/Conditionally-Append-Rows-to-Stats-Table/m-p/121235#M7040 The only problems is the Total Traffic field (total of all calls regardless whether it erred) is missing from many rows. What can I do after the makeresults that will fill this table out correctly?