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