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

[saurabhrai_it]

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.

[saurabhrai_it · Accepted solution]

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"

[woodcock]

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

[woodcock]

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

[saurabhrai_it]

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.

[woodcock]

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.

[saurabhrai_it]

Many thanks, will do the same

[felipesewaybric]

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)

[richgalloway]

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)