Splunk Search

Timechart of two perf counters values' product

naydenk
Path Finder

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!

Tags (2)
0 Karma
1 Solution

bmacias84
Champion

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"))|

Then createing your new fields:

...| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null()) | eval QueriesTotal=if(counter=="Queries",value,null()) |

to finalize your results using table or fields command and then performing your computation:

...|fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100)|

Now put together:

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

If you receive different lines for each host and counter try selfjoin:

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

Why one search? Subsearch is basiclly running two searches consuming more resources. Also filting results in your base search will increase your search performance. if you want to keep your exsiting search join may work better.

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

View solution in original post

bmacias84
Champion

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"))|

Then createing your new fields:

...| eval QueryFailureTimeout=if(counter=="Query Failures - Timeout",value,null()) | eval QueriesTotal=if(counter=="Queries",value,null()) |

to finalize your results using table or fields command and then performing your computation:

...|fields _time, host, QueriesTotal, QueryFailureTimeout | eval FailureRatio=(QueryFailureTimeout/QueriesTotal*100)|

Now put together:

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

If you receive different lines for each host and counter try selfjoin:

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

Why one search? Subsearch is basiclly running two searches consuming more resources. Also filting results in your base search will increase your search performance. if you want to keep your exsiting search join may work better.

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

bmacias84
Champion

Also I would recommend reading Exploring Splunk: Search Procesing Language Primer and Cookbook. Very informative.

0 Karma

naydenk
Path Finder

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

0 Karma

bmacias84
Champion

After your base search use bucket " ...| bucket span=1m|..." Or span your collection interval.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

0 Karma

naydenk
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...