Dashboards & Visualizations

How to use advanced xml to color a “bar” in a bar chart with diff colors based upon a field value

akhil4mdev
Explorer

Hello
I have a reportcreatedate for a report

Imagine I have 5 report instances of a report named A in one day

| timechart dc(reportcreateddate)

_time. Dc(reportcreateddate)

05-10. 5
05-11. 10
05-12 3

I am getting a time chart

So each report instance has a size

It means on 5-10 we have 5 reports with diff sizes
5.6kb,7kb,4kb,10kb,15kb

So I decided a threshold reports under 6kb are empty

So I want a time chart in a way that
It shows count of reports over time

And I want the chart bars to be covered by percentage of good reports that r present on that day for example

On 5-11 we have 10 reports if 5 of them r below 6kb and remaining five or above 6kb

So in the time chart

The bar of that day should be fifty percent green and fifty percent red

Is this possible ? How ? What will be the query

Reportcreateddate
Reportsize
_time are the required fields

Thank you

Tags (1)
0 Karma
1 Solution

niketn
Legend

@akhil4mdev, please try the following run aywhere dashboard based on details and sample data provided. While I dont have TIme field and its value based on the sample data following is the query based on Time field between 1-24

alt text

Following is the Simple XML code as per the samepl data provided. With Tabular Details and Chart representation of Good% and Bad %:

<dashboard>
  <label>Report with Good and Bad size</label>
  <row>
    <panel>
      <title>Tabular details</title>
      <table>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  stats count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1)." %",Bad=round((Bad/uniqueReportSize)*100,1)." %"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="Bad">
          <colorPalette type="expression">case(true(), "#DC4E41")</colorPalette>
        </format>
        <format type="color" field="Good">
          <colorPalette type="expression">case(true(), "#53A051")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Chart</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  chart count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1),Bad=round((Bad/uniqueReportSize)*100,1)
| table TimeWindow Good Bad</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"Bad":"0xDC4E41","Good":"0x53A051"}</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@akhil4mdev, please try the following run aywhere dashboard based on details and sample data provided. While I dont have TIme field and its value based on the sample data following is the query based on Time field between 1-24

alt text

Following is the Simple XML code as per the samepl data provided. With Tabular Details and Chart representation of Good% and Bad %:

<dashboard>
  <label>Report with Good and Bad size</label>
  <row>
    <panel>
      <title>Tabular details</title>
      <table>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  stats count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1)." %",Bad=round((Bad/uniqueReportSize)*100,1)." %"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="Bad">
          <colorPalette type="expression">case(true(), "#DC4E41")</colorPalette>
        </format>
        <format type="color" field="Good">
          <colorPalette type="expression">case(true(), "#53A051")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Chart</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  chart count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1),Bad=round((Bad/uniqueReportSize)*100,1)
| table TimeWindow Good Bad</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"Bad":"0xDC4E41","Good":"0x53A051"}</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

niketn
Legend

@akhil4mdev what is the name of size field? Also is its value just 4.5,5.4 or does it have kb in it like in your example 4.5kb and 5.4kb?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ramarcsight
Explorer

It have kb , bytes also but I think first I need to convert them all to KB
Could you please help me in that also

Reportsize=5.6
Sizeunit=Kib

Reportsize=118
Sizeunit=B which means bytes

First I need to convert all of them to Kib then do the coloring

0 Karma

ramarcsight
Explorer

Both r separate field names

Reportsize
Sizeunit

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