Splunk Search

not able to merge 2 queries to get the desired result

vinitpathri
Path Finder

I have 2 queries

1st is 

| rest /services/data/indexes
| fields title
| dedup title
| table title

this query is giving me all the indexes in my environment

 

2nd query is

| rest /servicesNS/-/-/saved/searches
| rex field=search "index=(?P<title>[^ ]+)"
| stats count by title
| sort -count
| table title

this is giving me all the indexes on which any savedsearch is created.

 

Now i want to see the remove the 2nd query set from 1st and just wanted to see the indexes on which there are no savedsearches in the environment.

I have tried placing "NOT" between the queries but not able to get the desired result.

Please help

 

Thanks in advance.

0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @vinitpathri,

Your first query outputs titles event rex does not match, please try below, I filtered internal indexes and also index=* searches;

| rest /services/data/indexes 
| fields title 
| dedup title 
| search title!="_*" 
| table title 
| search NOT 
    [| rest /servicesNS/-/-/saved/searches 
    | rex field=search "index=(?P<searched_index>[^ ]+)" 
    | where isnotnull(searched_index) AND searched_index!="_*" 
    | fields searched_index 
    | rename searched_index as title 
    | dedup title 
    | regex title="[^\*]" ]

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

vinitpathri
Path Finder

Thanks for your quick reply but the above query is not giving the exact required result (i am getting few of the indexes/feeds on which there is no savedsearch but not all)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I guess there are a couple of things to try. Firstly, depending on your searches, you may or may not use double quotes around index names, so trim those. Secondly, again depending on your searches, you may be searching more than one index, so use max_match=0. Finally, title is returned by saved/searches so you should probably override that with the index name found.

| rest /services/data/indexes
| fields title
| dedup title
| append [
| rest /servicesNS/-/-/saved/searches
| rex field=search max_match=0 "index=(?P<searchindex>[^\s]+)"
| eval title=trim(searchindex,"\"")
| stats count by title
]
| stats values(*) as * by title
| where isnull(count)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| rest /services/data/indexes
| fields title
| dedup title
| append [
| rest /servicesNS/-/-/saved/searches
| rex field=search "index=(?P<title>[^ ]+)"
| stats count by title
]
| stats values(*) as * by title
| where isnull(count)
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...