Splunk Search

Help me with search query command

Path Finder

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

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

Esteemed Legend

Try this:

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

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

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

Contributor

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

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

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

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