topic How to use a scheduled report as base for dashboard? in Dashboards & Visualizations

How to use a scheduled report as base for dashboard?

reynlds — Mon, 15 Apr 2019 21:00:38 GMT

Trying to use a scheduled report as the base for a dashboard because the dashboard users won't have access to the index. Both the report and dashboard exist in the same app and I believe permissions are applied correctly. I've read through a couple of answered questions on this forum, but it's not making sense to me. I've tried all the suggested solutions, but still get a blank dashboard. Happy to share my source if needed.

Re: How to use a scheduled report as base for dashboard?

grittonc — Tue, 16 Apr 2019 00:30:05 GMT

I'm assuming that since you said you have a blank dashboard and not a message that no artifacts were found, that all of the permissions are set up correctly.

One of the pitfalls of using a scheduled search (with loadjob) is that the time picker is applied to jobs themselves and not the results of the job. So if your dashboard is looking for data from the previous month and the scheduled search results are from an hour ago, that will not be in the range of the time picker.

There is a workaround though. In your dashboard XML, add these lines to your time picker:

        <change>
         <eval token="time.earliest_epoch">if(isnum('earliest'),'earliest',relative_time(now(),'earliest')</eval>
         <eval token="time.latest_epoch">if(isnum('latest'),'latest',relative_time(now(),'latest')</eval>
       </change>

Then add something like this to your panel searches, after invoking loadjob:

          | search _time>=$time.earliest_epoch$ AND _time<$time.latest_epoch$

Re: How to use a scheduled report as base for dashboard?

reynlds — Tue, 16 Apr 2019 12:43:34 GMT

Maybe it's best if I simply include the xml:

<form>
  <label>My Authentication Activity Dashboard</label>
  <fieldset submitButton="false" autoRun="true">
    <input type="text" token="username">
      <label>Username</label>
    </input>
    <input type="dropdown" token="event_code">
      <label>Event Type</label>
      <choice value="*">All Authentication Activity</choice>
      <choice value="0">Login Succeeded</choice>
      <choice value="3">Logout Succeeded</choice>
      <choice value="4">Session Expired</choice>
      <choice value="1">Invalid Username</choice>
      <choice value="2">Invalid Password</choice>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
    <input type="time" token="field1">
      <label></label>
    </input>
  </fieldset>
  <row>
    <panel>
      <event>
        <search base="My_Auth_Log_Report">
          <query>fields *</query>duser=$username$ evtcode=$event_code$</search>
      </event>
    </panel>
  </row>
</form>

Re: How to use a scheduled report as base for dashboard?

grittonc — Tue, 16 Apr 2019 12:51:42 GMT

Does your time input have a default value?

Also, I'm not sure what duser=$username$ evtcode=$event_code$ is doing outside your query tag so I've moved it inside.

Give this a try:

 <form>
   <label>My Authentication Activity Dashboard</label>
   <fieldset submitButton="false" autoRun="true">
     <input type="text" token="username">
       <label>Username</label>
     </input>
     <input type="dropdown" token="event_code">
       <label>Event Type</label>
       <choice value="*">All Authentication Activity</choice>
       <choice value="0">Login Succeeded</choice>
       <choice value="3">Logout Succeeded</choice>
       <choice value="4">Session Expired</choice>
       <choice value="1">Invalid Username</choice>
       <choice value="2">Invalid Password</choice>
       <default>*</default>
       <initialValue>*</initialValue>
     </input>
     <input type="time" token="field1">
       <label></label>
               <change>
         <eval token="time.earliest_epoch">if(isnum('earliest'),'earliest',relative_time(now(),'earliest')</eval>
         <eval token="time.latest_epoch">if(isnum('latest'),'latest',relative_time(now(),'latest')</eval>
       </change>
     </input>
   </fieldset>
   <row>
     <panel>
       <event>
         <search base="My_Auth_Log_Report">
           <query>fields * |search duser=$username$ evtcode=$event_code$
               | search _time&gt;=$time.earliest_epoch$ AND _time&lt;$time.latest_epoch$</query>
        </search>
       </event>
     </panel>
   </row>
 </form>

Re: How to use a scheduled report as base for dashboard?

grittonc — Wed, 30 Sep 2020 00:06:07 GMT

What is the time field called in My_Auth_Log_Report, and what do the values look like?

Re: How to use a scheduled report as base for dashboard?

reynlds — Tue, 16 Apr 2019 13:23:35 GMT

The column is called "Time", but the field is "timestamp". Here are some sample entries:

Apr 02 2019 07:21:28.737 CDT

Apr 02 2019 12:36:51.684 CDT

Apr 02 2019 14:30:02.823 CDT

Splunk's never had issues extracting this field for anything else. I made the changes you provided above, but the dashboard panel is still blank.

Re: How to use a scheduled report as base for dashboard?

grittonc — Tue, 16 Apr 2019 13:31:23 GMT

You have to convert Time into epoch time:

          <search base="My_Auth_Log_Report">
            <query>fields * |search duser=$username$ evtcode=$event_code$
                | eval epoch_time=strptime(Time, "%b %d %Y %X.%3Q %Z")
                | search epoch_time&gt;=$time.earliest_epoch$ AND epoch_time&lt;$time.latest_epoch$
             </query>
         </search>

Another thing you can try when your dashboard is blank is to try "Open in Search" (lower right corner of dashboard panel) to see how tokens are being passed to your search.

Re: How to use a scheduled report as base for dashboard?

grittonc — Wed, 30 Sep 2020 00:06:09 GMT

Is this the whole XML? I don't see where the base search My_Auth_Log_Report is defined.

Re: How to use a scheduled report as base for dashboard?

reynlds — Tue, 16 Apr 2019 17:13:34 GMT

the base search for the report is very basic:

index=auth_idx source="*auth-log*"

Re: How to use a scheduled report as base for dashboard?

niketn — Tue, 16 Apr 2019 17:30:04 GMT

@reynlds if someone does not have access to the data in the index, why would you want to expose it to such users? You can use scheduled search with collect command to move the data from current index to a new one to which user has access and then create dashboard for the same. Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Collect#1._Put_.22download.22_events_into_an_index_named_.22download_count.22

Re: How to use a scheduled report as base for dashboard?

reynlds — Tue, 16 Apr 2019 18:37:48 GMT

It was suggested by our Splunk admin(s) that this is the best way to do it, since they don't want to expose the full index (all sources) to the end users. If there is a way to setup a role with only the access needed for the source, I could bypass this whole scheduled report setup, but they've said that is not possible in our current environment. Using a scheduled report as a base for the search in the dashboard was all that was offered.

I read through the link you provided, but am unclear as to whether the collect index is created on-the-fly, or if I'll need them to create it for me.

Re: How to use a scheduled report as base for dashboard?

reynlds — Tue, 16 Apr 2019 20:23:04 GMT

Tried the "open in search" and it did nothing. I also tried the inspector and it opened a window that said "unknown SID". Looks like it's not even progressing to the point of starting.

Re: How to use a scheduled report as base for dashboard?

grittonc — Wed, 30 Sep 2020 00:10:45 GMT

Now I think we're getting to the root of the problem. As it is, the dashboard is looking to find a base search called My_Auth_Log_Report and is not finding one. Base searches (post-process searches) and scheduled searches are two different things. Read more about base searches here: https://answers.splunk.com/answers/239159/multiple-base-searches-in-a-dasboard-with-post-pro.html

For a scheduled search, you need to use the loadjob command to load the results of the scheduled search. Assuming that My_Auth_Log_Report is the name of your saved search, do you get anything if you replace yourusername and appname and run this command in a search window? | loadjob savedsearch="yourusername:appname:My_Auth_Log_Report"?

Re: How to use a scheduled report as base for dashboard?

reynlds — Wed, 17 Apr 2019 13:16:13 GMT

Running it alone in a search window shows all events (Huzzah!!!). However, they are in the "stats" tab and not the "events" tab. In the dashboard, still nothing...here is a snippet detailing the Search stanza (FYI...the app is called "search"):

<search>
  <query>| loadjob savedsearch="MyUsername:search:My_Auth_Log_Query" | fields * | search duser=$username$ evtcode=$event_code$
         | eval epoch_time=strptime(Time, "%b %d %Y %X.%3Q %Z")
         | search epoch_time&gt;=$time.earliest_epoch$ AND epoch_time&lt;$time.latest_epoch$
  </query>
</search>

Re: How to use a scheduled report as base for dashboard?

reynlds — Wed, 17 Apr 2019 13:35:05 GMT

Update: looks like the actual event is being used in the _raw field. Just need to split the _raw out into the 17 individual fields that are separated by a pipe. Pretty sure I'll have to use "rex" in some fashion.

Re: How to use a scheduled report as base for dashboard?

grittonc — Wed, 17 Apr 2019 13:53:52 GMT

Can you add a table statement to index=auth_idx source="*auth-log*" to indicate the fields you need in your dashboard?

Do you get results in the dashboard if you remove the inputs like username, eventcode, and time?

Re: How to use a scheduled report as base for dashboard?

grittonc — Wed, 17 Apr 2019 13:54:47 GMT

@niketnilay , isn't access to a source something that can be configured in a role under restricted search terms?

Re: How to use a scheduled report as base for dashboard?

reynlds — Wed, 17 Apr 2019 16:58:36 GMT

I don't think it would make a difference as those that will be using the dashboard won't have access to the index. Also I'm trying to get this built in search instead of fumbling in the dashboard all the time. Just need to break out the individual fields that are in the _raw string and separated by pipe delimiters.

Re: How to use a scheduled report as base for dashboard?

reynlds — Wed, 17 Apr 2019 19:20:07 GMT

Each source contains a certain level of sensitive data. The end users only need access to one source, but Splunk admins assure me that the only way to get access to that source with a traditional method (search directly in the dashboard/panel) is to give them read access to the entire index. Our plan was to use a scheduled search to limit the amount of data that is "searchable" and make that report/dashboard available to a limit number.

Re: How to use a scheduled report as base for dashboard?

niketn — Thu, 18 Apr 2019 00:30:43 GMT

[Updated] Removed answer for using index for access management as the approach suggested by Splunk Admin is also correct. @martin_mueller confirmed the same.

You can create a saved search and give read only access to the user. For the following example Report name is: "Your Report Name Goes Here"
Then use the dashboard as following:

<dashboard>
  <label>Your Dashboard</label>
  <row>
    <panel>
      <event>
        <title>Pull Results from Report</title>
        <search ref="Your Report Name Goes Here"></search>
        <option name="list.drilldown">none</option>
      </event>
    </panel>
  </row>
</dashboard>