How to use multiple search results in another search to get my expected output?

[guruwells]

Hi All,

I have 2 search statements which are giving 2 different search results with same index and sourcetype. I want to use these results in another search statement and need to get the result.

1st search:

index=main sourcetype=iis| where time_taken > 4000 |stats count(s_computername) as "# of Hits > 4 seconds"| 

2nd search:

index=main sourcetype=iis| eval u_name =replace(cs_username, "0#","")| eval u_name1= replace(u_name, ".w|","")|eval u_name2=replace(u_name1,"\|","")|stats dc(u_name2) AS "Unique Users", count(s_computername) as "Elements/Hits"

And looking for:

index= main sourcetype=iis| eval result= "# of Hits > 4 seconds" / Elements/Hits| eval resultvalue= result * 100

I am using appendcols to get the results from various search statements.

Your help is appreciated.

Thanks,
Guru

[sundareshr · Accepted solution]

You could try without the subsearch like this.

index=main sourcetype=iis | eval u_name =replace(cs_username, "0#","") | eval u_name1= replace(u_name, ".w|","") | eval u_name2=replace(u_name1,"\|","") | stats count(eval(time_taken>4000)) AS hits dc(u_name2) as users count(s_computername) AS elements  | eval resultsvalue = (hits/elements)*100 | rename hits AS "# of Hits > 4 seconds" elements AS  "Elements/Hits"

[guruwells]

Excellent. it's worked for me. Thanks for the help. It's saved lot of time.

[guruwells]

Hi Sundaresh,
I have one more question if you are ok with that.
Requirement is:
statement1: index=main sourcetype=iis earliest=-1d@d|eval csuri=lower(cs_uri_stem)| where csuri="/pages/default.aspx" AND sc_status!="401"|stats count(eval(time_taken>4000)) as "Page Views > 4 seconds" count(eval(time_taken>2500)) as "Page Views > 2.5 seconds" | appendcols[search index=main sourcetype=iis earliest=-1d@d|eval csuri=lower(cs_uri_stem)| Where csuri="/view/pages/default.aspx" AND sc_status!="401"|stats avg(time_taken) as "Page response time", count as "Page views"]

statement2: index=main sourcetype=iis|eval resultvalue= (Page Views > 4 seconds/Page Views) *100.

I am looking all 2 statements into single statement to get the result. In above answer we have looked one condition across statement. But here we can find various conditions across all 2 statements.

Please help me on this if you have any idea?

Thanks.
Guru Prasad K

[guruwells]

How to fulfill below requirement in single satement.
satement1:
index=main sourcetype=iis earliest=-1d@d|eval csuri=lower(cs_uri_stem)| where csuri="/pages/default.aspx" AND sc_status!="401"|stats count(eval(time_taken>4000)) as "Page Views > 4 seconds" count(eval(time_taken>2500)) as "Page Views > 2.5 seconds"
statement2:
appendcols[search index=main sourcetype=iis earliest=-1d@d|eval csuri=lower(cs_uri_stem)| Where csuri="/view/pages/default.aspx" AND sc_status!="401"|stats avg(time_taken) as "Page response time" |stats count as "Page views"]

statement3: index= main sourcetype=iis |eval resultset= (Page Views> 4 seconds"/"Page Views") *100.

In above answer it's really helped I got the output accordingly. But here couples of conditions are exist. How to make these 3 statements into single search statement.

Your help is appreciated.