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
@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
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>
@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
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>
@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?
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
Both r separate field names
Reportsize
Sizeunit