Splunk Search

How can I merge two queries using MAP or any other command?

Explorer

I have 2 queries!

Query 1: Find top 10 API using top command

eg :

index="some_index" "abc.def.operation"=* | rename "abc.def.operation" as Operation | top limit=10 Operation

Query 2: Find peak hour and the response time of each API

eg :

index="some_index" "abc.def.operation"=API_NAME |timechart span="1h" avg("abc.def.responseTime") as ResTime, count by index| rename "ResTime: some_index" as avg_res_time, "count: some_index" as NumberOfOccurence | sort - NumberOfOccurence | head 1 | eval avg_res_time=(round(avg_res_time,2)) | eval _time=strftime(_time, "%m/%d/%y %I:%M") | rename avg_res_time as AverageResponseTime(ms), _time as "PeakTime" | eval API=tostring("API_NAME ") | table API PeakTime NumberOfOccurence AverageResponseTime(ms)

The above queries are working fine. But, I have to change the API_NAME every time when I get the result.

When I tried to map the query 1 with query 2

index="some_index" | rename "abc.def.operation" as Operation | top limit=4 Operation| map search="search index="some_index" "abc.def.operation"=$Operation$ |timechart span="1m" avg("abc.def.responseTime") as ResTime, count by index| rename "ResTime: some_index" as avg_res_time, "count: some_index" as NumberOfOccurence | sort - NumberOfOccurence | head 1 | eval avg_res_time=(round(avg_res_time,2)) | eval _time=strftime(_time, "%m/%d/%y %I:%M") | rename avg_res_time as AverageResponseTime(ms), _time as "PeakTime" | eval API=tostring("$Operation$") | table API PeakTime NumberOfOccurence AverageResponseTime(ms)"

I am getting error Unable to run query (Whole SUBQUERY) .
I tried a lot, but no luck. I will be grateful for any help.

0 Karma
1 Solution

Explorer

I used below query which is working fine but counts are getting differed from the manual one.

index="$env$" logger_name="abc.def.RequestOutInterceptor" 
 | rename "abc.def.operation" as Operation 
 | top limit=$api_count$ Operation
 | eval map_env = "$env$"
 | eval _time=strftime(_time, "%m/%d/%y %I:%M") 
 | map maxsearches=$api_count$ search="search index=$$map_env$$ "abc.def.operation"=$$Operation$$ logger_name="abc.def.RequestOutInterceptor" 
 | bucket _time span="1h"\
 | stats avg("abc.def.responseTime") as ResTime, count by abc.def.operation _time 
 | sort - count 
 | head 1 "
 | eval ResTime=(round(ResTime,2)) 
 | rename ResTime as "Average Response Time(ms)",abc.def.operation as "Operation", count as "Operation Count"

View solution in original post

0 Karma

Explorer

I used below query which is working fine but counts are getting differed from the manual one.

index="$env$" logger_name="abc.def.RequestOutInterceptor" 
 | rename "abc.def.operation" as Operation 
 | top limit=$api_count$ Operation
 | eval map_env = "$env$"
 | eval _time=strftime(_time, "%m/%d/%y %I:%M") 
 | map maxsearches=$api_count$ search="search index=$$map_env$$ "abc.def.operation"=$$Operation$$ logger_name="abc.def.RequestOutInterceptor" 
 | bucket _time span="1h"\
 | stats avg("abc.def.responseTime") as ResTime, count by abc.def.operation _time 
 | sort - count 
 | head 1 "
 | eval ResTime=(round(ResTime,2)) 
 | rename ResTime as "Average Response Time(ms)",abc.def.operation as "Operation", count as "Operation Count"

View solution in original post

0 Karma

Esteemed Legend

I am not at all saying that you are doing the right thing the right way (quite probably you are not) but for the purposes of education, there are 2 problems with your search:

1: You are not escaping your double-quotes.
2: You are not properly protecting your field names that have periods in them so that the periods are not interpreted as concatenation operators.

Try this:

index="some_index"
| rename "abc.def.operation" as Operation
| top limit=4 Operation
| map search="search index=\"some_index\" \"abc.def.operation\"=$Operation$
| timechart span=1m avg(abc.def.responseTime) AS avg_res_time, count AS NumberOfOccurence
| sort 1 - NumberOfOccurence
| eval \"AverageResponseTime(ms)\" = round(avg_res_time, 2)
| eval PeakTime = strftime(_time, \"%m/%d/%y %I:%M\")
| eval API=tostring("$Operation$")
| table API PeakTime NumberOfOccurence AverageResponseTime*"
0 Karma

Esteemed Legend

@saurabhrai_it, did you try any answers? What is your situation?

0 Karma

Explorer

The provided answers were not working, may be I hadn't done it right.

I ended up with below query;

index="$env$" logger_name="abc.def.RequestOutInterceptor" 
| rename "abc.def.operation" as Operation 
| top limit=$api_count$ Operation
| eval map_env = "$env$"
| eval _time=strftime(_time, "%m/%d/%y %I:%M") 
| map maxsearches=$api_count$ search="search index=$$map_env$$ "abc.def.operation"=$$Operation$$ logger_name="abc.def.RequestOutInterceptor" 
| bucket _time span="1h"\
| stats avg("abc.def.responseTime") as ResTime, count by abc.def.operation _time 
| sort - count 
| head 1 "
| eval ResTime=(round(ResTime,2)) 
| rename ResTime as "Average Response Time(ms)",abc.def.operation as "Operation", count as "Operation Count"

But the thing is, I am now getting different(less count to be precise) count using the above query.
I tried all MODE from the search but I got different counts.
So, I just started doing my manual work on this as its once in a while task.
Thanks for your concern.

0 Karma

Esteemed Legend

If I am understanding you correctly, you have an answer that works well enough. If it is one of these, then click Accept to close the question. If not, post your answer yourself and Accept yours.

0 Karma

Explorer

Many thanks, will do the same

0 Karma

Contributor

you can use like:

index="index" [search index="index" earliest=X | top keyX | table keyX]

is like

index="index" (keyX=1 OR keyX=2 OR keyX=N)
0 Karma

SplunkTrust
SplunkTrust

Have you tried using a subsearch to get the top operations?

index="some_index" [index="some_index" "abc.def.operation"=* | top limit=10 Operation | fields abc.def.operation | format ] |timechart span="1h" avg("abc.def.responseTime") as ResTime, count by index| rename "ResTime: some_index" as avg_res_time, "count: some_index" as NumberOfOccurence | sort - NumberOfOccurence | head 1 | eval avg_res_time=(round(avg_res_time,2)) | eval _time=strftime(_time, "%m/%d/%y %I:%M") | rename avg_res_time as AverageResponseTime(ms), _time as "PeakTime" | eval API=tostring("API_NAME ") | table API PeakTime NumberOfOccurence AverageResponseTime(ms)
---
If this reply helps you, an upvote would be appreciated.
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!