Splunk Search

how to display a line in a table panel even if there is no results

jip31
Motivator

hi

I need that the stats command below display a line with 0 if there is no results
How can I do please??
index="xx" sourcetype="yy" key_path="HKLM\software\microsoft\windows nt\" OR key_path="HKLM\software\microsoft\windows nt\currentversion"

| stats dc(host) as "Number of hosts" by sourcetype key_path

0 Karma
1 Solution

to4kawa
Ultra Champion
<form>
  <label>test</label>
  <fieldset submitButton="false">
    <input type="text" token="field1">
      <label>test</label>
    </input>
  </fieldset>
  <row>
    <panel depends="$show_table$">
      <table>
        <search id="your query">
          <progress>
            <condition match="'job.resultCount' > 0">
              <set token="show_table">true</set>
              <unset token="show_table2"></unset>
            </condition>
            <condition>
              <unset token="show_table"></unset>
              <set token="show_table2">true</set>
            </condition>
          </progress>
          <query>index=_internal $field1$ | stats count</query>
          <earliest>-5s</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
    <panel depends="$show_table2$">
      <table>
        <search id="nothing">
          <query>| stats count </query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
      </table>
    </panel>
  </row>
</form>

This is an example dashboard.
If there is a search result, the result is output.
I would like you to write the necessary query.

View solution in original post

0 Karma

to4kawa
Ultra Champion
<form>
  <label>test</label>
  <fieldset submitButton="false">
    <input type="text" token="field1">
      <label>test</label>
    </input>
  </fieldset>
  <row>
    <panel depends="$show_table$">
      <table>
        <search id="your query">
          <progress>
            <condition match="'job.resultCount' > 0">
              <set token="show_table">true</set>
              <unset token="show_table2"></unset>
            </condition>
            <condition>
              <unset token="show_table"></unset>
              <set token="show_table2">true</set>
            </condition>
          </progress>
          <query>index=_internal $field1$ | stats count</query>
          <earliest>-5s</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
    <panel depends="$show_table2$">
      <table>
        <search id="nothing">
          <query>| stats count </query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
      </table>
    </panel>
  </row>
</form>

This is an example dashboard.
If there is a search result, the result is output.
I would like you to write the necessary query.

0 Karma

jip31
Motivator

sorry but it doesnt help much more...

0 Karma

niketn
Legend

[UPDATED QUERY] as per details provided for the key_path also to be defaulted to 0 in case they are not present.
@jip31, as per your query there are only two key_path. If so you can use the following query. If not you will have to prepare a list of key_paths available in a lookup and use that to default the values in case any key_path is not present.

index="xx" sourcetype="yy" key_path="HKLM\\software\\microsoft\\windows nt\\" OR key_path="HKLM\\software\\microsoft\\windows nt\\currentversion" 
 | stats dc(host) as "Number of hosts" by sourcetype key_path 
 | append 
     [| makeresults 
     | fields - _time 
     | eval index="xx",sourcetype="yy", key_path="HKLM\\software\\microsoft\\windows nt\\,HKLM\\software\\microsoft\\windows nt\\currentversion" ,"Number of hosts"=0
     | makemv key_path delim=","
     | mvexpand key_path] 
 | dedup index sourcetype key_path

@jip31, but you are fixing the sourcetype in your query to "yy" i.e. index="xx" sourcetype="yy". Is there only one sourcetype? If so, do you want to show single row with sourcetype=-yy and count=0 when the events from the sourcetype does not exist, you can try the following:

index="xx" sourcetype="yy" key_path="HKLM\\software\\microsoft\\windows nt\\" OR key_path="HKLM\\software\\microsoft\\windows nt\\currentversion" 
| stats dc(host) as "Number of hosts" by sourcetype key_path 
| append 
    [| makeresults 
    | fields - _time 
    | eval index="xx",sourcetype="yy", key_path="N/A" ,"Number of hosts"=0] 
| dedup index sourcetype
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jip31
Motivator

hi renjith
I have only 2 key path, 2 with a count > 0 and 2 key path with a count = 0
I tried to adapt your query but I have a count = 0 for the 4 key patch
here is what I have done

(index=toto) sourcetype="WinRegistry" key_path="HKLM\\ww" OR key_path="HKLM\\xx" OR key_path="HKLM\\yy" OR key_path="HKLM\\zz" 
  | stats dc(host) as "Number of hosts" by sourcetype key_path 
  | append 
      [| makeresults 
      | fields - _time 
      | eval index="ai-wkst-windows-fr",sourcetype="WinRegistry", key_path="HKLM\\ww\\,HKLM\\xx,HKLM\\yy,HKLM\\zz","Number of hosts"=0
      | makemv key_path delim=","
      | mvexpand key_path] 
  | dedup index sourcetype key_path
0 Karma

jip31
Motivator

hi, have you an idea of what is wrong please?

0 Karma

jip31
Motivator

is anybody for helping me please??

0 Karma

jip31
Motivator

hi renjith
yes there is just one sourcetype
I need the fields displayed like this :

"sourcetype" "key_path" "number of hosts"
toto 1 24
toto 2 18
toto 3 0
toto 4 0

As you can see I need to display all the key_path even if the number of hosts is equal to 0

0 Karma

techiesid
SplunkTrust
SplunkTrust

If I understood your question correctly simplest solution could be below,

index="xx" sourcetype="yy" key_path="HKLM\\software\\microsoft\\windows nt\\" OR key_path="HKLM\\software\\microsoft\\windows nt\\currentversion" 
| stats dc(host) as "Number of hosts" by sourcetype key_path 
| eval "Number of hosts" = if("Number of hosts" =0,"0-------------","Number of hosts" )

If you need proper lines in that case you may need to go for custom table renderer using js.

http://dev.splunk.com/view/webframework-developapps/SP-CAAAEUY

Sid

0 Karma

whrg
Motivator

"Number of hosts" inside the if statement refers to the string "Number of hosts" instead of the field. I suggest to name the field Number_of_hosts: | stats dc(host) as Number_of_hosts. Now the if statement Number_of_hosts==0 will work.

0 Karma

jip31
Motivator

sorry I dont understand
something like this ?
| stats dc(host) as Number_of_hosts by sourcetype key_path | eval Number_of_host=if(Number_of_host==0, "0", "Number_of_host")

0 Karma

jip31
Motivator

what I need if there is no events for a sourcetype its the name of the sourcetype displayed in the table panel and 0 for "Number_of_hosts"

0 Karma
Get Updates on the Splunk Community!

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 ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...