Stats 2 results together and filling in the blank fields with dynamically-generated values

[weidertc]

I need to create volume-base alerts so we know when volume drops. The services we need to monitor are usually suffixed with its version (e.g. placeOrder is actually placeOrder_v1, placeOrder_v2, etc) which often ramp up and down while servicing their respective percentage of the total traffic, causing our volume based alerts to fire even though the total calls for the overall feature are the same. I chopped off the suffix and created a calculated "group" field and want to use that, but this leaves a problem when the service isn't firing at all. the volume becomes 0, but so does the "group", or total, volume, for that row.

here is the half way point of my larger query so you can see the structure that is built as i append 2 results: the current, and the 6 week average, in run-anywhere query.

| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v1" | eval groupVolume="375" | eval volume="175"
| append
[| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v3" | eval groupVolume="375" | eval volume="200"]
| append
[| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v1" | eval pastGroupVolume="325" | eval pastVolume="200"]
| append
[| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v2" | eval pastGroupVolume="325" | eval pastVolume="100"]
| append
[| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v3" | eval pastGroupVolume="325" | eval pastVolume="25"]
| fields _time, group, service, groupVolume, volume, pastVolume, pastGroupVolume

this gives the following table

group          service         GroupVolume   Volume     PastGroupVolume   PastVolume
placeOrder     placeOrder_v1   375           175
placeOrder     placeOrder_v3   375           200
placeOrder     placeOrder_v1                            325               200
placeOrder     placeOrder_v2                            325               100
placeOrder     placeOrder_v3                            325               25

When I zip them with this command added added to it

| stats values(groupVolume) as groupVolume, values(volume) as volume, values(pastGroupVolume) as pastGroupVolume, values(pastVolume) as pastVolume by group, service

I get this

group                  service    GroupVolume    Volume   PastGroupVolume   PastVolume
placeOrder        placeOrder_v1   375            175      325              200
placeOrder        placeOrder_v2                           325              100
placeOrder        placeOrder_v3   375            200      325              25

_v2 is no longer being called, while _v1 is ramping down, and _v3 is ramping up.

An alert will fire for _v2 because the current group volume is 0 as i compare it to the historical avg. it needs to acquire 375 because it's the same group. How can i carry over this value into the row for _v2?

I need it to show this

group                  service    GroupVolume    Volume   PastGroupVolume   PastVolume
placeOrder        placeOrder_v1   375            175      325              200
placeOrder        placeOrder_v2   375                     325              100
placeOrder        placeOrder_v3   375            200      325              25

[KailA · Accepted solution]

Hi,

First thanks for the splunk query, it's way simpler to help you like that.
Can you try eventstats like that

| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v1" | eval groupVolume="375" | eval volume="175"
 | append
 [| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v3" | eval groupVolume="375" | eval volume="200"]
 | append
 [| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v1" | eval pastGroupVolume="325" | eval pastVolume="200"]
 | append
 [| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v2" | eval pastGroupVolume="325" | eval pastVolume="100"]
 | append
 [| makeresults 1 | eval group="placeOrder" | eval service="placeOrder_v3" | eval pastGroupVolume="325" | eval pastVolume="25"]
 | fields _time, group, service, groupVolume, volume, pastVolume, pastGroupVolume
 | stats values(groupVolume) as groupVolume, values(volume) as volume, values(pastGroupVolume) as pastGroupVolume, values(pastVolume) as pastVolume by group, service
| eventstats values(groupVolume) as groupVolume by group

It will give you the values by group fore the groupVolume and it seems to solve your problem, or maybe I didn't get what was the problem ^^'

[weidertc]

This does the trick. Thank you!

[somesoni2]

Try this

your search that generates data
 | stats values(groupVolume) as groupVolume, values(volume) as volume, values(pastGroupVolume) as pastGroupVolume, values(pastVolume) as pastVolume by group, service
| eventstats values(groupVolume) as groupVolume by group

[weidertc]

I was looking at something like this but couldn't figure out how to use eventstats properly. thanks! this works. I kept putting it before instead of after all this.