Is there a way to find (scheduled) searches that are not used in at least one dashboard?

[HeinzWaescher]

Hi,

Is there a way to find (scheduled) reports that are not used in at least one dashboard?

Thanks in advance

Heinz

[cramasta]

I like this question because the answer can help in cleaning up old knowledge objects in an installation.

Using the rest commands we can get all the information needed to answer the question.

This search will pull all the dashboard names and their source.
|rest /servicesNS/-/-/data/ui/views | rename eai:* as * |rename acl.* as * | search isVisible=1 | fields title data

This search will pull all the saved search names and their queries
| rest /servicesNS/-/-/saved/searches/ | search is_scheduled=1 | dedup title | fields title

Now that we have that information needed lets figure out how to answer the question...
I thought about using a wildcarded lookups (exporting the search names and then performing a wildcarded lookup in the data field), but I wanted something that didnt require me to modify any props/transforms files and also worked with a single query.

Also thought about using rex to extract all of the possible ways a saved search can be referenced in a dashboards and then some other splunk foo to get the final report but I figured not to go that route.

Here is what I came up with. Its not pretty but at first glance it seems to be working. You end up wih a count of how many times each saved search is referenced in all of the dashboards. It basically uses a sub-search that gets a list of all the saved search names and formats them into a stats string which is doing a conditional like count on the data field for each of the saved search names. Fair warning that this is basically just doing a search for the saved search name and doesn't check to see if its part of module thats calling a saved search. Meaning if you have a saved search called "ERROR" you might get some false positives in the count as any occurrence of the string ERROR will be included in the count.

|rest /servicesNS/-/-/data/ui/views splunk_server=local | rename eai:* as * |rename acl.* as * | search isVisible=1| fields title data  | stats [| rest /servicesNS/-/-/saved/searches/ splunk_server=local | search is_scheduled=1   | dedup title  | fields title  | eval savedSearch="count(eval(like(data,\"%".title."%\"))) AS \"".title."\""  | stats values(savedSearch) AS savedSearch | nomv savedSearch| return $savedSearch]  | transpose | rename column AS savedSearchName "row 1" AS countOfTimesFoundInDashboards

Would love to hear how other people might have tackled this in hopefully a much simpler way.

[chimell]

Hi HeinzWaescher

Yes in you splunk web , go to Settings then click on "Searches, reports, and alerts" and find you report in the opening page

[HeinzWaescher]

This tells me whether it is scheduled or not, but I can't see any information about the usage in dashboards.

[chimell]

In "Searches, reports, and alerts" page , we can see all the schedules or not schedule report , we can also see the reports that belong to a specific app that we choose in App context

[bjensen_citti]

This search will provide you with a list you can easily filter further for that purpose:

| rest /servicesNS/-/-/saved/searches/ 
| search is_scheduled=1 disabled=0
| eval dashboardtitle=substr(title,17) 
| table title eai:acl.app cron_schedule dashboardtitle
| rename * as search.* 
| rename search.dashboardtitle as title
| join type=outer title 
[| rest /servicesNS/-/-/data/ui/views 
| rename eai:* as * 
| rename acl.* as * 
| search isVisible=1 
| fields - data 
| table title app id disabled
| rename * as view.* 
| rename view.title AS title ]
| eval has_dashboard=if(isnotnull('view.id'),1,0)
| table title has_dashboard search.* view.*

You can add fields from either part as you need (e.g. in the upper part: action.email=1 etc)