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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...