Need to speed up (last 15 day) Tstats on sourcetypes / host

[spluzer]

Hey Splunksters,
Noob here.
The query below is for a dashboard panel that lets me toggle to see (for example) sourcetypes/ hosts that haven't sent data in 24 hours, but not more than 48 hours. It works, but its super slow. Any thoughts on the easiest way to speed this up? Perhaps a saved search? / loadjob? lookup? of so, what would the query for that look like???.. Any help is much appreciated!

<form>
  <label>Missing Data Dashboard</label>
  <fieldset autoRun="true" submitButton="false">
    <input type="dropdown" token="simple">
      <label>Select Period</label>
      <choice value="1">Missing 1 day</choice>
      <choice value="2">Missing 2 days</choice>
      <choice value="3">Missing 3 Days</choice>
      <choice value="4">Missing 4 Days</choice>
      <choice value="5">MIssing 5 Days</choice>
      <choice value="6">MIssing 6 Days</choice>
      <choice value="7">MIssing 7 Days</choice>
      <choice value="8">Missing 8 Days</choice>
      <choice value="9">Missing 9 Days</choice>
      <default>1</default>
    </input>
    <input type="dropdown" token="simple_2">
      <label>Select Cutoff</label>
      <choice value="2">Not > 2 Days</choice>
      <choice value="3">Not > 3 Days</choice>
      <choice value="4">Not > 4 Days</choice>
      <choice value="5">Not > 5 Days</choice>
      <choice value="6">Not > 6 Days</choice>
      <choice value="7">Not > 7 Days</choice>
      <choice value="8">Not > 8 Days</choice>
      <choice value="9">Not > 9 Days</choice>
      <default>2</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Select Day for Silent Hosts / Sourcetypes  (Last 15 Days)</title>
      <table>
        <search>
          <query>| tstats latest(_indextime) as lt by host sourcetype 
  | eval timeLastSeenCheck=relative_time(now(), "-$simple$d@d"), newer_than=relative_time(now(), "-$simple_2$d@d")

   | eventstats count(host) as tots_hosts by sourcetype
   | eventstats dc(sourcetype) as tots_st by host 
  | where lt < timeLastSeenCheck and lt>newer_than
   | eventstats count(host) as ghost_hosts  by sourcetype
   | eval percent_ghost_host = (ghost_hosts / tots_hosts) * 100
   | eventstats dc(sourcetype) as ghost_st by host
   | eval percent_ghost_st = (ghost_st / tots_st) * 100
  | convert ctime(timeLastSeenCheck) as Time_LAst_Seen_Check timeformat="%Y/%m/%d %H:%M"
  | convert ctime(lt) as "Cutoff" timeformat="%Y/%m/%d %H:%M"
  | dedup sourcetype

   | rename host as Host sourcetype as Sourcetype  percent_ghost_host as "This Hosts Percentage Missing to Whole" percent_ghost_st as "This Sourcetypes Percentage Missing to Whole" tots_hosts as "Total Count of Hosts by Sourcetype" tots_st as "Total Count of Sourcetypes by Host"  ghost_hosts as "Count of Missing Hosts" ghost_st as "Count of Missing Sourcetypes" Time_LAst_Seen_Check as "Day Last Seen"
| table Host Sourcetype "Total Count of Hosts by Sourcetype" "Count of Missing Hosts" "This Hosts Percentage Missing to Whole" "Total Count of Sourcetypes by Host" "Count of Missing Sourcetypes" "This Sourcetypes Percentage Missing to Whole" "Day Last Seen" "Cutoff"</query>
          <earliest>-15d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>

[woodcock]

You should run this once over All time, output this to a lookup file, and then true it up with a scheduled search that runs every hour for the last 2 hours that updates the same lookup file which tells you the first and last time that each host had an event for each sourcetype. The have the dashboard pull the overall details from the lookup file and do your other logic from there. Look at the Missing Forwarder app or the missing hosts search in Splunk Security Essentials app or the same kind of thing in the Meta woot! app or even the missing hosts feature on the Monitoring Console.

[spluzer]

Thanks Gents!

[dflodstrom]

Be sure to add "summariesonly=true" to your tstats search, that should speed things up considerably.

[spluzer · Accepted solution]

Ended up creating a saved search :

tstats latest(_indextime) as lt by host sourcetype

runs every six hours (30 day grab)

Then dashboard panel uses this query:

| loadjob savedsearch=admin:search;my_saved_search
| eval timeLastSeenCheck=relative_time(now(), "-$simple$d@d"), newer_than=relative_time(now(), "-$simple_2$d@d")

    | eventstats count(host) as tots_hosts by sourcetype
    | eventstats dc(sourcetype) as tots_st by host 
   | where lt < timeLastSeenCheck and lt>newer_than
    | eventstats count(host) as ghost_hosts  by sourcetype
    | eval percent_ghost_host = (ghost_hosts / tots_hosts) * 100
    | eventstats dc(sourcetype) as ghost_st by host
    | eval percent_ghost_st = (ghost_st / tots_st) * 100
   | convert ctime(timeLastSeenCheck) as Time_LAst_Seen_Check timeformat="%Y/%m/%d %H:%M"
   | convert ctime(lt) as "Cutoff" timeformat="%Y/%m/%d %H:%M"
   | dedup sourcetype

    | rename host as Host sourcetype as Sourcetype  percent_ghost_host as "This Hosts Percentage Missing to Whole" percent_ghost_st as "This Sourcetypes Percentage Missing to Whole" tots_hosts as "Total Count of Hosts by Sourcetype" tots_st as "Total Count of Sourcetypes by Host"  ghost_hosts as "Count of Missing Hosts" ghost_st as "Count of Missing Sourcetypes" Time_LAst_Seen_Check as "Day Last Seen"
 | table Host Sourcetype "Total Count of Hosts by Sourcetype" "Count of Missing Hosts" "This Hosts Percentage Missing to Whole" "Total Count of Sourcetypes by Host" "Count of Missing Sourcetypes" "This Sourcetypes Percentage Missing to Whole" "Day Last Seen" "Cutoff"

[woodcock]

You should create a summary index for this.

[spluzer]

Thanks. To clarify, would I just take the first line | tstats latest(_indextime) as lt by host and sourcetpye) ...run it for 30 days (for example) and save THAT as the summary index?

[dflodstrom]

You just need to schedule the search to do that and it will access the latest results using the loadjob command... make sure you | tstats sumarriesonly=true

🙂