Dashboards & Visualizations

How to create a dashboard that sends an aggregated report to my email?

nick405060
Motivator

Hi guys,

I'd like to create a dashboard where you can enter a user and a timerange and then have a report sent to a specified email address of all of their activity. The impetus for wanting to do this comes from a dashboard I created that runs and displays this report IN the dashboard, but the dashboard became too complicated, and having over 10 different base searches was simply too much. It also took too long for everything to run, I don't want my users to have to wait multiple minutes for this massive dashboard to finish running.

So, I'd like to create a much more simple dashboard that sends this to their email. How can I create a dashboard that sends you a "report is running" message, aggregates multiple reports (passing tokens all the way down to each), and sends to your email?

0 Karma
1 Solution

nick405060
Motivator

Here's how.

SimpleXML:

<form script="button_submit_user_activity.js">
  <label>Generate User Activity Report</label>
  <description>Badge: LOCATION1, LOCATION2, LOCATION3 only; VPN: Group/TunnelGroup only; Exchange: only received messages of under 2 recipients</description>


  <row>
    <panel>
      <html depends="$hide$">
        <style>
          #submit_button{
            width:80px !important;
          }
          #submit_button div[data-component="splunk-core:/splunkjs/mvc/components/LinkList"]{
            width:80px !important;
          }
          #submit_button  button{
            padding: 6px 15px !important;
            border-radius: 3px !important;
            font-weight: 500 !important;
            background-color: #5cc05c !important;
            border: transparent !important;
            color: #fff !important;
          }
          #submit_button  button:hover{
            background-color: #40a540 !important;
            border-color: transparent !important;
          }
        </style>
      </html>
      <input type="time">
        <label>Time range:</label>
        <default>
          <earliest>@d</earliest>
          <latest>now</latest>
        </default>
        <change>
          <eval token="earliest_epoch_onChange">case(isnum($earliest$), $earliest$, $earliest$=="now", time(), $earliest$="", 0, true(), relative_time(time(), $earliest$))</eval>
          <eval token="latest_epoch_onChange">case(isnum($latest$), $latest$, $latest$=="now", time(), true(), relative_time(time(), $latest$))</eval>
        </change>
      </input>
      <input type="text" token="network_id_onChange" id="search_textbox">
        <label>NetworkID</label>
        <default></default>
      </input>
      <input type="multiselect" token="sourcetypes_onChange" id="sourcetypes_multiselect">
        <label>Select sources:</label>
        <choice value="accesscontrol">Badge</choice>
        <choice value="VPN">VPN</choice>
        <choice value="MSExchange:2013:MessageTracking">Exchange</choice>
        <choice value="duo">Duo</choice>
        <choice value="CENSORED3">CENSORED3</choice>
        <initialValue>Badge,VPN,Exchange,Duo,CENSORED3</initialValue>
        <delimiter>,</delimiter>
      </input>
      <input type="text" token="send_to_onChange" id="email_textbox">
        <label>Send report to (email addr):</label>
        <default></default>
      </input>
      <input type="link" id="submit_button">
        <label></label>
        <choice value="submit">Submit</choice>
      </input>
    </panel>
  </row>
  <search>
    <query>
index=CENSORED2 source=CENSORED2:vwJobClassGroup NetworkId="$network_id$" | rex field=LastFirst "(?&lt;last&gt;\S+?), ?(?&lt;first&gt;\S+)" | eval Name=first." ".last | table Email Name last
    </query>
    <earliest>-24h</earliest>
    <latest>now</latest>
    <done>
      <set token="email">$result.Email$</set>
      <set token="name">$result.Name$</set>
      <set token="last">$result.last$</set>
    </done>
  </search>

  <row>
    <panel depends="$alwaysHideCSS$">
      <html>
        <style>          
          #timepicker div[data-view="splunkjs/mvc/timerangeview"]{
            width: 250px !important;
            margin-right: auto !important;
          }
          #search_textbox .splunk-textinput
          {
            width: 250px !important;
          }
          #sourcetypes_multiselect div[data-component="splunk-core:/splunkjs/mvc/components/MultiDropdown"]{
            width: 290px !important;
          }
          #sourcetypes_multiselect div[data-view="splunkjs/mvc/multidropdownview"]{
            width: 290px !important;
            margin-right: auto !important;
          }
          #email_textbox .splunk-textinput
          {
            width: 250px !important;
          }
          .fieldset .input{
            width:auto !important;
          } 
        </style>
      </html>
    </panel>
  </row>


  <search>
    <query>
| makeresults 
| eval badge_toggle=if(match("$sourcetypes$","(?i)accesscontrol"),"user_activity_badge","junk_report") 
| eval exchange_toggle=if(match("$sourcetypes$","(?i)exchange"),"user_activity_exchange","junk_report") 
| eval vpn_toggle=if(match("$sourcetypes$","(?i)vpn"),"user_activity_vpn","junk_report")
| eval duo_toggle=if(match("$sourcetypes$","(?i)duo"),"user_activity_duo","junk_report")
| eval CENSORED3_toggle=if(match("$sourcetypes$","(?i)CENSORED3"),"user_activity_CENSORED3","junk_report")
| map maxsearches=10000 search="| savedsearch user_activity_master earliest_epoch=\"$earliest_epoch$\" latest_epoch=\"$latest_epoch$\" network_id=\"$network_id$\" email=\"$email$\" name=\"$name$\" last=\"$last$\" send_to=\"$send_to$\" badge_toggle=\"$$badge_toggle$$\" exchange_toggle=\"$$exchange_toggle$$\" vpn_toggle=\"$$vpn_toggle$$\" duo_toggle=\"$$duo_toggle$$\" CENSORED3_toggle=\"$$CENSORED3_toggle$$\""
    </query>
  </search>


  <row>
    <panel>
      <table>
        <search>
          <query>
| makeresults | eval trigger="$submit_trigger$" | eval message="Your report is running and will be sent to your email. You may close out of this window" | table message
          </query>
          <earliest>1</earliest>
          <latest>2</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>


</form>

Master report (user_activity_master):

| makeresults | multireport
[| makeresults | eval type=""]
[| makeresults | eval A="Badge",B="Badge",C="Badge",D="Badge",E="Badge",F="Badge",G="Badge",H="Badge",I="Badge",J="Badge",K="Badge",L="Badge",M="Badge",N="Badge",O="Badge",P="Badge",Q="Badge",R="Badge",pst="Badge",type="Badge"]
[| makeresults | eval A="reader" | eval type=""]
[| savedsearch "$badge_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" last="$last$"]
[| makeresults | eval type=""]
[| makeresults | eval A="VPN",B="VPN",C="VPN",D="VPN",E="VPN",F="VPN",G="VPN",H="VPN",I="VPN",J="VPN",K="VPN",L="VPN",M="VPN",N="VPN",O="VPN",P="VPN",Q="VPN",R="VPN",pst="VPN",type="VPN"]
[| makeresults | eval A="ip",B="ip_resolved",C="message",type=""]
[| savedsearch "$vpn_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$"]
[| makeresults | eval type=""]
[| makeresults | eval A="Exchange",B="Exchange",C="Exchange",D="Exchange",E="Exchange",F="Exchange",G="Exchange",H="Exchange",I="Exchange",J="Exchange",K="Exchange",L="Exchange",M="Exchange",N="Exchange",O="Exchange",P="Exchange",Q="Exchange",R="Exchange",pst="Exchange",type="Exchange"]
[| makeresults | eval A="sender",B="message_subject",C="recipient",type=""]
[| savedsearch "$exchange_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$" email="$email$"]
[| makeresults | eval type=""]
[| makeresults | eval A="Duo",B="Duo",C="Duo",D="Duo",E="Duo",F="Duo",G="Duo",H="Duo",I="Duo",J="Duo",K="Duo",L="Duo",M="Duo",N="Duo",O="Duo",P="Duo",Q="Duo",R="Duo",pst="Duo",type="Duo"]
[| makeresults | eval A="username",B="result",C="integration",D="location.city",E="location.country",F="location.state",G="action",H="actionlabel",I="context",J="extracted_eventtype",K="object",L="phone",M="duo_type",N="factor",O="device",P="model",Q="os_family",R="os_version",type=""]
[| savedsearch "$duo_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$" email="$email$" name="$name$"]
[| makeresults | eval type=""]
[| makeresults | eval A="CENSORED3",B="CENSORED3",C="CENSORED3",D="CENSORED3",E="CENSORED3",F="CENSORED3",G="CENSORED3",H="CENSORED3",I="CENSORED3",J="CENSORED3",K="CENSORED3",L="CENSORED3",M="CENSORED3",N="CENSORED3",O="CENSORED3",P="CENSORED3",Q="CENSORED3",R="CENSORED3",pst="CENSORED3",type="CENSORED3"]
[| makeresults | eval A="creator",B="modder",C="location",D="attendees",E="start",F="end",G="last_mod",H="created",I="pending_appr",J="timezone",K="desc",L="ext_desc",M="all_day",N="deleted",O="catering",P="hoteling",Q="pending_approver",type=""]
[| savedsearch "$CENSORED3_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$"]
| where type!="junk" | fields - _time
| sendemail to="$send_to$" server=smtp.CENSORED1.com subject="Here is your requested Splunk report." message="Results:" sendresults=true inline=true sendcsv=true

Example subreport (user_activity_exchange):

| makeresults | map maxsearches=10000 search="search index=msexchange sender=\"$email$\" OR recipient=\"$email$\" event_id=\"AGENTINFO\" earliest=\"$earliest_epoch$\" latest=\"$latest_epoch$\" | eval recipient_size=mvcount(recipient) | where recipient_size < 3 OR sender=\"$email$\" | sort 0 - _time | eval pst=strftime(_time,\"%m-%d-%Y %H:%M:%S\") | table sender message_subject recipient pst" 
| rename sender AS "A",message_subject AS "B",recipient AS "C" | eval type="exchange"

button_submit_user_activity.js:

 require([
     'jquery',
     'splunkjs/mvc',
     'splunkjs/mvc/simplexml/ready!'
 ], function($,mvc){
     var submittedTokens = mvc.Components.get("submitted");
     $("#submit_button").click(function(){
         submittedTokens.set("submit_trigger", ""+Math.random());
         submittedTokens.set("earliest_epoch",submittedTokens.get("earliest_epoch_onChange"));
         submittedTokens.set("latest_epoch",submittedTokens.get("latest_epoch_onChange"));
         submittedTokens.set("network_id",submittedTokens.get("network_id_onChange"));
         submittedTokens.set("sourcetypes",submittedTokens.get("sourcetypes_onChange"));
         submittedTokens.set("send_to",submittedTokens.get("send_to_onChange"));
     });
 });

The reason that you need to use map in the subreports is because you can't just do index=msexchange sender="$email$" event_id="AGENTINFO" earliest="$earliest_epoch$" latest="$latest_epoch$" | eval type="exchange" | table message_subject recipient _time. If you do, while sender will be fed correctly, earliest and latest won't be fed correctly since they are special keywords, and the subreport will not run. You can't alternately fix this by evaling my_temp_eval="$earliest_epoch$" and then appending the search, since you can't pass tokens to an append, so you have to use map instead (in the case of map, you don't even need to do the my_temp_eval first).

You need to map in the SimpleXML because | savedsearch must be the first command in the search and you need to toggle the correct toggle tokens beforehand.

View solution in original post

0 Karma

nick405060
Motivator

Here's how.

SimpleXML:

<form script="button_submit_user_activity.js">
  <label>Generate User Activity Report</label>
  <description>Badge: LOCATION1, LOCATION2, LOCATION3 only; VPN: Group/TunnelGroup only; Exchange: only received messages of under 2 recipients</description>


  <row>
    <panel>
      <html depends="$hide$">
        <style>
          #submit_button{
            width:80px !important;
          }
          #submit_button div[data-component="splunk-core:/splunkjs/mvc/components/LinkList"]{
            width:80px !important;
          }
          #submit_button  button{
            padding: 6px 15px !important;
            border-radius: 3px !important;
            font-weight: 500 !important;
            background-color: #5cc05c !important;
            border: transparent !important;
            color: #fff !important;
          }
          #submit_button  button:hover{
            background-color: #40a540 !important;
            border-color: transparent !important;
          }
        </style>
      </html>
      <input type="time">
        <label>Time range:</label>
        <default>
          <earliest>@d</earliest>
          <latest>now</latest>
        </default>
        <change>
          <eval token="earliest_epoch_onChange">case(isnum($earliest$), $earliest$, $earliest$=="now", time(), $earliest$="", 0, true(), relative_time(time(), $earliest$))</eval>
          <eval token="latest_epoch_onChange">case(isnum($latest$), $latest$, $latest$=="now", time(), true(), relative_time(time(), $latest$))</eval>
        </change>
      </input>
      <input type="text" token="network_id_onChange" id="search_textbox">
        <label>NetworkID</label>
        <default></default>
      </input>
      <input type="multiselect" token="sourcetypes_onChange" id="sourcetypes_multiselect">
        <label>Select sources:</label>
        <choice value="accesscontrol">Badge</choice>
        <choice value="VPN">VPN</choice>
        <choice value="MSExchange:2013:MessageTracking">Exchange</choice>
        <choice value="duo">Duo</choice>
        <choice value="CENSORED3">CENSORED3</choice>
        <initialValue>Badge,VPN,Exchange,Duo,CENSORED3</initialValue>
        <delimiter>,</delimiter>
      </input>
      <input type="text" token="send_to_onChange" id="email_textbox">
        <label>Send report to (email addr):</label>
        <default></default>
      </input>
      <input type="link" id="submit_button">
        <label></label>
        <choice value="submit">Submit</choice>
      </input>
    </panel>
  </row>
  <search>
    <query>
index=CENSORED2 source=CENSORED2:vwJobClassGroup NetworkId="$network_id$" | rex field=LastFirst "(?&lt;last&gt;\S+?), ?(?&lt;first&gt;\S+)" | eval Name=first." ".last | table Email Name last
    </query>
    <earliest>-24h</earliest>
    <latest>now</latest>
    <done>
      <set token="email">$result.Email$</set>
      <set token="name">$result.Name$</set>
      <set token="last">$result.last$</set>
    </done>
  </search>

  <row>
    <panel depends="$alwaysHideCSS$">
      <html>
        <style>          
          #timepicker div[data-view="splunkjs/mvc/timerangeview"]{
            width: 250px !important;
            margin-right: auto !important;
          }
          #search_textbox .splunk-textinput
          {
            width: 250px !important;
          }
          #sourcetypes_multiselect div[data-component="splunk-core:/splunkjs/mvc/components/MultiDropdown"]{
            width: 290px !important;
          }
          #sourcetypes_multiselect div[data-view="splunkjs/mvc/multidropdownview"]{
            width: 290px !important;
            margin-right: auto !important;
          }
          #email_textbox .splunk-textinput
          {
            width: 250px !important;
          }
          .fieldset .input{
            width:auto !important;
          } 
        </style>
      </html>
    </panel>
  </row>


  <search>
    <query>
| makeresults 
| eval badge_toggle=if(match("$sourcetypes$","(?i)accesscontrol"),"user_activity_badge","junk_report") 
| eval exchange_toggle=if(match("$sourcetypes$","(?i)exchange"),"user_activity_exchange","junk_report") 
| eval vpn_toggle=if(match("$sourcetypes$","(?i)vpn"),"user_activity_vpn","junk_report")
| eval duo_toggle=if(match("$sourcetypes$","(?i)duo"),"user_activity_duo","junk_report")
| eval CENSORED3_toggle=if(match("$sourcetypes$","(?i)CENSORED3"),"user_activity_CENSORED3","junk_report")
| map maxsearches=10000 search="| savedsearch user_activity_master earliest_epoch=\"$earliest_epoch$\" latest_epoch=\"$latest_epoch$\" network_id=\"$network_id$\" email=\"$email$\" name=\"$name$\" last=\"$last$\" send_to=\"$send_to$\" badge_toggle=\"$$badge_toggle$$\" exchange_toggle=\"$$exchange_toggle$$\" vpn_toggle=\"$$vpn_toggle$$\" duo_toggle=\"$$duo_toggle$$\" CENSORED3_toggle=\"$$CENSORED3_toggle$$\""
    </query>
  </search>


  <row>
    <panel>
      <table>
        <search>
          <query>
| makeresults | eval trigger="$submit_trigger$" | eval message="Your report is running and will be sent to your email. You may close out of this window" | table message
          </query>
          <earliest>1</earliest>
          <latest>2</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>


</form>

Master report (user_activity_master):

| makeresults | multireport
[| makeresults | eval type=""]
[| makeresults | eval A="Badge",B="Badge",C="Badge",D="Badge",E="Badge",F="Badge",G="Badge",H="Badge",I="Badge",J="Badge",K="Badge",L="Badge",M="Badge",N="Badge",O="Badge",P="Badge",Q="Badge",R="Badge",pst="Badge",type="Badge"]
[| makeresults | eval A="reader" | eval type=""]
[| savedsearch "$badge_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" last="$last$"]
[| makeresults | eval type=""]
[| makeresults | eval A="VPN",B="VPN",C="VPN",D="VPN",E="VPN",F="VPN",G="VPN",H="VPN",I="VPN",J="VPN",K="VPN",L="VPN",M="VPN",N="VPN",O="VPN",P="VPN",Q="VPN",R="VPN",pst="VPN",type="VPN"]
[| makeresults | eval A="ip",B="ip_resolved",C="message",type=""]
[| savedsearch "$vpn_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$"]
[| makeresults | eval type=""]
[| makeresults | eval A="Exchange",B="Exchange",C="Exchange",D="Exchange",E="Exchange",F="Exchange",G="Exchange",H="Exchange",I="Exchange",J="Exchange",K="Exchange",L="Exchange",M="Exchange",N="Exchange",O="Exchange",P="Exchange",Q="Exchange",R="Exchange",pst="Exchange",type="Exchange"]
[| makeresults | eval A="sender",B="message_subject",C="recipient",type=""]
[| savedsearch "$exchange_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$" email="$email$"]
[| makeresults | eval type=""]
[| makeresults | eval A="Duo",B="Duo",C="Duo",D="Duo",E="Duo",F="Duo",G="Duo",H="Duo",I="Duo",J="Duo",K="Duo",L="Duo",M="Duo",N="Duo",O="Duo",P="Duo",Q="Duo",R="Duo",pst="Duo",type="Duo"]
[| makeresults | eval A="username",B="result",C="integration",D="location.city",E="location.country",F="location.state",G="action",H="actionlabel",I="context",J="extracted_eventtype",K="object",L="phone",M="duo_type",N="factor",O="device",P="model",Q="os_family",R="os_version",type=""]
[| savedsearch "$duo_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$" email="$email$" name="$name$"]
[| makeresults | eval type=""]
[| makeresults | eval A="CENSORED3",B="CENSORED3",C="CENSORED3",D="CENSORED3",E="CENSORED3",F="CENSORED3",G="CENSORED3",H="CENSORED3",I="CENSORED3",J="CENSORED3",K="CENSORED3",L="CENSORED3",M="CENSORED3",N="CENSORED3",O="CENSORED3",P="CENSORED3",Q="CENSORED3",R="CENSORED3",pst="CENSORED3",type="CENSORED3"]
[| makeresults | eval A="creator",B="modder",C="location",D="attendees",E="start",F="end",G="last_mod",H="created",I="pending_appr",J="timezone",K="desc",L="ext_desc",M="all_day",N="deleted",O="catering",P="hoteling",Q="pending_approver",type=""]
[| savedsearch "$CENSORED3_toggle$" earliest_epoch="$earliest_epoch$" latest_epoch="$latest_epoch$" network_id="$network_id$"]
| where type!="junk" | fields - _time
| sendemail to="$send_to$" server=smtp.CENSORED1.com subject="Here is your requested Splunk report." message="Results:" sendresults=true inline=true sendcsv=true

Example subreport (user_activity_exchange):

| makeresults | map maxsearches=10000 search="search index=msexchange sender=\"$email$\" OR recipient=\"$email$\" event_id=\"AGENTINFO\" earliest=\"$earliest_epoch$\" latest=\"$latest_epoch$\" | eval recipient_size=mvcount(recipient) | where recipient_size < 3 OR sender=\"$email$\" | sort 0 - _time | eval pst=strftime(_time,\"%m-%d-%Y %H:%M:%S\") | table sender message_subject recipient pst" 
| rename sender AS "A",message_subject AS "B",recipient AS "C" | eval type="exchange"

button_submit_user_activity.js:

 require([
     'jquery',
     'splunkjs/mvc',
     'splunkjs/mvc/simplexml/ready!'
 ], function($,mvc){
     var submittedTokens = mvc.Components.get("submitted");
     $("#submit_button").click(function(){
         submittedTokens.set("submit_trigger", ""+Math.random());
         submittedTokens.set("earliest_epoch",submittedTokens.get("earliest_epoch_onChange"));
         submittedTokens.set("latest_epoch",submittedTokens.get("latest_epoch_onChange"));
         submittedTokens.set("network_id",submittedTokens.get("network_id_onChange"));
         submittedTokens.set("sourcetypes",submittedTokens.get("sourcetypes_onChange"));
         submittedTokens.set("send_to",submittedTokens.get("send_to_onChange"));
     });
 });

The reason that you need to use map in the subreports is because you can't just do index=msexchange sender="$email$" event_id="AGENTINFO" earliest="$earliest_epoch$" latest="$latest_epoch$" | eval type="exchange" | table message_subject recipient _time. If you do, while sender will be fed correctly, earliest and latest won't be fed correctly since they are special keywords, and the subreport will not run. You can't alternately fix this by evaling my_temp_eval="$earliest_epoch$" and then appending the search, since you can't pass tokens to an append, so you have to use map instead (in the case of map, you don't even need to do the my_temp_eval first).

You need to map in the SimpleXML because | savedsearch must be the first command in the search and you need to toggle the correct toggle tokens beforehand.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...