topic Re: Missing subsearch results in Splunk Search

Missing subsearch results

gbwilson — Wed, 20 Jun 2018 17:56:44 GMT

I've created a search that is composed of two subsearches. I have a dashboard where if I search an application name, it tells me all the underlying infrastructure that supports that app.

(index=cms_db_server OR index=cms_app_server) 
| join type=left max=0 Database,InstanceName [search index="cms_app_db"]
| join type=left max=0 VM [search (index="cms_vm")]
| dedup Host VM Application
| table Host VM Application

Above is the search that I'm using. When I use this query, data from both subsearches is displayed. However, if I limit the search by application name, I only see results from one of the joins, not both. Why is it that when I query the search as is above- I can see the data from both subsearches, but when I limit the search by an app name the results from one subsearch is not displayed?

Any insight would be helpful as I am new to Splunk.

Re: Missing subsearch results

DalJeanis — Wed, 20 Jun 2018 23:17:46 GMT

You would have to show us how you added the limitation for the application name

However, we can help you clear up the search results a bit. Remove from each of these fields commands any fields that don't exist on that branch of the search...

 (index=cms_db_server OR index=cms_app_server) 
| fields  Host, VM, Database, InstanceName, Application 
| join type=left max=0 Database,InstanceName [
    search index="cms_app_db" 
    | fields  Host, VM, Database, InstanceName, Application
    ]
| fields  Host, VM, Database, InstanceName, Application
| join type=left max=0 VM [
    search (index="cms_vm") 
    | fields  Host, VM, Database, InstanceName, Application
    ]
| dedup Host VM Application
| table Host VM Application

My assumption is that, since you only need three fields, and you have two joins, you are using the Database and InstanceName to get the VM, and then the VM to get the Host, something like this...

 (index=cms_db_server OR index=cms_app_server) 
| fields  Application, Database, InstanceName 
| join type=left max=0 Database,InstanceName [
    search index="cms_app_db" 
    | fields  VM, Database, InstanceName
    ]
| fields  VM, Database, InstanceName, Application
| join type=left max=0 VM [
    search (index="cms_vm") 
    | fields  Host, VM
    ]
| dedup Host VM Application
| table Host VM Application

...and so you should be able to add this line after the first line to limit the Application value...

| where Application="foo"

... however, you could also just leave the earlier part in a base search, and have a post-processing query that does the same thing to limit the results to your requested application.

| where Application="foo"

Re: Missing subsearch results

gbwilson — Thu, 21 Jun 2018 14:22:15 GMT

@DalJeanis

Thanks for your help, the second search you sent works well enough. However, I'm still not getting all the results I should be seeing.

(index=cms_db_server OR index=cms_app_server) $appservicefield$
 | fields  VM, Application, Database, InstanceName 
 | join type=left max=0 Database,InstanceName [
     search index="cms_app_db" 
     | fields  VM, Database, InstanceName
     ]
 | fields  VM, Database, InstanceName, Application
 | join type=left max=0 VM [
     search (index="cms_vm") 
     | fields  Host, VM
     ]
 | dedup Host VM Application
 | table Host VM Application

I really want to see these results displayed in a dashboard. I'm trying to use a token to search on the Application name. I'm still only seeing results from one join. If I name an Application where data is coming from both joins, the search results from one join don't display for some reason still.

Re: Missing subsearch results

gbwilson — Tue, 29 Sep 2020 20:03:22 GMT

So I think I figured out why only some results are displaying, but I still can't figure out a good solution. Basically, when I search an Application name, results are only displayed from cms_app_server because there is an Application column in that index. In the cms_db_server index no Application column exists.

However, in the results I can see Applications tied to VMs from the cms_db_server index. It's just when I try to search an App name that the results are limited.

Re: Missing subsearch results

gbwilson — Thu, 21 Jun 2018 16:44:48 GMT

Figured it out. Thanks for your help! Worked through it.

(index=cms_app_db) OR (index=cms_app_server)  Atlassian
 | fields  Application, VM, Database, InstanceName
 | join type=outer max=0 Database, InstanceName [
     search index="cms_db_server" 
     | fields  VM, Application, Database, InstanceName
     ]
 | fields  VM, Application, Database, InstanceName
 | join type=outer max=0 VM [
     search (index="cms_vm") 
     | fields  Host, VM
     ]
 | dedup Host VM Application
 | table Host VM Application

Had to switch a few indexes around and add a field to row 5.

Re: Missing subsearch results

woodcock — Sat, 30 Jun 2018 23:42:45 GMT

@gbwilson, you should click Accept to give @daljeanis the points and close the question!

Re: Missing subsearch results

DalJeanis — Mon, 02 Jul 2018 22:15:30 GMT

@gbwilson - yes, the hardest part of these "splunk stew" solutions, when they don't all have a common key, is to roll the data together in the right order.