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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...