Splunk Search

nested sub searches

lbogle
Contributor

Hello Splunkers,
I have a search where I have two indexes from two different indexed .csv files. I have 3 seperate searches that seem to be working okay but I may be having trouble with the format of nesting the sub searches. Essentially I have two asset indexes in two different formats. One has a set of hostnames I can identify with attributes that I'd like to use to help identify and exclude said hostnames from the main search. This seems to be working successfully, however, I want to also join and dedup those two databases to help form up a master asset list. Here is what I have so far

(index=asset_db
source="/var/asset_database/fullpull.csv"
NOT "Purpose2"=*Farm*
"Reporting Status"=Reporting 
"High Level Status"=Production
"System Name"=* 
"Last Audit"=*) | convert timeformat="%m/%d/%Y" mktime("Last Audit") as last_audit_time | eval timer=now()-(90*24*60*60) | where last_audit_time>timer
[search index=test_assets
source="C:\\Splunk Test Assets\\AD-LDAP export.csv"
earliest=-90d@d latest=-0d@d
NOT CN=*} NOT
[search
index=asset_db 
source="/var/asset_database/fullpull.csv" 
"System Name"=* 
"Purpose2"=*Farm* 
| rename "System Name" AS CN | table CN] 
| rename CN as hostname | table hostname]
| eval hostname=lower(hostname) 
| table hostname

What am I doing wrong here?
Thanks!

Tags (2)
0 Karma
1 Solution

ltrand
Contributor

The initial join isn't part of a valid command. Specifically:

...
where last_audit_time>timer [search index=test_assets
...

So you need a join or an append to make this work correctly.

Next you are testing a double NOT statement and wanting to use the output of the second nested search as a condition:

...
earliest=-90d@d latest=-0d@d
NOT CN=} NOT
[search
...

This isn't so problematic, however you are outputting subsearch2 results as a table. If you want to limit the output to only the select field then use the fields command prior to the rename. Remove that and see if it helps. Good luck!

View solution in original post

lbogle
Contributor

Hello Ayn,
We have a scenario where we have a couple asset databases but neither one of them is 100% accurate. To help maximize effectiveness of them, we sought to join them together and dedup the hostnames for a single master list so that once it was included into Splunk, we could verify multiple application databases (which we are already Splunking) against it to verify if we were saturating the asset database effectively with our install efforts (IE, is Virus Scan installed across the entire asset base) etc. Does that make sense?

0 Karma

ltrand
Contributor

The initial join isn't part of a valid command. Specifically:

...
where last_audit_time>timer [search index=test_assets
...

So you need a join or an append to make this work correctly.

Next you are testing a double NOT statement and wanting to use the output of the second nested search as a condition:

...
earliest=-90d@d latest=-0d@d
NOT CN=} NOT
[search
...

This isn't so problematic, however you are outputting subsearch2 results as a table. If you want to limit the output to only the select field then use the fields command prior to the rename. Remove that and see if it helps. Good luck!

jrodman
Splunk Employee
Splunk Employee

It's important to realize that a subsearch is implemented by the prior command. So the prior command (where, in this case) needs to know what to do with the results from a subsearch. Some commands, such as the search command, or append, or join, know what to do with a subsearch, but the where command does not.

lbogle
Contributor

Question: So it's not the fields I want to remove specifically but rather the entire event where (events equal hostnames in this case) where if the field matches "Purpose2"=Farm, we need those hostnames excluded from the report.
Can I do that with fields?

0 Karma

lbogle
Contributor

Thanks for your reply!
I'll try your suggestions.
Thank you.

0 Karma

Ayn
Legend

Not a solution as such, but generally any kind of asset DB functionality is likely much easier to work with in its original format - as CSV files used as lookups. Is there a specific reason for choosing to index them instead?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...