Splunk Search

How filter with join ?

Abarny
Path Finder

hi guys,
I want to filter my request where when logs{}.newStateId!=5 i recover the projects{}.id but this join isn't match and give all projects{}.id . I can't see projects{}.icma cause it takes the values on the first join and i would have all projects{}.icma by projects{}.id.

Can you tell me what wrong on my request :

index="gtav2_projects"    
|fields projects{}.id, projects{}.client.name,projects{}.icma
| stats list(projects{}.id) as projet by projects{}.client.name 
| join[search index="gtav2_projects" projects{}.icma="xxx"]
| join [search index="gta_projets" logs{}.newStateId!=5 
|fields logs{}.projectId 
| rename logs{}.projectId as 'projet' ]
|rename projects{}.client.name as Client|table Client, projet, projects{}.icma

Thanks you very much for your answers,

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index="gtav2_projects"    [search index="gta_projets" logs{}.newStateId!=5 
 |fields logs{}.projectId ]
| stats list(projects{}.id) as projet by projects{}.client.name 
 | join[search index="gtav2_projects" projects{}.icma="xxx"]
|rename projects{}.client.name as Client|table Client, projet, projects{}.icma

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index="gtav2_projects"    [search index="gta_projets" logs{}.newStateId!=5 
 |fields logs{}.projectId ]
| stats list(projects{}.id) as projet by projects{}.client.name 
 | join[search index="gtav2_projects" projects{}.icma="xxx"]
|rename projects{}.client.name as Client|table Client, projet, projects{}.icma

Abarny
Path Finder

It's okay but i don't have projects{}.icma. I have just xxx and 1 ICMA = 1 projectId. Do you know how i can do that ?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

So you want to exclude projects which has projects{}.icma="xxx" Or only include those?

0 Karma

Abarny
Path Finder

I want find all project about this Client. With projects{}.icma="xxx" I find Client, I recover all projectId about this Client and i would all projects{}.icma because each projectId has an icma.

Thanks you for your answer

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

index="gtav2_projects"    [search index="gta_projets" logs{}.newStateId!=5 
  |fields logs{}.projectId ]
  | join[search index="gtav2_projects" projects{}.icma="xxx"]
 | stats list(projects{}.id) as projet list(projects{}.icma) as icma by projects{}.client.name 
 |rename projects{}.client.name as Client|table Client, projet, projects{}.icma

Abarny
Path Finder

I'm so stupid !

Thanks Somesoni2

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...