How to write a search to include 2 metrics on the same chart panel and alert when these metrics deviate by greater than 10%?

[lyndac]

My data looks like this (field names are: inputTime, metricName, value, key)

2015-07-09 08:01:03  num_bytes_sent  4345654 host1
2015-07-09 08:01:03 num_bytes_received 4345654 host1
2015-07-09 08:02:03  num_bytes_sent  4323654 host2
2015-07-09 08:02:03 num_bytes_received 4323654 host2
2015-07-09 08:02:03  num_bytes_sent  5325152 host1
2015-07-09 08:02:03 num_bytes_received 5327152 host1
2015-07-09 08:03:03  num_bytes_sent  124585 host2
2015-07-09 08:03:03 num_bytes_received 124589 host2

Currently, I have a dashboard that includes 1 panel displaying the total bytes sent by key per day, and 1 panel that displays the total bytes received by key per day. I use these searches to populate the panels:

index=foo metricName=num_bytes_sent | timechart span=1d sum(value) by key
index=foo metricName=num_bytes_received  | timechart span=1d sum(value) by key

My goals are 1) to display both of these metrics on the same chart and then 2) to alert when the send & receive totals for any key deviate by greater than 10%.

I've changed up my dashboard to have the user select a key, but I can't seem to get the search right to display both num_bytes_sent and num_bytes_received for the selected key on the same panel. Please help.

[woodcock · Accepted solution]

This should work:

index=foo | eval num_bytes_sent=if(metricName=num_bytes_sent,value,0) | eval num_bytes_received=if(metricName=num_bytes_received,value,0) | stats sum(num_bytes_sent) sum(num_bytes_received) BY key

This will also work and may be more clear:

index=foo | rex "(?:num_bytes_sent\s+(?<num_bytes_sent>\d+))|(?:num_bytes_received \s+(?<num_bytes_received>\d+)) | stats sum(num_bytes_sent) sum(num_bytes_received) BY key

[lyndac]

That worked to display the graph -- thanks! It looks great! Now, with our data, the num_bytes_received and num_bytes_sent should be the same, unless there is an issue with one of the hosts. How do I now send out an alert when the sent and received differ by say 10%? IDo I use the stdev function somehow? I know how to use the UI to make the alert, I don't understand how to set the thresshold.

[woodcock]

Well that is a different question so usually that should be a new question in the forum, too. In any case, you can do it like this:

index=foo | rex "(?:num_bytes_sent\s+(?<num_bytes_sent>\d+))|(?:num_bytes_received \s+(?<num_bytes_received>\d+)) | stats sum(num_bytes_sent) AS TotalBytesSent sum(num_bytes_received) AS TotalBytesReceived BY key | eval diff=TotalBytesSent-TotalBytesReceived | eval pctDiff=100*(TotalBytesReceived/TotalBytesSent) | where pctDiff ?=10

Then save this as an Alert that triggers when # of events returned > 0.