Splunk Search

stats count not work in sub query?

nitesh218ss
Communicator

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

Tags (3)
0 Karma

chimell
Motivator

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)
0 Karma

sideview
SplunkTrust
SplunkTrust

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)
0 Karma

nitesh218ss
Communicator

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

0 Karma

vganjare
Builder

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!!

0 Karma

vganjare
Builder

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

0 Karma

nitesh218ss
Communicator

i use head 10 in both query
they working fine

0 Karma

vganjare
Builder

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

0 Karma

nitesh218ss
Communicator

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

0 Karma

vganjare
Builder

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

Thanks!!

0 Karma

nitesh218ss
Communicator

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

0 Karma

nitesh218ss
Communicator

outer join also not work

0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...