Hi All,
Any help is greatly appreciated as I am of course in a bit of a time crunch.
We are currently using splunk to ingest our logs from haproxies running in our environment. The haproxies front a number of services we offer using API calls.
I am trying to generate a report that breaks down the average response time (RTT in the haproxy log) broken out by each API call.
I found I can do this using this search:
sourcetype=haproxy:http status=200 "API1?" | stats avg(rtt) as API1 |
appendcols [search "API2?" | stats avg(rtt) as API2] |
appendcols [search "API3?" | stats avg(rtt) as API3] |
appendcols [search "API4?" | stats avg(rtt) as API4] |
transpose
I then get the table that I need with the first column being the APIs and the second column being the average response time for each request to that API call.
The problem is that I have about 40 API calls that I need to generate in this report. When I get to 20 subsearches, then I get an error of "Too many subsearches".
Does anyone know if there is a way to workaround this?
Thank you very much.
Tony
@niketnilay - Thank you so much! That is very helpful! It worked perfectly and I was able to add the counts as well to the output which will help with us prioritizing where we should focus some optimization efforts.
Thanks!
Tony
@aalvino73 I am glad the solution worked. Do accept/up vote the answer 🙂
Do read the Splunk Documentation for Event Grouping and Correlation and Quick Tips for Search Optimization
@aalvino73, you should try to avoid sub-searches until absolutely unnecessary. In your case your query can work without sub-searches. Try the following and confirm!
sourcetype=haproxy:http status=200 ("API1?" OR "API2?" OR "API3?")
| eval API=case(searchmatch("API1?"),"API1",
searchmatch("API2?"),"API2",
searchmatch("API3?"),"API3",
true(),"unknown")
| stats avg(date_second) as Average by API
| eval Average=round(Average,2)
Also when you end-up using transpose
or xyseries
or untable
commands to format the table output, you should also consider whether it is possible to construct the final output without using those.