topic Re: stats count not work in sub query? in Splunk Search

stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 05:00:07 GMT

I create a query which have sub query i want total number of event on sub query but they show blank result

My Query

index="uk" sourcetype="ukpro" serviceType=1 message="Received * bytes from IP*" 
| rename time as time1,message as Request 
| join audit [search index="uk" sourcetype="ukpro" serviceType=1 message="Deleted m_pReceivingSocket" 
             | rename time as time2, message as Responce 
             | stats count as req]
| eval itime=strptime(time1,"%H:%M:%S.%3N") 
| eval ptime=strptime(time2,"%H:%M:%S.%3N") 
| eval TimeDiff=ptime  -itime
| table time2 time1 TimeDiff audit Request Responce 
| stats avg(TimeDiff) as AVG, max(TimeDiff) as MAX, min(TimeDiff) as Min,count(Responce)

If you see in sub query last i added
.....| stats count as req
which not counting
i use eventstats but they also not work

How i count sub query event

Re: stats count not work in sub query?

vganjare — Fri, 08 May 2015 08:26:13 GMT

Hi,

The first query and subquery should have a field named audit. Also, there should be some correlation between these searches. Try outer join to see if there is any correlation between the data returned by both the searches.

Thanks!!

Re: stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 08:33:31 GMT

outer join also not work

Re: stats count not work in sub query?

vganjare — Fri, 08 May 2015 08:41:21 GMT

Can you please check if both the searches are returning valid results? Try to limit the result to 10 rows for testing.

Re: stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 10:00:43 GMT

i use head 10 in both query
they working fine

Re: stats count not work in sub query?

vganjare — Fri, 08 May 2015 10:13:00 GMT

Can you try running the entire query against these 20 results? Ideally, the join should give you some result.

Re: stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 11:49:34 GMT

when i use head 20 in sub query they give 20 result, when i use head 20 in main query they give 20 result and when i use head 20 in bot they give 19 result .Result is correct

index="uk" sourcetype="ukpro2" serviceType=1 message="Received * bytes from IP*"|rename time as time1,message as Request |head 20 | join type="outer" audit[search index="uk" sourcetype="ukpro2" serviceType=1 message="Deleted m_pReceivingSocket"| rename time as time2, message as Responce |head 20]| eval itime=strptime(time1,"%H:%M:%S.%3N") | eval ptime=strptime(time2,"%H:%M:%S.%3N") | eval TimeDiff=ptime -itime |where TimeDiff > 0 |table time2 time1 TimeDiff audit Request Responce |stats avg(TimeDiff) as AVG, max(TimeDiff) as MAX, min(TimeDiff) as Min

Re: stats count not work in sub query?

vganjare — Fri, 08 May 2015 11:57:38 GMT

Can you verify if the result is missing due to subsearch limits? This can be verified from Search Job Inspector.

Thanks!!

Re: stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 13:01:14 GMT

the result come only is audit no is match in both query

Re: stats count not work in sub query?

sideview — Fri, 08 May 2015 13:34:31 GMT

You don't want the join command here. I think you want to use append instead.

The join command by it's nature will take rows from the outer search and match them up with rows from the inner search that have the same value for the joined field (here your field called "audit"). Since your inner search ends in stats count as req, it will have no value at all for audit, so that row will be discarded.

What you want in this case, if you just want to add the results of your subsearch to the main search results, is append instead of join.

index="uk" sourcetype="ukpro" serviceType=1 message="Received * bytes from IP*" 
 | rename time as time1,message as Request 
 | append [search index="uk" sourcetype="ukpro" serviceType=1 message="Deleted m_pReceivingSocket" 
              | rename time as time2, message as Responce 
              | stats count as req]
 | eval itime=strptime(time1,"%H:%M:%S.%3N") 
 | eval ptime=strptime(time2,"%H:%M:%S.%3N") 
 | eval TimeDiff=ptime  -itime
 | table time2 time1 TimeDiff audit Request Responce 
 | stats avg(TimeDiff) as AVG, max(TimeDiff) as MAX, min(TimeDiff) as Min,count(Responce)

Re: stats count not work in sub query?

nitesh218ss — Fri, 08 May 2015 18:06:57 GMT

I use append but they not given correct result

value of audit are present in both search sub search and main search because audit no is similar for one transaction that message "Received * bytes from IP*" for start transaction and message "Deleted m_pReceivingSocket" for end transaction
so audit value are present every time if audit value are not equal in both side they not count because of join

Re: stats count not work in sub query?

chimell — Sat, 09 May 2015 09:02:56 GMT

Hi nitesh218ss
Try with this xml code

search index="uk" sourcetype="ukpro" serviceType=1 message="Deleted m_pReceivingSocket"
| rename time as time2, message as Responce
| stats count as req|appendcols[search index="uk" sourcetype="ukpro" serviceType=1 message="Received * bytes from IP*"
| rename time as time1,message as Request]
| eval itime=strptime(time1,"%H:%M:%S.%3N")
| eval ptime=strptime(time2,"%H:%M:%S.%3N")
| eval TimeDiff=ptime -itime
| table time2 time1 TimeDiff audit Request Responce req
| stats avg(TimeDiff) as AVG, max(TimeDiff) as MAX, min(TimeDiff) as Min,count(Responce)