Splunk Search

NOT the contents of a sub search from the main search

lbogle
Contributor

Hello Splunkers,
I have some successful searches that are producing accurate numbers but I am trying to put them together so that I take the results of a nested sub search, remove them from the sub search and then join the results of the sub search to the main search. The problem is that I don't know how to use or otherwise achieve the equivalent of a

NOT [search index=......

I know you need to do a join or append to get the contents of a sub search talking to the search 'above' it but is there a way to negate, subtract or do a NOT | join [search index=....

Can I add parentheses to move past this or what is the way to achieve what I am trying to do?

Thanks.

0 Karma
1 Solution

Runals
Motivator

You can do a NOT [subsearch] to remove results from your main search so not sure why that isn't working for you. Would be interesting to get a more defined use case. Are you familiar with using if or case statements within an eval? The reason I ask is, off the top of my head, the only thing I can come up with to trim out results from a search only to later join them back in is to create a complete 'picture' of activity where you are wanting to categorize. To that end if or case statements might be your best ticket.

Trying to think of an example that isn't totally contrived. Let's say you wanted to show webservers that either have or have not had a 500 series http status even if they have also had 200 ones. You could probably get away with something like

status=5* OR status=2* | eval state = if(status>=500, "uh-oh", "All Ok") | sort -status | dedup host | table host state

Frankly that's not a great query but the point is I didn't first do a subsearch to weed out one set of servers only to add them back in with an append or join. Hope that makes sense at some level.

View solution in original post

0 Karma

Runals
Motivator

You can do a NOT [subsearch] to remove results from your main search so not sure why that isn't working for you. Would be interesting to get a more defined use case. Are you familiar with using if or case statements within an eval? The reason I ask is, off the top of my head, the only thing I can come up with to trim out results from a search only to later join them back in is to create a complete 'picture' of activity where you are wanting to categorize. To that end if or case statements might be your best ticket.

Trying to think of an example that isn't totally contrived. Let's say you wanted to show webservers that either have or have not had a 500 series http status even if they have also had 200 ones. You could probably get away with something like

status=5* OR status=2* | eval state = if(status>=500, "uh-oh", "All Ok") | sort -status | dedup host | table host state

Frankly that's not a great query but the point is I didn't first do a subsearch to weed out one set of servers only to add them back in with an append or join. Hope that makes sense at some level.

0 Karma

lbogle
Contributor

Yes. That is a macro.

0 Karma

Runals
Motivator

just to confirm - is the board eating your back ticks on what appears to be a macro (filter_audit_daysago)

0 Karma

lbogle
Contributor

Here is the search I am using for which is pulling up zero events. Separately, they all work fine:

index=asset_db
source="/var/asset_database/fullpull.csv"
"Reporting Status"=Reporting
"High Level Status"=Production
NOT "Purpose2"=Farm
"Last Audit"=*
"System Name"=*
| filter_audit_daysago(90)
| rename "System Name" as CN
| join
[search index=test_assets
source="C:\Splunk Test Assets\AD-LDAP export.csv"
earliest=-90d@d latest=-0d@d
CN=* NOT [search index=asset_db index=asset_db source="/var/asset_database/fullpull.csv" "Reporting Status"=Reporting "High Level Status"=Production "System Name"=* "Purpose2"=Farm | filter_audit_daysago(90) | rename "System Name" AS CN| fields + CN]]

0 Karma

lbogle
Contributor

It's actually not a case of removing and then re-adding (apologies for my unclear description). I have two separate asset log files/databases. There is a way to organize a small subset of one of the log files that I cannot use to organize in the other, even though the two share the same assets (for the most part). The idea is to use the small subset in the nested subsearch to remove those assets from the subsearch, and then join the two asset lists(with both having the subset removed). The databases are not exactly the same which is why I am trying to join them to make a master reference.
The goal is to then actively track the log files and compare against other application logs to ensure that we are achieving the maximum software installation saturation in our environment.
Does that make sense?
Let me know if you have any ideas.
Thank you very much for your reply.
I appreciate it.

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...