topic Re: Help me with search query command in Splunk Search

Help me with search query command

sravankaripe — Wed, 17 May 2017 14:12:06 GMT

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

Re: Help me with search query command

andrey2007 — Wed, 17 May 2017 14:24:42 GMT

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

Re: Help me with search query command

kmorris_splunk — Wed, 17 May 2017 14:25:17 GMT

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

Re: Help me with search query command

DalJeanis — Wed, 17 May 2017 14:31:17 GMT

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.

Re: Help me with search query command

DalJeanis — Wed, 17 May 2017 14:34:07 GMT

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

Re: Help me with search query command

rjthibod — Wed, 17 May 2017 14:34:52 GMT

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

Re: Help me with search query command

woodcock — Wed, 17 May 2017 15:10:02 GMT

Try this:

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

Re: Help me with search query command

andrey2007 — Fri, 19 May 2017 08:36:50 GMT

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

Re: Help me with search query command

andrey2007 — Fri, 19 May 2017 08:38:29 GMT

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