Other Usage

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

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!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...