Can you help me write a subsearch?

[Rocky31]

Firstly, i am trying to separate 1) cachekey=false in one query and 2) cachekey=true in another query and 3) with both combined in one query. Also i want avg response time and perc90 response time. Where i am facing difficulty is that count 1 and 2 combined is not equal to the count in query #3. I don't know where it went wrong in the query. Experts please spare some time to pull me out of this.

Below are the query how i wrote

1)

index=datapower CVSEVENT=EXIT opName="ABCD" status="SUCCESS" [search index=datapower opName=" 1234" cache ="false" | dedup grid | fields grid ]
| dedup grid
| table  respTime, grid | stats avg(respTime), perc90(respTime), count

2)

index=datapower  opName="ABCD" status="SUCCESS" [search index=datapower opName="1234" cache ="true" | dedup grid | fields grid ]
| dedup grid
| table  respTime, grid | stats avg(respTime), perc90(respTime), count 

3)

index=datapower  opName="ABCD" OR  opName="1234" status="SUCCESS" |stats avg(respTime), perc90(respTime), count by opName

Thanks.

[mstjohn_splunk]

hi @Rocky31,

Did @DalJeanis answer your question? If so, please resolve this post by approving their answer below. If not, keep us updated so that someone else can help solve your problem.

Also, if you're feeling generous, give out an upvote to the user that helped ya. Our users love them upvotes. 🙂

[DalJeanis]

There's no reason those three would match. Let's take the trivial situation where there is one or two of each

   index=datapower CVSEVENT=EVENT opName="ABCD" status="SUCCESS" grid=foo1 respTime=9
   index=datapower CVSEVENT=EVENT opName="ABCD" status="SUCCESS" grid=foo1 respTime=10  - DUP will be dropped
   index=datapower opName="1234" status="SUCCESS" grid=foo1 cache="true"
   index=datapower CVSEVENT=EVENT opName="ABCD" status="SUCCESS" grid=foo2 respTime=15
   index=datapower opName="1234" status="SUCCESS" grid=foo2 cache="true"
   index=datapower opName="ABCD" status="SUCCESS" grid=foo3 respTime=18
   index=datapower opName="1234" status="SUCCESS" grid=foo3 cache="false"

results 1
    avg=12 count=2

results 2
    avg=18 count=1

results 3
   opname="ABCD",avg=14,count=4
   opname="1234", count=2

You can try something like this for a consolidated report. No guarantee on this one, because I don't know your data.

(index=datapower opName="ABCD" status="SUCCESS") OR
(index=datapower opName="1234")
| fields opName grid CVSEVENT respTime cache
| dedup opName grid CVSEVENT respTime
| dedup opName grid cache
| stats values(*) as * by grid 

| stats avg(respTime) as avgResp p90(respTime) as p90Resp count by opName cache