Dashboards & Visualizations

how to format single value chart based on multiple occurrances

romoc
Explorer

Hello,
I'd like to create a single value chart, based on below search, to display the number of invalid objects in a database. I would like to format in red (critical) when there is at least one invalid object from OWNER "SYS" or "SYSTEM", yellow (warning) if there is invalid object is coming from any other owner than "SYS" and "SYSTEM". Green if there isn't any invalid object in the database. It would be desirable if the count to display were the total of invalid objects in the database.

sourcetype=oracle:object STATUS!=VALID | stats count by OWNER

OWNER COUNT
SYS 1
MYCUST 2

Any help will be really appreciated.

Tags (1)
0 Karma
1 Solution

niketn
Legend

Since you do not just want Color ranges by count rather want Color Ranges based on fields and count, I would suggest going for Status Indicator Custom Visualization (https://splunkbase.splunk.com/app/3119/). Further your requirement is to have only one panel with Single value based on priority of results i.e. Red, Yellow Green (respectively), which makes the required code a bit tricky.

Following query will decide whether the Panel is Red, Yellow Or Green.

sourcetype=oracle:object STATUS!="VALID" 
| stats count by OWNER
| appendcols [| makeresults 
              | eval count=0
              | eval OWNER="Ok"
              | fields - _time]
| eval Icon=case(OWNER=="SYS" OR OWNER=="SYSTEM","times-circle",OWNER=="Ok","check-circle",true(),"exclaimation-circle")
| stats sum(count) as Count by Icon
| transpose header_field="Icon" column_name="Icon"
| eval filter=case('times-circle'>0,"times-circle",'exclamation-circle'>0,"exclaimaton-circle",true(),"check-circle")

You will then need to use Search Event Handler to pass on the required values Count, Icon and Color to Status Indicator Viz.

      <done>
        <!-- Check Red condition first if true then set Red Values -->
        <condition match="$result.filter$==&quot;times-circle&quot;">
          <set token="tokCount">$result.times-circle$</set>
          <set token="tokIcon">times-circle</set>
          <set token="tokColor">#ff0000</set>
        </condition>
        <!-- Check Yellow condition second if true then set Yellow Values -->
        <condition match="$result.filter$==&quot;exclamation-circle&quot;">
          <set token="tokCount">$result.exclamation-circle$</set>
          <set token="tokIcon">exclamation-circle</set>
          <set token="tokColor">#ffc200</set>
        </condition>
        <!-- Check Green condition in the end if true then set Green Values -->
        <condition match="$result.filter$==&quot;check-circle&quot;">
          <set token="tokCount">$result.check-circle$</set>
          <set token="tokIcon">check-circle</set>
          <set token="tokColor">#008000</set>
        </condition>
      </done>

When there is no results for INVALID status by any OWNER, appendcols will add Ok (Green) Status Row. times_circle icon and #ff0000 represent Red, exclaimation-circle icon and #ffc200 represent Yellow and check-circle icon and #008000 represent Green. Refer to documentation on Status Indicator for details - https://docs.splunk.com/Documentation/StatusIndicator/latest/StatusIndicatorViz/StatusIndicatorSearc...

Following is run anywhere example using Splunk's _internal index :

  <!-- Query to find Status Indicator Count Icon and Color -->
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
    | stats count by log_level
    | appendcols [| makeresults 
          | eval count=0
          | eval log_level="Ok"
          | fields - _time]
    | eval Icon=case(log_level=="ERROR" OR log_level=="WARN","times-circle",log_level=="Ok","check-circle",true(),"exclaimation-circle")
    | stats sum(count) as Count by Icon
    | transpose header_field="Icon" column_name="Icon"
    | eval filter=case('times-circle'>0,"times-circle",'exclamation-circle'>0,"exclaimaton-circle",true(),"check-circle")</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <!-- Check Red condition first if true then set Red Values -->
            <condition match="$result.filter$==&quot;times-circle&quot;">
              <set token="tokCount">$result.times-circle$</set>
              <set token="tokIcon">times-circle</set>
              <set token="tokColor">#ff0000</set>
            </condition>
            <!-- Check Yellow condition second if true then set Yellow Values -->
            <condition match="$result.filter$==&quot;exclamation-circle&quot;">
              <set token="tokCount">$result.exclamation-circle$</set>
              <set token="tokIcon">exclamation-circle</set>
              <set token="tokColor">#ffc200</set>
            </condition>
            <!-- Check Green condition in the end if true then set Green Values -->
            <condition match="$result.filter$==&quot;check-circle&quot;">
              <set token="tokCount">$result.check-circle$</set>
              <set token="tokIcon">check-circle</set>
              <set token="tokColor">#008000</set>
            </condition>
          </done>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <viz type="status_indicator_app.status_indicator">
        <search>
          <query>| makeresults 
              | eval count=$tokCount$
              | eval icon="$tokIcon$"
              | eval color="$tokColor$"
              | table count icon color</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="height">100</option>
        <option name="status_indicator_app.status_indicator.colorBy">field_value</option>
        <option name="status_indicator_app.status_indicator.fillTarget">background</option>
        <option name="status_indicator_app.status_indicator.fixIcon">warning</option>
        <option name="status_indicator_app.status_indicator.icon">field_value</option>
        <option name="status_indicator_app.status_indicator.precision">0</option>
        <option name="status_indicator_app.status_indicator.showOption">1</option>
        <option name="status_indicator_app.status_indicator.staticColor">#555</option>
        <option name="status_indicator_app.status_indicator.useColors">true</option>
        <option name="status_indicator_app.status_indicator.useThousandSeparator">true</option>
      </viz>
    </panel>
  </row>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

Since you do not just want Color ranges by count rather want Color Ranges based on fields and count, I would suggest going for Status Indicator Custom Visualization (https://splunkbase.splunk.com/app/3119/). Further your requirement is to have only one panel with Single value based on priority of results i.e. Red, Yellow Green (respectively), which makes the required code a bit tricky.

Following query will decide whether the Panel is Red, Yellow Or Green.

sourcetype=oracle:object STATUS!="VALID" 
| stats count by OWNER
| appendcols [| makeresults 
              | eval count=0
              | eval OWNER="Ok"
              | fields - _time]
| eval Icon=case(OWNER=="SYS" OR OWNER=="SYSTEM","times-circle",OWNER=="Ok","check-circle",true(),"exclaimation-circle")
| stats sum(count) as Count by Icon
| transpose header_field="Icon" column_name="Icon"
| eval filter=case('times-circle'>0,"times-circle",'exclamation-circle'>0,"exclaimaton-circle",true(),"check-circle")

You will then need to use Search Event Handler to pass on the required values Count, Icon and Color to Status Indicator Viz.

      <done>
        <!-- Check Red condition first if true then set Red Values -->
        <condition match="$result.filter$==&quot;times-circle&quot;">
          <set token="tokCount">$result.times-circle$</set>
          <set token="tokIcon">times-circle</set>
          <set token="tokColor">#ff0000</set>
        </condition>
        <!-- Check Yellow condition second if true then set Yellow Values -->
        <condition match="$result.filter$==&quot;exclamation-circle&quot;">
          <set token="tokCount">$result.exclamation-circle$</set>
          <set token="tokIcon">exclamation-circle</set>
          <set token="tokColor">#ffc200</set>
        </condition>
        <!-- Check Green condition in the end if true then set Green Values -->
        <condition match="$result.filter$==&quot;check-circle&quot;">
          <set token="tokCount">$result.check-circle$</set>
          <set token="tokIcon">check-circle</set>
          <set token="tokColor">#008000</set>
        </condition>
      </done>

When there is no results for INVALID status by any OWNER, appendcols will add Ok (Green) Status Row. times_circle icon and #ff0000 represent Red, exclaimation-circle icon and #ffc200 represent Yellow and check-circle icon and #008000 represent Green. Refer to documentation on Status Indicator for details - https://docs.splunk.com/Documentation/StatusIndicator/latest/StatusIndicatorViz/StatusIndicatorSearc...

Following is run anywhere example using Splunk's _internal index :

  <!-- Query to find Status Indicator Count Icon and Color -->
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
    | stats count by log_level
    | appendcols [| makeresults 
          | eval count=0
          | eval log_level="Ok"
          | fields - _time]
    | eval Icon=case(log_level=="ERROR" OR log_level=="WARN","times-circle",log_level=="Ok","check-circle",true(),"exclaimation-circle")
    | stats sum(count) as Count by Icon
    | transpose header_field="Icon" column_name="Icon"
    | eval filter=case('times-circle'>0,"times-circle",'exclamation-circle'>0,"exclaimaton-circle",true(),"check-circle")</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <!-- Check Red condition first if true then set Red Values -->
            <condition match="$result.filter$==&quot;times-circle&quot;">
              <set token="tokCount">$result.times-circle$</set>
              <set token="tokIcon">times-circle</set>
              <set token="tokColor">#ff0000</set>
            </condition>
            <!-- Check Yellow condition second if true then set Yellow Values -->
            <condition match="$result.filter$==&quot;exclamation-circle&quot;">
              <set token="tokCount">$result.exclamation-circle$</set>
              <set token="tokIcon">exclamation-circle</set>
              <set token="tokColor">#ffc200</set>
            </condition>
            <!-- Check Green condition in the end if true then set Green Values -->
            <condition match="$result.filter$==&quot;check-circle&quot;">
              <set token="tokCount">$result.check-circle$</set>
              <set token="tokIcon">check-circle</set>
              <set token="tokColor">#008000</set>
            </condition>
          </done>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <viz type="status_indicator_app.status_indicator">
        <search>
          <query>| makeresults 
              | eval count=$tokCount$
              | eval icon="$tokIcon$"
              | eval color="$tokColor$"
              | table count icon color</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="height">100</option>
        <option name="status_indicator_app.status_indicator.colorBy">field_value</option>
        <option name="status_indicator_app.status_indicator.fillTarget">background</option>
        <option name="status_indicator_app.status_indicator.fixIcon">warning</option>
        <option name="status_indicator_app.status_indicator.icon">field_value</option>
        <option name="status_indicator_app.status_indicator.precision">0</option>
        <option name="status_indicator_app.status_indicator.showOption">1</option>
        <option name="status_indicator_app.status_indicator.staticColor">#555</option>
        <option name="status_indicator_app.status_indicator.useColors">true</option>
        <option name="status_indicator_app.status_indicator.useThousandSeparator">true</option>
      </viz>
    </panel>
  </row>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

romoc
Explorer

Thanks niketnilay - looks like it's what we were looking for. I will be working on get it implemented.

0 Karma

inventsekar
Ultra Champion

if you need to use the above query, you could try "Splunk 6.x Dashboard Examples" app, which got a "rangemap" that will be suitable for your task.
https://splunkbase.splunk.com/app/1603/
alt text

for single value, the search query should generate only one numerical output.
so you can use -

sourcetype=oracle:object STATUS!=VALID OWNER=SYS | stats count

then using the format options, you can apply color settings.

everytime, manually applying color, maybe looks like a strange task. but this will be useful for real time count monitors, or you can save it as a dashboard and when you open the dashboard, the query will be run and color will be automatically selected.

alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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