Hello
I need some help with the following scenario:
I am collecting two perf counters, CounterA and CounterB, from multiple hosts. I want to be able to timechart the following:
Ratio = CounterA / CounterB * 100
per host. I tried this:
index=perfmon object="Core DataAccess" instance="_total" counter="Query Failures - Timeout" | eval QueryFailureTimeout=Value | appendpipe [search index=perfmon object="Core DataAccess" counter="Queries" | eval QueriesTotal=Value] | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | fields _time, host, FailureRatio | timechart max(FailureRatio) by host
but it does not seem to work - it looks like the FalureRatio is not getting calculated...
Thank you in advance!
You could rework you search into a single search using the OR AND operators in the base search. I made some assumption on your data.
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))|
...| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null()) | eval QueriesTotal=if(counter=="Queries",value,null()) |
...|fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100)|
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null() | eval QueriesTotal=if(counter=="Queries",value,null() | fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null() | eval QueriesTotal=if(counter=="Queries",value,null() | fields _time, host, QueriesTotal, QueryFailureTimeout | selfjoin host| eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
index=perfmon object="Core DataAccess" instance="_total" counter="Query Failures - Timeout" | eval QueryFailureTimeout=Value|fields _time, host,QueryFailureTimeout | join host [search index=perfmon object="Core DataAccess" counter="Queries" | eval QueriesTotal=Value|fields _time, host,QueriesTotal] | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
Hope this help or gives you ideas. If this does help dont forget to use accept or thumbs up answers if they help. Cheers
You could rework you search into a single search using the OR AND operators in the base search. I made some assumption on your data.
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))|
...| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null()) | eval QueriesTotal=if(counter=="Queries",value,null()) |
...|fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100)|
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null() | eval QueriesTotal=if(counter=="Queries",value,null() | fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
index=perfmon object="Core DataAccess" AND ((instance="_total" AND counter="Query Failures - Timeout") OR (counter="Queries"))| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null() | eval QueriesTotal=if(counter=="Queries",value,null() | fields _time, host, QueriesTotal, QueryFailureTimeout | selfjoin host| eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
index=perfmon object="Core DataAccess" instance="_total" counter="Query Failures - Timeout" | eval QueryFailureTimeout=Value|fields _time, host,QueryFailureTimeout | join host [search index=perfmon object="Core DataAccess" counter="Queries" | eval QueriesTotal=Value|fields _time, host,QueriesTotal] | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | timechart max(FailureRatio) by host
Hope this help or gives you ideas. If this does help dont forget to use accept or thumbs up answers if they help. Cheers
Also I would recommend reading Exploring Splunk: Search Procesing Language Primer and Cookbook. Very informative.
That was perfect! The self join was definitely necessary. And thank you for breaking it down - it helps understand how to build it myself next time (taught me how to fish... 🙂 )
After your base search use bucket " ...| bucket span=1m|..." Or span your collection interval.
I think what you want is the "append" command. Appendpipe performs operations on the previous search.
index=perfmon object="Core DataAccess" instance="_total" counter="Query Failures - Timeout" | eval QueryFailureTimeout=Value | append [search index=perfmon object="Core DataAccess" counter="Queries" | eval QueriesTotal=Value] | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100) | fields _time, host, FailureRatio | timechart max(FailureRatio) by host
http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Appendpipe
http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Append
I had tried that too, without success.
I just tried removing the last piped command (| timechart max(FailureRatio) by host) and changing the fields command to "fields _time, host, QueriesTotal, QueryFailureTimeout, FailureRatio" - it looks like the calculation may not be working because (I am theorizing) it is trying to do the calculation between values logged at the same exact _time. Since the counters will very rarely have the same timestamp, the calculation fails. If this theory is correct, how do I get it to do the division using numbers from the same minute?