topic Re: How to use self join in Splunk Search

How to use self join

shugup2923 — Thu, 10 Oct 2019 12:52:00 GMT

Hi All,
I have table in which I have columns such as name, id, type, business group etc
type field has 2 values 'user' or 'approver', there are some name which are both are user as well as approver for same id.

My requirement is to create a two new columns 'isapprover' and 'isuser' which will contain value yes or no.

I was able to create these two columns by using
if type is user put yes in isuser and
if type is approver put yes in isapprover.

Issue: If any name type is both user and approver I need yes and yes value in 'isapprover' and 'isuser' in every row where that name is present for same id.

Any help will be appreciated.

Re: How to use self join

richgalloway — Thu, 10 Oct 2019 13:11:32 GMT

If a name is both a user and approver, is that two separate rows or two values within the type field or two type fields?

Re: How to use self join

shugup2923 — Thu, 10 Oct 2019 13:16:12 GMT

It will be two different rows , for ex-
name id group type
xyz 123 abc user
xyz 123 def approver
xyz 123 ghi user

expected result
name id group type isuser isapprover
xyz 123 abc user yes yes
xyz 123 def approver yes yes
xyz 123 ghi user yes yes

Re: How to use self join

wmyersas — Thu, 10 Oct 2019 14:02:40 GMT

If I understand you correctly, you're going to replace/reformat the current lookup to remove the type field, and instead replace it with a combination of isuser and isapprover fields. Is that correct?

(FWIW - since every one of these entries must be a user, the isuser field is going to be redundant)

Should be able to do something like the following:

| inputlookup mylookup.csv
| stats values(type) as type by name id business_group [...]
| eval isuser="Yes"
| eval isapprover=if(mvfind(type,"approver")>=0,"Yes","No")

Append a

| outputlookup mylookup.csv

line to end of the search when you verify it's exactly how you want to see it

Re: How to use self join

richgalloway — Thu, 10 Oct 2019 15:29:04 GMT

Try this.

index=foo (type="user" OR type="approver") | fields name, id, type. group
| eval isapprover=if(type="approver", "yes", "no"), isuser=if(type="user", "yes", "no")
| stats values(*) as * by name
| table name id group type isuser isapprover

Re: How to use self join

shugup2923 — Fri, 11 Oct 2019 06:27:35 GMT

Thanks it worked , just one change what I did is I have used eventstats as there were other fields also 🙂

Re: How to use self join

shugup2923 — Fri, 11 Oct 2019 06:28:34 GMT

Thanks for your efforts really appreciate your time but however it did not met my requirement