Getting Data In

How to merge two search queries of same source and get the difference using splunk ?

kanamarlapudive
New Member

Hi team,

I have two below queries, can you please suggest how to merge and get difference of counts in separate column.

index=A | rex field=_raw command=(?Common ID\w+) | stats count by CountA

And

index=A | rex field=_raw command=(? Common ID\w+) | stats count by CountB

The Common ID i am getting through rex command.

I am looking for the below output in columns format.

Common ID  Count A  Count B   Difference ( i.e. Count A - Count B)

Kindly suggest on this any approach, i have tried subsearch and join and am unable to get the result.

0 Karma

woodcock
Esteemed Legend

Like this:

index="A" AND ("StringA" OR "StringB")
| rex "some RegEx with capture for commonID here"
|stats count(eval(searchmatch("StringA"))) AS CountA count(eval(searchmatch("StringB"))) AS CountB BY commonID
| eval Difference = CountA - CountB
0 Karma

woodcock
Esteemed Legend

Edit your post and fix your rex commands.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@kanamarlapudivenkatanagavinaykumar ,

Try using eval in stats.

Assuming that countA and B are values of a field ,

index=A | rex field=_raw command=(?Common ID\w+)
|stats count(eval(field_name=="countA")) as countA,
       count(eval(field_name=="countB")) as countB by CommonID
|eval Difference=countA-countB
0 Karma

kanamarlapudive
New Member

Hi Renjith,
it is not working, i have updated my question.

index=A Search String A| rex field=_raw command=(?CommonID\w+) | stats count as CountA by CommonID

index=A Search String B | rex field=_raw command=(? CommonID\w+) | stats count as CountB by CommonID

Common ID i am getting through rex command.

I am looking for the below output in columns format.

Common ID Count A Count B Difference ( i.e. Count A - Count B)

Kindly suggest on this any approach, i have tried subsearch and joins. Unable to get the result.

0 Karma

kanamarlapudive
New Member

Hi renjith,
I found the partial solution for my problem.

index=A ("StringA" OR "StringB")
|stats count(eval(searchmatch("StringA"))) as CountA ,count(eval(searchmatch("StringB"))) as CountB by commonID

with this i am able to achieve the data in below format.

Common ID Count A Count B

I am not getting Difference as another column, can you please tell how to get the difference as a separate column in results.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@kanamarlapudivenkatanagavinaykumar ,
whats the result of ?

|eval diff=CountA - CountB 
0 Karma

kanamarlapudive
New Member

Hi team,

I have corrected the statements for everyone understanding.

Hi team,

I have two below queries, can you please suggest how to merge and get difference of counts in separate column.

index=A Search String A| rex field=_raw command=(?Common ID\w+) | stats count as CountA by CommonID

index=A search string B | rex field=_raw command=(? Common ID\w+) | stats count as CountB by CommonID

Common ID i am getting through rex command.

I am looking for the below output in columns format.

Common ID Count A Count B Difference ( i.e. Count A - Count B)

Kindly suggest on this any approach, i have tried subsearch and joins. Unable to get the result.

0 Karma