Splunk Search

Help me with search query command

sravankaripe
Communicator

help me with JOIN query for my usecase
i have
index=abc sourcetype=abc
index=abc sourcetype=pqr

In sourcetype=abc i have fields userName and ID.
In sourcetype=pqr i have fields ID and i want to know count made by the ID

i want to display it in a table like

userName ID count
name1 101 3
name2 102 2
name3 103 1

please help me with join query

alt text

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Don't use a join if you can avoid it. In this case, you do this ---

index=abc (sourcetype=abc OR sourcetype=pqr)
| fields userName ID 
| eval mycount=if(sourcetype=abc,0,1)
| stats values(userName) as userName, sum(mycount) as mycount by ID

The records from abc will have a value in the userName field, and for the ones from pqr that field will be NULL.
The records from abc will have 0 in the mycount field, and for the ones from pqr that field will be 1.
Stats will therefore give you the count of pqr and the userName value of abc.

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

index=abc (sourcetype="abc" OR sourcetype="pqr")
| stats values(username) AS userName count BY ID

rjthibod
Champion

You should try to do this in one query and not using join.

index=abc (sourcetype=abc OR sourcetype=pqr) ID=* 
| fields ID userName sourcetype
| stats values(userName) as userName count(eval(sourcetype=="pqr")) as Count by ID 
| table userName ID Count

DalJeanis
SplunkTrust
SplunkTrust

Don't use a join if you can avoid it. In this case, you do this ---

index=abc (sourcetype=abc OR sourcetype=pqr)
| fields userName ID 
| eval mycount=if(sourcetype=abc,0,1)
| stats values(userName) as userName, sum(mycount) as mycount by ID

The records from abc will have a value in the userName field, and for the ones from pqr that field will be NULL.
The records from abc will have 0 in the mycount field, and for the ones from pqr that field will be 1.
Stats will therefore give you the count of pqr and the userName value of abc.

0 Karma

andrey2007
Contributor

Looks that "if" has no sense because "fields" command deleted sourcetype field and mycount is always equals to "1"

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

For some clarification, is count a field in sourcetype pqr, or do you need to perform a count?

0 Karma

andrey2007
Contributor

Maybe this would helpful

index=abc sourcetype=abc OR index=abc sourcetype=pqr | stats count as Count by ID
| join type=left ID [search index=abc sourcetype=abc | stats latest(userName) as userName by ID] | table userName ID Count

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@andrey2007 - Your first stats command's count will include a count of the records in abc. OP wants the count from pqr only. Remove the sourcetype=abc from the first portion of the query to get the desired answer. Also, see my answer for a non-join method that will avoid the limit on subsearches.

0 Karma

andrey2007
Contributor

Sure, you are right "index=abc sourcetype=abc OR index=abc sourcetype=pqr" should be changed to something like "index=abc (sourcetype=abc OR sourcetype=pqr )"

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...