How to left join Two Splunk queries to get result?

[sshubh]

I have 2 splunk queries: 

Query1: 

gives result

Account	User	consumed Count
A1	U1	2
A1	U2	1

Query2: 

gives result

Account	User	verified Count
A1	U1	2
A1	U2	2

I wanted to do a left join on both to produce the following result:

Account	User	consumed Count	verified Count
A1	U1	2	2
A1	U2	1	2

please help me with how this can be done.

tried the left join but not getting the correct result.

Thanks in advance.

[gcusello]

Hi @sshubh,

use join command only when you haven't any other solution because it's a very slow command and has the limit of 50,000 results in the subsearch..

In your case, please try:

index="test" ("data consumed by" OR "data verified by")
| rex field=_raw "data\sconsumed\sby\sAccountId=(?P<Account>\d+),\sUserName=(?P<User>.*)"
| eval type=if(searchmatch("data consumed by"),"consumed","verified")
| stats 
   count(eval(type="consumed")) AS "Consumed Count"
   count(eval(type="verified")) AS "Verified Count"
   BY Account User

 Ciao.

Giuseppe

[sshubh]

.

[isoutamo]

Hi

you should try to change rex to 

| rex field=_raw "data\s(consumed|verified)\sby\sAccountId=(?P<Account>\d+),\sUserName=(?P<User>.*)"

That way it look both consumed and verified events.

r. Ismo 

[sshubh]

Now, I have one third log 

[isoutamo]

As you have three separate values you should change little this logic. You should remove this line. It's no more needed as we match all those options later.

| rex field=_raw "data\sconsumed\sby\sAccountId=(?P<Account>\d+),\sUserName=(?P<User>.*)"

 Then modify this one to add a new verb into it

| rex field=_raw "data\s(consumed|verified|sent)\sby\sAccountId=(?P<Account>\d+),\sUserName=(?P<User>.*)"

then no need for you addition for sent event. And change the next like

| eval type=case(
            searchmatch("data consumed by"), "consumed",
            searchmatch("data verified by"), "verified",
            searchmatch("data sent by"), "sent",
            true(), "unkown")
| stats 
   count(eval(type="consumed")) AS "Consumed Count"
   count(eval(type="verified")) AS "Verified Count"
   count(eval(type="sent")) AS "Sent Count
   BY Account User

 I think that those should fix your query?

When you will add a new verbs, you should just modify those there lines to get it work.

[sshubh]

@isoutamo  not giving the correct result

[isoutamo]

Can you give some sample data and explain how this is wrong? Based on your explanation this should work, so I expecting that there are something in your source data....

[sshubh]

.

[isoutamo]

Can you try something like this

| makeresults 
| eval _raw="data consumed by A1, U1
data consumed by A1, U1
data consumed by A1, U2
data verified by A1, U1
data sent by A1
data sent by A1
data sent by A1" 
| multikv noheader=t
| table _raw
``` previous make sample data ```
| rex "data\s\w+\sby\s(?P<Account>\w+)(?:,\s(?<User>\w+))?"
| eval type=case(
            searchmatch("data consumed by"), "consumed",
            searchmatch("data verified by"), "verified",
            searchmatch("data sent by"), "sent",
            true(), "unkown")
| eventstats count(eval(type=="sent")) as "Data Sent" by Account
| stats 
   count(eval(type="consumed")) AS "Consumed"
   count(eval(type="verified")) AS "Verified"
   values("Data Sent") as "Data Sent"
   BY Account User
| fields Account, "Data Sent", User, Consumed Verified

[sshubh]

@isoutamo this kind of work after making some adjustments, but can we make to print single value instead of multiple same value of 

[isoutamo]

Issue was original group by which adding those additional lines/value pairs here. You could try to add this after the last stats and before fields.

| stats list(Consumed) as Consumed list(Verified) as Verified list(User) as User by Account "Data Sent" 

I suppose that this fix your table formatting issue. 

[sshubh]

.

[isoutamo]

Ok, then you need to do it little bit differently. There are several ways to do it, here is one example!

1st separate to get Account and User to own rex like

| rex "data\sconsumed\sby\sAccountId=(?P<Account>\d+)"
| rex ",\sUserName=(?P<User>.*)"

I think that 1st eval is ok, but you need to add something to User to get last stats to work

| eval User = if (isnull(User), "N/A", User)
| stats 
   count(eval(type="consumed")) AS "Consumed Count"
   count(eval(type="verified")) AS "Verified Count"
   count(eval(type="sent")) AS "Sent Count
   BY Account User

 Here I expecting that you haven't user called "N/A". If you have, then name that "NULL" User to something else.