Splunk Search

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

sangs8788
Communicator

Hi,

I have a below search result which shows Violators as red in color. Violators are more than 2 sec

Screenshot 2020-12-02 at 4.08.15 PM.png

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,

ModuleLastViolatedMonthLastViolatedResponseViolationCount
AppSDKNov-20203.1710
DocumentExchangeOct-20202.296
FrieghtAuditAug-20202.184

 

could someone please help me out here.

Thanks

Labels (5)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

sangs8788
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...