topic Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)? in Splunk Search

How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Tue, 20 Mar 2018 14:31:20 GMT

Hello.

I'm trying to compare two panels to see if there are any changes in the count. Both panels should be equal but if it changes (allowing a count of plus/minus 5 for catch up) then notify in another panel, i.e.

If both panels have the same count then display GOOD in third panel.
If numbers differ, then display WARNING in same third panel as above.

1st Panel search string...
index="file1" type=input" | stats count

2nd Panel search string...
index="file2" type="output" | stats count 

3rd Panel ?????...
compare   index="file1" type=input" | stats count   with   index="file2" type="output" | stats count   if   difference is greater or less than 5 then echo WARNING   otherwise   GOOD

I'm struggling with the 3rd panel and have completely lost count of the variations I have tried but the logic is above.

,Hello.

If both panels have the same count then display GOOD in third panel.
If numbers differ, then display WARNING in same third panel as above.

1st Panel search string...
index="file1" type=input" | stats count

2nd Panel search string...
index="file2" type="output" | stats count 

3rd Panel search string...
compare   index="file1" type=input" | stats count   with   index="file2" type="output" | stats count   if   difference is greater or less than 5 then echo WARNING   otherwise   GOOD

I'm struggling with the 3rd panel and have completely lost count of the variations I have tried but the logic is above.

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

strive — Tue, 20 Mar 2018 15:18:33 GMT

You can have base search and refer that in other panels. Something like this

<query>
  index="file1" type=input" | stats count as Count1 by _time | join _time type=left [index="file2" type="output" | stats count as Count2 by _time]
</query>
<earliest>0</earliest>
<latest></latest>




 <panel>
  <chart>
    <title>Panel 1</title>
    <search base="base_search">
      <query>| fields Count1</query>
      <earliest>0</earliest>
      <latest></latest>
    </search>



<panel>
  <chart>
    <title>Panel 2</title>
    <search base="base_search">
      <query>| fields Count2</query>
      <earliest>0</earliest>
      <latest></latest>
    </search>



<panel>
  <chart>
    <title>Panel 3</title>
    <search base="base_search">
      <query>| eval Field3=case((Count1-Count2)=5,"GOOD","WARNING") | fields Field3</query>
      <earliest>0</earliest>
      <latest></latest>
    </search>

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

strive — Tue, 20 Mar 2018 15:19:39 GMT

Are you sure that Count1-Count2=5 then Good else WARNING?

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

strive — Tue, 20 Mar 2018 15:20:43 GMT

OR you can also take the search like this for 3rd panel

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Tue, 20 Mar 2018 15:54:16 GMT

Count1 should equal Count2 - there may be times when the difference is out by plus or minus 5 which is acceptable. If it's anymore than that, then it's a WARNING. Hope that makes sense 😕

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Tue, 20 Mar 2018 15:56:56 GMT

so...

10 = 10 = GOOD

10 = 12 = GOOD

10 = 8 = GOOD

10 = 18 = WARNING

10 = 3 = WARNING

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Tue, 20 Mar 2018 16:28:14 GMT

I can never get the join to work. Tried it in other queries with little success - " Search Factory: Unknown search command 'index' " My searches look the same as yours but I'm obviously doing something wrong 😕

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

jluo_splunk — Tue, 20 Mar 2018 18:58:47 GMT

Try this out for your third panel..

(index="file1" type="input") OR (index="file2" type="output") | stats count(eval(type="input")) as input, count(eval(type="output")) as output | eval result=if(input==output, "GOOD", "WARNING")

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

niketn — Tue, 20 Mar 2018 19:41:50 GMT

@Derben, Since your first two panels are single value results | stats count. You can use <search> Event Handler to set the results of first two panels as tokens and then use them in the third panel. Similar to one of my older answers: https://answers.splunk.com/answers/580233/use-values-from-two-panels-in-a-third-panel.html

Please find below a run anywhere example which has dummy searches with Input and Output count set via text box for testing. You can replace the two panel searches with your own search. (PS: Also for testing I have a hidden time input using depends="$alwaysHideDefaultTimeForDemo$" since dummy makeresults command does not need time I have set it to Last 1 second).

<form>
  <label>Compare two panels and show result in third</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true" depends="$alwaysHideDefaultTimeForDemo$">
      <label>Time (default value)</label>
      <default>
        <earliest>-1s@s</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Input</title>
      <input type="text" token="tokInput" searchWhenChanged="true">
        <label>Enter Input count for testing</label>
        <default>10</default>
      </input>
      <single>
        <search>
          <query>| makeresults 
| fields - _time 
| eval count=$tokInput$</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <!-- Your First Query Goes Here-->
        <option name="refresh.display">progressbar</option>
      </single>
    </panel>
    <panel>
      <title>Output</title>
      <input type="text" token="tokOutput" searchWhenChanged="true">
        <label>Enter Output count for testing</label>
        <default>4</default>
      </input>
      <single>
        <search>
          <query>| makeresults 
| fields - _time 
| eval count=$tokOutput$</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
      </single>
    </panel>
    <panel>
      <title>Result</title>
      <table>
        <search>
          <query>| makeresults 
| fields - _time 
| eval message=if($tokInput$>=$tokOutput$,$tokInput$-$tokOutput$,$tokOutput$-$tokInput$)
| eval message=if(message>5,"WARNING","GOOD")</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <!-- No changes required for this query -->
        <option name="count">10</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="message">
          <colorPalette type="map">{"GOOD":#65A637,"WARNING":#D93F3C}</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</form>

While the simple XML I have provided is for results to be displayed in third panel via a Table with Color ranges, you can get Status Indicator Custom Visualization which can display the same with Color Icon and Text message. Following is code for third panel in case you have Status Indicator Custom Visualization available.

    <panel>
      <title>Result</title>
      <viz type="status_indicator_app.status_indicator">
        <search>
          <query>| makeresults 
| fields - _time 
| eval diff=if($tokInput$>=$tokOutput$,$tokInput$-$tokOutput$,$tokOutput$-$tokInput$)
| eval message=if(diff>5,"WARNING","GOOD")
| eval icon=if(diff>5,"times-circle","info-circle")
| eval color=if(diff>5,"red","green")
| table message icon color</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <!-- No changes required for this query -->
        <option name="drilldown">none</option>
        <option name="status_indicator_app.status_indicator.colorBy">field_value</option>
        <option name="status_indicator_app.status_indicator.fillTarget">background</option>
        <option name="status_indicator_app.status_indicator.fixIcon">warning</option>
        <option name="status_indicator_app.status_indicator.icon">field_value</option>
        <option name="status_indicator_app.status_indicator.precision">0</option>
        <option name="status_indicator_app.status_indicator.showOption">1</option>
        <option name="status_indicator_app.status_indicator.staticColor">#555</option>
        <option name="status_indicator_app.status_indicator.useColors">true</option>
        <option name="status_indicator_app.status_indicator.useThousandSeparator">true</option>
      </viz>
    </panel>

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Thu, 22 Mar 2018 11:18:16 GMT

Brilliant. Thanks for all your help. We've now got it looking like we wanted. The only outstanding issue we have is that we'd like the query result to appear in relevant colour. It's no a number output, just text, therefore the word GOOD should be GREEN.

I've tried the suggestion above...

      <colorPalette type="map">{"GOOD":#65A637,"WARNING":#D93F3C}</colorPalette>
  </format>

but it just brings back a warning so I'm obvs doing something wrong. Is there not an easy way of changing the colour of character results?

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

niketn — Thu, 22 Mar 2018 16:31:53 GMT

@Derben, you can edit directly from UI and and Color by Value > Define Rules to color based on values in the table. Besides color have you verified whether both the values GOOD and WARNING are being displayed in table as per your input and output count?

I hope you are on Splunk 6.5 or higher.

Re: How to compare two fields and result in a 'pass' or 'fail' in another field (give or take a count of 5)?

Derben — Thu, 22 Mar 2018 16:59:16 GMT

We're on v7 🙂 But I can't find that option. I thought only numeric values are changeable? My visualization is 'single value' but using words 'good' 'caution' and 'warning' 😕