Help required: Need SPL to extract all Dashboard Queries

[satyaallaparthi]

Hi everyone,

I need some help with a SPL query.

I am trying to create an inventory of all queries running in my dashboards (both Classic and Dashboard Studio) by using the | rest /servicesNS/-/-/data/ui/views endpoint.

I need a query that produces a table with the following columns for every panel:

1. App & Dashboard Name
2. Panel Name

3. Base Search search id & Query (if the panel uses one)
4. Panel Search Query

5. Full Query (The Base Query + Panel Query combined)

I am struggling to write the SPL that extracts the Base Search IDs if any and Base Queries, & extract panel queries and then correctly maps them to the individual panels to form the full query string.

Does anyone have a working SPL query that handles the logic for both Classic and Studio?

Thanks in advance! 

[tscroggins · Accepted solution]

Hi @satyaallaparthi,

Irrespective of the use case--data dictionary, audit artifact, documentation, etc.--see below for a starting point for Simple XML dashboards to run as a user with the admin or sc_admin role. It exposes several problems:

1. Base searches have arbitrary ancestry depth:

search -> base1 -> base2 -> base3 -> ... -> baseN

You can extended the inner join with additional joins as needed to meet your depth requirements.

2. Base searches may be invalid and require loop detection, although this is moot given problem 1:

search -> base1 -> base2 -> base1 (loop)

If you extend the inner join, you can track base references and detect loops with creative use of field values and eval logic.

3. Panels may reference prebuilt panels. This was not implemented below, but the logic is similar to the join used to match searches to base searches. The additional logic would match the panel element ref and app attributes to prebuilt panels through another join.

4. Searches may reference saved searches (reports). As above, the additional logic would match the search element ref attribute to saved searches through another join.

5. The mvexpand command has conservative default limits. If you encounter mvexpand errors, see the limits.conf [default] and [mvexpand] stanzas and the max_mem_usage_mb setting for more information.

6. The join command has conservative default limits; however, 50,000 dashboard base search results may meet your requirement. If you have missing or unexpected results, see the limits.conf [join] and [searchresults] stanzas for more information.

7. Not a problem but a warning: Dashboards with the same title (object name) can exist across apps and sharing modes. The search below includes the eai:acl.app, eai:acl.owner, and eai:acl.sharing fields to help differentiate between dashboards. You could also keep the id field as an absolute reference to the object relative to the local search head.

8. The example references the local search head, which should work for standalone search heads, search head clusters, and Splunk Cloud. The logic may not work as-is from a monitoring console with many search head peers and modified splunk_server or splunk_server_group arguments.

| rest splunk_server=local /servicesNS/-/-/data/ui/views search="isDashboard=1" search="isVisible=1" search="version!=2"
| fields id eai:acl.app eai:acl.owner eai:acl.sharing title label eai:data 
| rename eai:* as *, acl.* as *
| spath input=data "dashboard.row.panel" output=panel 
| mvexpand panel 
| spath input=panel "title" output=panel_title 
| eval panel_title=coalesce(panel_title, "Untitled") 
| xpath outfield=search "//search" field=panel 
| mvexpand search 
| spath input=search "search{@base}" output=base_search_id 
| spath input=search "search.query" output=search_query 
| join type=left max=0 id base_search_id 
    [| rest splunk_server=local /servicesNS/-/-/data/ui/views search="isDashboard=1" search="isVisible=1" search="version!=2"
    | fields id eai:data 
    | xpath outfield=base_search "/dashboard/search" field=eai:data 
    | mvexpand base_search 
    | spath input=base_search "search{@id}" output=base_search_id 
    | spath input=base_search "search.query" output=base_search_query 
    | fields id base_search_id base_search_query ] 
| eval spl=replace(coalesce(base_search_query." | ".search_query, search_query), "\\|[\\s]*\\|", "|") 
| table app title owner sharing label panel_title spl

Dashboard Studio dashboards have base (chain) search ancestry and saved search problems similar to Simple XML but should be self-contained. The required logic is similar to Simple XML but limited to the visualizations and dataSources objects. A join isn't necessary because there are no XML attribute values to match:

| rest splunk_server=local /servicesNS/-/-/data/ui/views search="isDashboard=1" search="isVisible=1" search="version=2"
| fields id eai:acl.app eai:acl.owner eai:acl.sharing title label eai:data
| rename eai:* as *, acl.* as *
| spath input=data "dashboard.definition" output=data
| eval panel=json_array_to_mv(json_keys(json_extract(data, "visualizations"))), panel=mvmap(panel, spath(data, "visualizations.".panel))
| mvexpand panel
| spath input=panel "title" output=panel_title
| eval panel_title=coalesce(panel_title, "Untitled")
| spath input=panel "dataSources.primary" output=search_id
| eval search_query=spath(data, "dataSources.".search_id.".options.query")
| eval base_search_id=spath(data, "dataSources.".search_id.".options.extend")
| eval base_search_query=spath(data, "dataSources.".base_search_id.".options.query")
| eval spl=replace(coalesce(base_search_query." | ".search_query, search_query), "\\|[\\s]*\\|", "|")
| table app title owner sharing label panel_title spl

Any edits or additions are welcomed.

[tscroggins]

As an aside, this is a common question posed by auditors in compliance and cybersecurity verticals. If you need to track and implement workarounds for the various problems identified across everyone's answers, a script in Python or your language of choice is a better solution. There might be an exposed method to render search strings the same way the internal dashboard code does, but I haven't looked for one.

[PickleRick]

Actually, if a security/compliance auditor is looking at searches instead of actual permissions, there is something wrong with the scope of the audit. 😉

But yes. It's a relatively often appearing question (in this or similar form).

And no, there is no general answer for it covering all possible scenarios (base searches, eventstats, macros, dynamic parameters...).

[tscroggins]

They're usually interested in mapping the business logic to the monitored content, e.g., an audit log entry corresponding to a NIST control and the corresponding design and implementation of transformations, identification rules, human readable elements, etc.

[richgalloway]

1. This information is available from the eai:acl.app and label fields, respectively.

2. Not all panels have a name.  To get the names, parse the eai:data field and pull out the dashboard.row.panel.title and form.row.panel.title elements.

| rest splunk_server=local /servicesNS/-/-/data/ui/views
| fields eai:acl.app label eai:data
| rename eai:* as *
| spath input=data
| rename dashboard.* as *, form.* as *
| fields acl.app label row.panel.title

Note that this SPL works only with Simple XML dashboards.  That's because Dashboard Studio uses a very different name scheme for elements and because the spath command fails on DS dashboards because it contains JSON nested within XML.

3. This is tricky because base searches can exist both within and without a panel.  Also, you'll probably want to retrieve the earliest and latest elements for each query.

4. This is in the row.panel.search.query and row.panel.*.search.query elements, however, not all panels have a query.

5. This is another tricky bit.  The data returned above will be multi-valued since there may be many elements with the same name ("row.panel.table.search.query", for example).  Mapping a panel name to its query will be a challenge.

If you still want to attempt this (and, like @ITWhisperer , I wonder about the utility of this), then consider using Python instead of SPL to do it.

Depending on the use case, it may be possible to get much of the same information from Splunk's internal logs.  Tell us more about the use case so we might offer other solutions.

[satyaallaparthi]

Fair point. The goal is to build a Data Dictionary.

We need to document the actual 'Business Logic' (SPL) running behind the panels rather than just the visual titles. With hundreds of dashboards, a manual audit isn't feasible, so I need an automated SPL method to extract the underlying code using rest and spl from my search heads. 

[richgalloway]

What you're trying to do seems like overkill for a data dictionary.  It also ignores queries contained in saved searches.  I don't see how parsing search queries helps build a data dictionary; although I can see how it might show what data people are searching for.

When I work with my customers to build data dictionaries, we use nightly scheduled searches to pull lists of sources and sourcetypes from the indexes.  Using the tstats command makes this fast and lightweight.

| tstats count where index=* by index,sourcetype
| outputlookup DD_sourcetypes.csv.gz

Once the lookup file is created, you can use it to build your Data Dictionary dashboard.

Don't forget to add logic that trims the lookup file or else it might grow forever.

[ITWhisperer]

This is easier said than done. Dashboards are designed to be flexible and allow for multiple ways to do things, so even if you found a way to do this for one dashboard, it may not work for the next dashboard.

My question is why are you attempting to do this? What is your usecase? Given the complexity of the task (cost), what would you do with the information (benefit) assuming you could even get a working solution for your environment?

[satyaallaparthi]

Fair point. The goal is to build a Data Dictionary.

We need to document the actual 'Business Logic' (SPL) running behind the panels rather than just the visual titles. With hundreds of dashboards, a manual audit isn't feasible, so I need an automated SPL method to extract the underlying code using rest and spl from my searchheads.