Splunk Search

How to insert rows for zero counts and group by multiple fields of yet unknown values

weidertc
Communicator

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-...

https://community.splunk.com/t5/Splunk-Search/Any-way-to-return-zero-result-count-stats-of-a-field-s...

https://community.splunk.com/t5/Security/Search-events-against-a-lookup-table-and-show-matching-coun...

https://community.splunk.com/t5/Dashboards-Visualizations/Conditionally-Append-Rows-to-Stats-Table/m...

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?

 

Labels (1)
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

If I understand the problem correctly then combining api with errorMsg will do it.  See this query.

| 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 values(api) as api, values(errorMsg) as errorMsg
    | eval Minute=split("0,15,30,45", ",") 
    | mvexpand Minute]
``Combine api and errorMsg for sorting purposes```
| eval apiMsg = api.errorMsg
| stats sum(Traffic) as Traffic, sum(Failures) as Failures by apiMsg, Minute
| fillnull value=0 Failures 
```Drop the unneeded field```
| fields - apiMsg
---
If this reply helps you, an upvote would be appreciated.
0 Karma

weidertc
Communicator

Hi Rich, thanks for the help.  Unfortunately this doesn't preserve the api and errormsg as those fields are now gone.

I did, however, discover a quick solution.  i can add an eventstats at the end after the stats command.

 

| eventstats values(Traffic) as Traffic by api, Minute

 

This doesn't fill in any data where that api + minute combo doesn't yet exist though, so it isn't complete.  I think I need to redesign the whole query as this is getting a little complex.  All i'm trying to do is track the rates of failures for each type of failure for each api over 15 minute spans of time over the past hour so i can see if they are rising or falling.  The math does work though since if there's no data for that row that means there's no errors so the failure rate will be zero regardless of amount of traffic that's missing.

If there's a better way to track rates of failure over the past hour to see if they are rising, i'm all ears, but this may do for the time being.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!