Help me with search query command

[sravankaripe]

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

[woodcock]

Try this:

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

[rjthibod]

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 · Accepted solution]

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.

[andrey2007]

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

[kmorris_splunk]

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

[andrey2007]

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

[DalJeanis]

@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.

[andrey2007]

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