How to get the latest violator value and its month and the number of times it has violated

[sangs8788]

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

[bowesmana · Accepted solution]

@sangs8788 

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 &lt;&lt;FIELD&gt;&gt;=(random() % 200 + 130) / 100 ]
| rename X_* as *
| table Module *
| fields - _time
| eval ViolationCount=0, LastViolatedMonth="", LastViolatedResonse=0
| foreach 2* [ eval ViolationCount=if('&lt;&lt;FIELD&gt;&gt;'&gt;2,ViolationCount+1,ViolationCount), LastViolatedMonth=if('&lt;&lt;FIELD&gt;&gt;'&gt;2,"2&lt;&lt;MATCHSTR&gt;&gt;",LastViolatedMonth), LastViolatedResonse=if('&lt;&lt;FIELD&gt;&gt;'&gt;2,'&lt;&lt;FIELD&gt;&gt;',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.

[sangs8788]

Thanks. This worked. I just had to change the Month name to month number for this to work.