color rows based on two fields conditions

[Becherer]

I have a dashboard created displaying when a device connects to a specific access point. The dashboard is laid out like

Date/Time.................SSID.....................Device...............................Host
April 20 11:51..........Guest...................iPhone.......................192.168.1.15
April 21 10:00..........Production............comp\1221................192.168.1.21
April 21 10:02........Production...........iPhone ........................192.168.1.33

(comp\1221 is the computer name)

This is working great, but now I am looking to color red when a non production device is connected to the wifi network. How do I say any device that is named "comp\" that joins to Production, highlight green else highlight red if any other device connects to production.

<format type="color" field="SSID" field="Device">
               <colorPalette type="expression">if (value = "Production" AND "comp\*","#FF5733","#00FF3E")</colorPalette>

I am stuck and would appreciate any help.

Thank you.

[Redha]

Hi,

can you share the SPL command to displaying : 

date.........SSID......Device.......host

Thx

[niketn]

@Becherer besides the above option you can also check out the other two options you have to color table field based on other field. Including the answer by @kamlesh_vaghela using JS to do exactly what you have asked.

https://answers.splunk.com/answers/686288/how-do-you-highlight-a-table-cell-based-on-a-field.html

[Becherer]

@niketnilay This is good and was able to color specific results in a column, but I am needing to color a row if two columns don't match. In stead of having two columns be different colors, I would like to have the row highlight based on two fields in the same row but different.

I am not good at this. ha.

Is there a way for me to have where a device on a row does not match a predefined whitelist, it will show Alert!

Let me explain why I am needing this and maybe it will make more sense.
We want to have a system in place that will show alerts when an employee connects a personal device to the production network. Our company devices use the same naming convention and our SSID stay the same. So anything outside of the predefined names that then shows up on production will through an alert.

Our SSID's are
Production
Production1
Guest

Our company devices names are: same beginning (comp\ and Prod)
comp\1121
comp\1122
comp\1123
etc...
Prod\010
Prod\011
Prod\012
etc...

Any device that is not part of the predefined list that shows up on Production or Production1 will display "red" Alert!, else nothing is displayed. As long as there are not unknown devices connected to the production network, the fields are not colored.

Date/Time.................SSID.....................Device...............................Host.................Alert
April 20 11:51..........Guest...................iPhone.......................192.168.1.15...........All Good
April 21 10:00..........Production............comp\1221..............192.168.1.21..........All Good
April 21 10:02........Production...........iPhone ........................192.168.1.33............Alert!

[adonio · Accepted solution]

hello there,

would go with the eval statement on your search and create a binary field for yes or no alert
try this dashboard:

<dashboard>
  <label>Color Table</label>
  <row>
    <panel>
      <title>No Color Table</title>
      <table>
        <search>
          <query>| makeresults count=1 
| eval data = "April 20 11:51,Guest,iPhone,192.168.1.15;;;April 21 10:00,Production,comp\1221,192.168.1.21;;;
April 21 10:02,Production,iPhone,192.168.1.33;;;April 21 10:10,Production,comp\1231,192.168.1.21;;;April 21 11:00,Guest,comp\1421,192.168.1.21"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<DateTime>[^\,]+)\,(?<SSID>[^\,]+)\,(?<Device>[^\,]+)\,(?<Host>.+)"
| table DateTime Device SSID Host</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">50</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>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>With Alert</title>
      <table>
        <search>
          <query>| makeresults count=1 
| eval data = "April 20 11:51,Guest,iPhone,192.168.1.15;;;April 21 10:00,Production,comp\1221,192.168.1.21;;;
April 21 10:02,Production,iPhone,192.168.1.33;;;April 21 10:10,Production,comp\1231,192.168.1.21;;;April 21 11:00,Guest,comp\1421,192.168.1.21"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<DateTime>[^\,]+)\,(?<SSID>[^\,]+)\,(?<Device>[^\,]+)\,(?<Host>.+)"
| table DateTime Device SSID Host
| eval alert = if(like(Device,"comp%") AND SSID=="Production","Alert!","All Good")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">50</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="alert">
          <colorPalette type="map">{"All Good":#00FF3E,"Alert!":#FF5733}</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

screenshot:

hope it helps

[Becherer]

Let me explain why I am needing this and maybe it will make more sense.
We want to have a system in place that will show alerts when an employee connects a personal device to the production network. Our company devices use the same naming convention and our SSID stay the same. So anything outside of the predefined names that then shows up on production will through an alert.

Our SSID's are
Production
Production1
Guest

Our company devices names are: same beginning (comp\ and Prod)
comp\1121
comp\1122
comp\1123
etc...
Prod\010
Prod\011
Prod\012
etc...

Any device that is not part of the predefined list that shows up on Production or Production1 will display "red" Alert!, else nothing is displayed. As long as there are not unknown devices connected to the production network, the fields are not colored.

Date/Time.................SSID.....................Device...............................Host.................Alert
April 20 11:51..........Guest...................iPhone.......................192.168.1.15...........All Good
April 21 10:00..........Production............comp\1221..............192.168.1.21..........All Good
April 21 10:02........Production...........iPhone ........................192.168.1.33............Alert!

[Becherer]

@adonio
Let me explain why I am needing this and maybe it will make more sense.
We want to have a system in place that will show alerts when an employee connects a personal device to the production network. Our company devices use the same naming convention and our SSID stay the same. So anything outside of the predefined names that then shows up on production will through an alert.

Our SSID's are
Production
Production1
Guest

Our company devices names are: same beginning (comp\ and Prod)
comp\1121
comp\1122
comp\1123
etc...
Prod\010
Prod\011
Prod\012
etc...

Any device that is not part of the predefined list that shows up on Production or Production1 will display "red" Alert!, else nothing is displayed. As long as there are not unknown devices connected to the production network, the fields are not colored.

Date/Time.................SSID.....................Device...............................Host.................Alert
April 20 11:51..........Guest...................iPhone.......................192.168.1.15...........All Good
April 21 10:00..........Production............comp\1221..............192.168.1.21..........All Good
April 21 10:02........Production...........iPhone ........................192.168.1.33............Alert!

[Becherer]

@adonio
Adonio! Thank you! I have one more request. What if I wanted to add another SSID like Production1 and all devices with the start prefix of "dev-" would show "All Good" as well as comp\1421 as you have shown above?

Something like this. I changes it around... I switched the Alert! with All Good. That way devices with the name comp\1421 or dev-%, that is connected to Production1 or Production would show green "All Good" then everything else would show red "Alert!"

| eval alert = if(like(Device,"comp%", OR Device,"dev-%") AND SSID=="Production1", OR "Production","All Good","Alert!")

You were a great help in getting this to work!

[adonio]

@Becherer do you need any further assistance here?
if not, kindly accept the answer that helped, feel free to up-vote helpful answers and / or comments