not able to merge 2 queries to get the desired result

[vinitpathri]

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.

[scelikok]

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="[^\*]" ]

[vinitpathri]

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)

[ITWhisperer]

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)

[ITWhisperer]

| 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)