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.
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
.
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
Now, I have one third log
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.
@isoutamo not giving the correct result
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....
.
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
@isoutamo this kind of work after making some adjustments, but can we make to print single value instead of multiple same value of
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.
.
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.