Reporting

How to left join Two Splunk queries to get result?

sshubh
Explorer

I have 2 splunk queries: 

Query1: 

 

 

 

 

 

 

gives result

AccountUserconsumed Count
A1U12
A1U21

 

Query2: 

 

 

 

 

 

 

 

 

gives result

AccountUserverified Count
A1U12
A1U22

 


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

AccountUserconsumed Countverified Count
A1U122
A1U21 2




please help me with how this can be done.

tried the left join but not getting the correct result.


Thanks in advance.

Labels (2)
0 Karma

gcusello
Esteemed Legend

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
Explorer

.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Explorer

Now, I have one third log 

 

 



0 Karma

isoutamo
SplunkTrust
SplunkTrust

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.

0 Karma

sshubh
Explorer

@isoutamo  not giving the correct result

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

0 Karma

sshubh
Explorer

.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

 

0 Karma

sshubh
Explorer

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

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Explorer

.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...