Subquery Event count in not work i need to count sub query

[nitesh218ss]

i create query in which i search unique no of values of one field and that unique value join to other query they work properly

index="uk" sourcetype="uk18" serviceType=2 | eval ll=substr(message,0,18) | where ll="getLogMsg returned" | rex field=message "^71^+(?<myAp>[^^]+)^" | eval result=if(myAp = 00,"sucess","fail") | join audit [search index="uk" sourcetype="uk18" serviceType=1 | dedup audit|fields + audit ] | stats count(audit) by result

But when i use [stats count] in subquery they not work i want to count total no of result in subquery. i want to subtract sub query count with total result

My query which not work is :
index="uk" sourcetype="uk18" serviceType=2 | eval ll=substr(message,0,18) | where ll="getLogMsg returned" | rex field=message "^71^+(?<myAp>[^^]+)^" | eval result=if(myAp = 00,"sucess","fail") | join audit [search index="uk" sourcetype="uk18" serviceType=1 | dedup audit|fields + audit | stats count as totall ] | stats count(audit) by result

if you see i add [stats count as totall] in sub query

[vganjare]

Hi,

You can try using eventstats instead of stats. Ideally, the join command expects one or many common fields on both sides of the command. i.e. first query should have "audit" field and the subsearch should also have "audit" field.

Thanks!!

[nitesh218ss]

i use this way they run but in result how i get in result they show field result and count(audit) only not show sub query count
my query is:
| eval ll=substr(message,0,18) | where ll="getLogMsg returned" | rex field=message "^71^+(?<myAp>[^^]+)^" | eval result=if(myAp = 00,"sucess","fail") | join audit [search index="uk" sourcetype="uk18" serviceType=1 | dedup audit|fields + audit | eventstats count as kk ] |stats count(audit) by result