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
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.
Try this:
index=abc (sourcetype="abc" OR sourcetype="pqr")
| stats values(username) AS userName count BY ID
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
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.
Looks that "if" has no sense because "fields" command deleted sourcetype field and mycount is always equals to "1"
For some clarification, is count a field in sourcetype pqr, or do you need to perform a count?
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
@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.
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 )"