Splunk Search

How to use self join

shugup2923
Path Finder

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.

1 Solution

wmyersas
Builder

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

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

shugup2923
Path Finder

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

0 Karma

wmyersas
Builder

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

shugup2923
Path Finder

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

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.
0 Karma

shugup2923
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...