Join 2 searches and finding the difference in the output.

[arungeorge09]

I want to join 2 queries by a common field and the counts of the searches are different. I want to work on the dataset which does not join.

[arungeorge09 · Accepted solution]

My two searches are here.

index=xyz event="NEAT-IN" platform="APNS" | join type=inner platform,batch [search index=xyz event="push*"] | stats count(event)

Each individual query count is different but when joined it always counts the joined count as the largest count.

[MuS]

no need to join, try this:

index=xyz event="NEAT-IN" platform="APNS"  OR event="push*" 
| stats count(eval(event="NEAT-IN")) AS count_NEAT count(eval(event="push*")) AS count_push by platform, batch

cheers, MuS

[arungeorge09]

@Mus Executed the query but count_push is coming as zero . Looks like splunk does not do count(eval(event="push*"))

[MuS]

or try this:

index=xyz event="NEAT-IN" platform="APNS"  OR event="push*" 
 | stats count(eval(event="NEAT-IN")) AS count_NEAT count(eval(if(match(event, push) , 1, 0 ))) AS count_push by platform, batch

[arungeorge09]

Now both counts are coming as 578.

Query used:
index=xyz event="NEAT-IN" platform="APNS" OR event="push*"
| stats count(eval(event="NEAT-IN")) AS count_NEAT count(eval(if(match(event, push) , 1, 0 ))) AS count_push by platform

The output is 578,578 . This is not correct @MuS.

It should be 578,488

Any Idea what is happening.

[arungeorge09]

NEAT-IN

<167>1 2014-12-14T18:23:49.908-07:00 x.y.com neat 901 apns [meta@28281 sequenceId="69599" sysUpTime="1109890868"][analytics@28281 event="NEAT-IN" platform="APNS"] {"timestamp":"1418606629897","platform":"apns","alertId":"1404841346","appId":"appId","args":{"time":"1418606629788","batch":"48288","tms_id":"tmsId","src":"src"},"deviceToken":"devToken","alert":"Exciting Game"}

pushApns

<167>1 2014-12-14T18:23:49.909-07:00 x.y.com neat 6627 apns [meta@28281 sequenceId="71272" sysUpTime="1109890867"][analytics@28281 event="pushApns" platform="APNS" outcome="0" errorCode="0" errorDesc="Push to apns success" errorContext="TCP-SSL" operation="PUSH_APNS" opTime="0" startTime="1418606629908" appId="appId" deviceToken="devToken" args="{\"time\":\"1418606629788\",\"batch\":\"48288\",\"tms_id\":\"tms_id\",\"src\":\"src\"}" txId="2099269910"]

[MuS]

this should provide the correct count:

index=xyz event="NEAT-IN" OR event="pushApns" platform="APNS" 
| stats count(eval(event="NEAT-IN")) AS count_NEAT count(event="pushApns") AS count_push

[arungeorge09]

Why not the previous one.

[MuS]

because count(event="pushApns") is not the same like count(eval(if(match(event, push) , 1, 0 )))
the first will match only the event field which contains pushApns but the later will match any event field containing push like foopush or pushbaz or pushApns

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions

[arungeorge09]

But event can be only pushApns or NEAT-IN

[MuS]

No, can you provide some samples of your events?

[MuS]

could be try it without the star/wildcard and use the value you need instaed

[arungeorge09]

Not understanding how the count got goofed up

[DavidHourani]

What do you mean by missing ? You lose the rows after you perform a join ?

[arungeorge09]

I have 2 searches and there counts are different. I want to find the rows which don't join.

[arungeorge09]

event="NEAT-IN" has 578 count
event="pushApns" has only 488 count
I want to know which 578-488 are the missing ones and do further query on them