Splunk Search

Left Join search with lookup over inputlookup

CarmineCalo
Path Finder

Ciao , I'm trying to solve the following problem.
I've a main search like this

index=major _static
| fields _time, id_inc, start_inc, end_inc, duration, name_cust, name_itbs, name_itsc, name_its, level_inc, asset, CI
| dedup id_inc
that give me a list of incident.

I need to append to this search, for each Application Code (CI) , the required availability.
The information can be piped from the following subsearch, based on inputlookup and lookup commands:

| inputlookup append=t DOM_ServiceCatalogueLookup
| rename ApplicationID as CI
| lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeeklyAvailability
| stats max(ReqWeeklyAvailability) as MaxAva by CI

I'm trying to connect the two searches through the following line of command:

main search
| join type=left CI
[subsearch]

but it's not working, I finally got maxAva column empty while printing the results...
any help?

Tks in advance,
Carmine

0 Karma
1 Solution

CarmineCalo
Path Finder

I solved the problem changing the content of the subsearch.
I replace the previous commands with the following set of istructions:

| join type=left CI
[ search index=oro_major_static
| fields CI
| lookup DOM_ServiceCatalogueLookup ApplicationID as CI OUTPUTNEW PrimaryWindows as PrimaryWindows
| lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeeklyAvailability
| mvexpand PrimaryWindows
| stats max(ReqWeeklyAvailability) as maxAva by CI]

now it works 🙂

View solution in original post

0 Karma

CarmineCalo
Path Finder

I solved the problem changing the content of the subsearch.
I replace the previous commands with the following set of istructions:

| join type=left CI
[ search index=oro_major_static
| fields CI
| lookup DOM_ServiceCatalogueLookup ApplicationID as CI OUTPUTNEW PrimaryWindows as PrimaryWindows
| lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeeklyAvailability
| mvexpand PrimaryWindows
| stats max(ReqWeeklyAvailability) as maxAva by CI]

now it works 🙂

0 Karma

elliotproebstel
Champion

Great! Glad you got it solved. It's best if you accept your answer so it shows the question as solved. 🙂

0 Karma

elliotproebstel
Champion

When you run the main search and the intended subsearch, they both return results as expected, right? And does the capitalization of the CI fields match? Is there any chance that intended subsearch is timing out?

0 Karma

CarmineCalo
Path Finder

Ciao,
if i ran separately the searches they both provide results.
Capitalization is fine (CI is a 3 letter full capital code), as far as in a separate trial i just lookup DOM_ServiceCatalogueLookup to retrieve "Cluster_Availability" fields and it worked as expected...

0 Karma

elliotproebstel
Champion

Hmm...Can you share an event from the primary search and an event from the subsearch that you would expect to be joined? The best way to share these would be to use the code button 101010 to preserve formatting. Maybe if we can see a few events that should be joined, we can see if there is anything obvious that would prevent the two from being joined.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...