- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try this:
index=abc (sourcetype="abc" OR sourcetype="pqr")
| stats values(username) AS userName count BY ID
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Looks that "if" has no sense because "fields" command deleted sourcetype field and mycount is always equals to "1"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


For some clarification, is count a field in sourcetype pqr, or do you need to perform a count?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 )"
