topic Re: Join 2 searches and finding the difference in the output. in Splunk Search

Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 09:54:32 GMT

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.

Re: Join 2 searches and finding the difference in the output.

DavidHourani — Mon, 15 Dec 2014 10:07:02 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 10:12:50 GMT

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.

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 10:14:00 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 10:16:43 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 10:19:37 GMT

Not understanding how the count got goofed up

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 10:23:11 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 12:22:36 GMT

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

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 12:27:47 GMT

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

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 12:33:21 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 28 Sep 2020 18:26:48 GMT

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.

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 12:48:15 GMT

No, can you provide some samples of your events?

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 28 Sep 2020 18:26:51 GMT

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"]

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 13:02:43 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 14:30:50 GMT

Why not the previous one.

Re: Join 2 searches and finding the difference in the output.

MuS — Mon, 15 Dec 2014 14:36:41 GMT

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

Re: Join 2 searches and finding the difference in the output.

arungeorge09 — Mon, 15 Dec 2014 14:54:52 GMT

But event can be only pushApns or NEAT-IN