Hi,
I have a below search result which shows Violators as red in color. Violators are more than 2 sec
I would like to get a resultset which would show the latest month when the violation happened, its response time and the number of times it has violated. Something like,
Module | LastViolatedMonth | LastViolatedResponse | ViolationCount |
AppSDK | Nov-2020 | 3.17 | 10 |
DocumentExchange | Oct-2020 | 2.29 | 6 |
FrieghtAudit | Aug-2020 | 2.18 | 4 |
could someone please help me out here.
Thanks
Here's a sample dashboard with a simulation of your data. The month names are slightly different, but essentially the last two lines of the query will do what you want - adjusting for your field names. Work is done by the foreach command and is, in the example, working on all the 2* field names.
<dashboard>
<label>Violations</label>
<row>
<panel>
<table>
<search>
<query>| makeresults
| eval Module=split("AppSDK,DocumentExchange,FrieghtAudit",",")
| mvexpand Module
| eval X_202001=0, X_202002=0, X_202003=0, X_202004=0, X_202005=0, X_202006=0, X_202007=0, X_202008=0, X_202009=0, X_202010=0, X_202011=0
| foreach X_* [ eval <<FIELD>>=(random() % 200 + 130) / 100 ]
| rename X_* as *
| table Module *
| fields - _time
| eval ViolationCount=0, LastViolatedMonth="", LastViolatedResonse=0
| foreach 2* [ eval ViolationCount=if('<<FIELD>>'>2,ViolationCount+1,ViolationCount), LastViolatedMonth=if('<<FIELD>>'>2,"2<<MATCHSTR>>",LastViolatedMonth), LastViolatedResonse=if('<<FIELD>>'>2,'<<FIELD>>',LastViolatedResonse)]</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">100</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>
<format type="color" field="202001">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202002">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202003">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202004">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202005">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202006">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202007">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202008">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202009">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202010">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202011">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
</table>
</panel>
</row>
</dashboard>
Hope this helps.
Here's a sample dashboard with a simulation of your data. The month names are slightly different, but essentially the last two lines of the query will do what you want - adjusting for your field names. Work is done by the foreach command and is, in the example, working on all the 2* field names.
<dashboard>
<label>Violations</label>
<row>
<panel>
<table>
<search>
<query>| makeresults
| eval Module=split("AppSDK,DocumentExchange,FrieghtAudit",",")
| mvexpand Module
| eval X_202001=0, X_202002=0, X_202003=0, X_202004=0, X_202005=0, X_202006=0, X_202007=0, X_202008=0, X_202009=0, X_202010=0, X_202011=0
| foreach X_* [ eval <<FIELD>>=(random() % 200 + 130) / 100 ]
| rename X_* as *
| table Module *
| fields - _time
| eval ViolationCount=0, LastViolatedMonth="", LastViolatedResonse=0
| foreach 2* [ eval ViolationCount=if('<<FIELD>>'>2,ViolationCount+1,ViolationCount), LastViolatedMonth=if('<<FIELD>>'>2,"2<<MATCHSTR>>",LastViolatedMonth), LastViolatedResonse=if('<<FIELD>>'>2,'<<FIELD>>',LastViolatedResonse)]</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">100</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>
<format type="color" field="202001">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202002">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202003">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202004">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202005">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202006">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202007">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202008">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202009">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202010">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
<format type="color" field="202011">
<colorPalette type="list">[#C1FA9B,#FC9D9C]</colorPalette>
<scale type="threshold">2</scale>
</format>
</table>
</panel>
</row>
</dashboard>
Hope this helps.
Thanks. This worked. I just had to change the Month name to month number for this to work.