Splunk Search

How to rename label in splunk legend that not effected the lookup name?

matoulas
Path Finder

Hi,

I would like to display each plugname on the legend instead of plug1, plug2, plug3 and so on using timechart.
I was able to display plug1, plug2, plug3 as a lable name and so on, on the chart legend, but couldn't get the plugname for each plug to display on the legend.

How to rename or show a label in splunk legend to show the dynamic plugname when performing a search.
Please looking forward to your help to resolve this problem.

Below is the JSON code that send to Splunk:

{
    "timestamp": "2019-06-21T21:50:13+00:00",
    "siteid": "LABRACK1",
    "assettag": "CPM-1600-1-ECAM-LAB",
    "powerunit": "1",
    "outletmetering": "1",
    "ats": "1",
    "branchcount": "2",
    "plugcount": "16",
    "powerfactor": "100",
    "powereff": "100",
    "powerdatacount": "1",
    "powerdata": [{
        "timestamp": "2019-06-21T21:50:13+00:00",
        "plug1": [{
            "voltage": "114.00",
            "current": "2.00",
            "branch": "1",
                        "plugname": "Outlet_A1"
        }],
        "plug2": [{
            "voltage": "114.00",
            "current": "5.00",
            "branch": "1",
                        "plugname": "Outlet_A2"
        }],
        "plug3": [{
            "voltage": "114.00",
            "current": "3.00",
            "branch": "1",
                        "plugname": "Cisco-ASA5510"
        }],
        "plug4": [{
            "voltage": "114.00",
            "current": "1.00",
            "branch": "1",
                        "plugname": "Cisco-SW3560"
        }],
        "plug5": [{
            "voltage": "114.00",
            "current": "2.00",
            "branch": "1",
                        "plugname": "Cisco-SW3760"
        }],
        "plug6": [{
            "voltage": "114.00",
            "current": "5.00",
            "branch": "1",
                        "plugname": "UPS"
        }],
        "plug7": [{
            "voltage": "114.00",
            "current": "3.00",
            "branch": "1",
                        "plugname": "Outlet_A7"
        }],
        "plug8": [{
            "voltage": "114.00",
            "current": "1.00",
            "branch": "1",
                        "plugname": "Outlet_A8"
        }],
        "plug9": [{
            "voltage": "119.00",
            "current": "2.00",
            "branch": "2",
                        "plugname": "F5"
        }],
        "plug10": [{
            "voltage": "119.00",
            "current": "7.00",
            "branch": "2",
                        "plugname": "Camera"
        }],
        "plug11": [{
            "voltage": "119.00",
            "current": "5.00",
            "branch": "2",
                        "plugname": "TV"
        }],
        "plug12": [{
            "voltage": "119.00",
            "current": "1.00",
            "branch": "2",
                        "plugname": "Meraki"
        }],
        "plug13": [{
            "voltage": "119.00",
            "current": "4.00",
            "branch": "2",
                        "plugname": "Outlet_13"
        }],
        "plug14": [{
            "voltage": "119.00",
            "current": "5.00",
            "branch": "2",
                        "plugname": "Outlet_A14"
        }],
        "plug15": [{
            "voltage": "119.00",
            "current": "3.00",
            "branch": "2",
                        "plugname": "PC"
        }],
        "plug16": [{
            "voltage": "119.00",
            "current": "1.00",
            "branch": "2",
                        "plugname": "Outlet_A16"
        }]
    }]
}
Tags (2)
1 Solution

woodcock
Esteemed Legend

Wow, that was FUN! Try this:

| makeresults 
|  eval _raw="{
    \"timestamp\": \"2019-06-21T21:50:13+00:00\",
    \"siteid\": \"LABRACK1\",
    \"assettag\": \"CPM-1600-1-ECAM-LAB\",
    \"powerunit\": \"1\",
    \"outletmetering\": \"1\",
    \"ats\": \"1\",
    \"branchcount\": \"2\",
    \"plugcount\": \"16\",
    \"powerfactor\": \"100\",
    \"powereff\": \"100\",
    \"powerdatacount\": \"1\",
    \"powerdata\": [{
        \"timestamp\": \"2019-06-21T21:50:13+00:00\",
        \"plug1\": [{
            \"voltage\": \"114.00\",
            \"current\": \"2.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A1\"
        }],
        \"plug2\": [{
            \"voltage\": \"114.00\",
            \"current\": \"5.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A2\"
        }],
        \"plug3\": [{
            \"voltage\": \"114.00\",
            \"current\": \"3.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-ASA5510\"
        }],
        \"plug4\": [{
            \"voltage\": \"114.00\",
            \"current\": \"1.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-SW3560\"
        }],
        \"plug5\": [{
            \"voltage\": \"114.00\",
            \"current\": \"2.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-SW3760\"
        }],
        \"plug6\": [{
            \"voltage\": \"114.00\",
            \"current\": \"5.00\",
            \"branch\": \"1\",
                        \"plugname\": \"UPS\"
        }],
        \"plug7\": [{
            \"voltage\": \"114.00\",
            \"current\": \"3.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A7\"
        }],
        \"plug8\": [{
            \"voltage\": \"114.00\",
            \"current\": \"1.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A8\"
        }],
        \"plug9\": [{
            \"voltage\": \"119.00\",
            \"current\": \"2.00\",
            \"branch\": \"2\",
                        \"plugname\": \"F5\"
        }],
        \"plug10\": [{
            \"voltage\": \"119.00\",
            \"current\": \"7.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Camera\"
        }],
        \"plug11\": [{
            \"voltage\": \"119.00\",
            \"current\": \"5.00\",
            \"branch\": \"2\",
                        \"plugname\": \"TV\"
        }],
        \"plug12\": [{
            \"voltage\": \"119.00\",
            \"current\": \"1.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Meraki\"
        }],
        \"plug13\": [{
            \"voltage\": \"119.00\",
            \"current\": \"4.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_13\"
        }],
        \"plug14\": [{
            \"voltage\": \"119.00\",
            \"current\": \"5.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_A14\"
        }],
        \"plug15\": [{
            \"voltage\": \"119.00\",
            \"current\": \"3.00\",
            \"branch\": \"2\",
                        \"plugname\": \"PC\"
        }],
        \"plug16\": [{
            \"voltage\": \"119.00\",
            \"current\": \"1.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_A16\"
        }]
    }]
}"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| spath
| fields - _raw
| foreach powerdata{}.plug*{}.* [ eval eachplug<<MATCHSEG1>> = mvappend(eachplug<<MATCHSEG1>>, "<<MATCHSEG2>>=" . '<<FIELD>>') |  fields - <<FIELD>> ]
| foreach eachplug* [ nomv <<FIELD>> |  eval allplugs = mvappend(allplugs, "plug=<<MATCHSTR>>, " . <<FIELD>>) |  fields - <<FIELD>> ]
| mvexpand allplugs
| rename allplugs AS _raw
| kv
| timechart limit=0 max(current) AS max_current BY plugname

View solution in original post

matoulas
Path Finder

Now the search query is working base on

Search query:

siteid=* location=*
| foreach powerdata{}.plug*{}.* [ eval eachplug<<MATCHSEG1>> = mvappend(eachplug<<MATCHSEG1>>, "<<MATCHSEG2>>=" . '<<FIELD>>') |  fields - <<FIELD>> ]
| foreach eachplug* [ nomv <<FIELD>> |  eval allplugs = mvappend(allplugs, "plug=<<MATCHSTR>>, " . <<FIELD>>) |  fields - <<FIELD>> ]
| mvexpand allplugs
| rename allplugs AS _raw
| kv
| timechart limit=0 max(current) AS max_current BY plugname

alt text

In addition, next step I would like to be able to click on individual plug name on the bar chart or from the legend label for the drill down details. As of now when I click it. It said no result found. Please I really need help and thank you in advance.

0 Karma

woodcock
Esteemed Legend

Start a new question.

0 Karma

matoulas
Path Finder
0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="{
\"timestamp\": \"2019-06-21T21:50:13+00:00\",
\"siteid\": \"LABRACK1\",
\"assettag\": \"CPM-1600-1-ECAM-LAB\",
\"powerunit\": \"1\",
\"outletmetering\": \"1\",
\"ats\": \"1\",
\"branchcount\": \"2\",
\"plugcount\": \"16\",
\"powerfactor\": \"100\",
\"powereff\": \"100\",
\"powerdatacount\": \"1\",
\"powerdata\": [{
\"timestamp\": \"2019-06-21T21:50:13+00:00\",
\"plug1\": [{
\"voltage\": \"114.00\",
\"current\": \"2.00\",
\"branch\": \"1\",
\"plugname\": \"Outlet_A1\"
}],
\"plug2\": [{
\"voltage\": \"114.00\",
\"current\": \"5.00\",
\"branch\": \"1\",
\"plugname\": \"Outlet_A2\"
}],
\"plug3\": [{
\"voltage\": \"114.00\",
\"current\": \"3.00\",
\"branch\": \"1\",
\"plugname\": \"Cisco-ASA5510\"
}],
\"plug4\": [{
\"voltage\": \"114.00\",
\"current\": \"1.00\",
\"branch\": \"1\",
\"plugname\": \"Cisco-SW3560\"
}],
\"plug5\": [{
\"voltage\": \"114.00\",
\"current\": \"2.00\",
\"branch\": \"1\",
\"plugname\": \"Cisco-SW3760\"
}],
\"plug6\": [{
\"voltage\": \"114.00\",
\"current\": \"5.00\",
\"branch\": \"1\",
\"plugname\": \"UPS\"
}],
\"plug7\": [{
\"voltage\": \"114.00\",
\"current\": \"3.00\",
\"branch\": \"1\",
\"plugname\": \"Outlet_A7\"
}],
\"plug8\": [{
\"voltage\": \"114.00\",
\"current\": \"1.00\",
\"branch\": \"1\",
\"plugname\": \"Outlet_A8\"
}],
\"plug9\": [{
\"voltage\": \"119.00\",
\"current\": \"2.00\",
\"branch\": \"2\",
\"plugname\": \"F5\"
}],
\"plug10\": [{
\"voltage\": \"119.00\",
\"current\": \"7.00\",
\"branch\": \"2\",
\"plugname\": \"Camera\"
}],
\"plug11\": [{
\"voltage\": \"119.00\",
\"current\": \"5.00\",
\"branch\": \"2\",
\"plugname\": \"TV\"
}],
\"plug12\": [{
\"voltage\": \"119.00\",
\"current\": \"1.00\",
\"branch\": \"2\",
\"plugname\": \"Meraki\"
}],
\"plug13\": [{
\"voltage\": \"119.00\",
\"current\": \"4.00\",
\"branch\": \"2\",
\"plugname\": \"Outlet_13\"
}],
\"plug14\": [{
\"voltage\": \"119.00\",
\"current\": \"5.00\",
\"branch\": \"2\",
\"plugname\": \"Outlet_A14\"
}],
\"plug15\": [{
\"voltage\": \"119.00\",
\"current\": \"3.00\",
\"branch\": \"2\",
\"plugname\": \"PC\"
}],
\"plug16\": [{
\"voltage\": \"119.00\",
\"current\": \"1.00\",
\"branch\": \"2\",
\"plugname\": \"Outlet_A16\"
}]
}]
}"
| spath
| fields - _raw
| untable _time category status
| rex field=category "powerdata\{\}\.(?<plug>\w+)\{\}\.(?<cat>\w+)"
| eval tmp=mvzip(cat,status,"=")
| rename tmp as _raw
| kv
| fields - _raw category status cat
| stats values(_time) as _time values(*) as * by plug
| sort 0 branch plug
`comment("table create")`
| table _time plugname current
| xyseries _time plugname current

It was also beautiful here.

| spath 
| fields - _raw 
| untable _time category status 
| rex field=category "powerdata\{\}\.(?<plug>\w+)\{\}\.(?<cat>\w+)" 
| chart limit=0 values(status) values(_time) as t over plug by cat 
| table "t: plugname" values(status)* 
| rename "t: plugname" as _time, "values(status): *" as * 
| sort 0 branch 
`comment("table create")` 
| table _time plugname current 
| xyseries _time plugname current

This is also, too.

woodcock
Esteemed Legend

Wow, that was FUN! Try this:

| makeresults 
|  eval _raw="{
    \"timestamp\": \"2019-06-21T21:50:13+00:00\",
    \"siteid\": \"LABRACK1\",
    \"assettag\": \"CPM-1600-1-ECAM-LAB\",
    \"powerunit\": \"1\",
    \"outletmetering\": \"1\",
    \"ats\": \"1\",
    \"branchcount\": \"2\",
    \"plugcount\": \"16\",
    \"powerfactor\": \"100\",
    \"powereff\": \"100\",
    \"powerdatacount\": \"1\",
    \"powerdata\": [{
        \"timestamp\": \"2019-06-21T21:50:13+00:00\",
        \"plug1\": [{
            \"voltage\": \"114.00\",
            \"current\": \"2.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A1\"
        }],
        \"plug2\": [{
            \"voltage\": \"114.00\",
            \"current\": \"5.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A2\"
        }],
        \"plug3\": [{
            \"voltage\": \"114.00\",
            \"current\": \"3.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-ASA5510\"
        }],
        \"plug4\": [{
            \"voltage\": \"114.00\",
            \"current\": \"1.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-SW3560\"
        }],
        \"plug5\": [{
            \"voltage\": \"114.00\",
            \"current\": \"2.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Cisco-SW3760\"
        }],
        \"plug6\": [{
            \"voltage\": \"114.00\",
            \"current\": \"5.00\",
            \"branch\": \"1\",
                        \"plugname\": \"UPS\"
        }],
        \"plug7\": [{
            \"voltage\": \"114.00\",
            \"current\": \"3.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A7\"
        }],
        \"plug8\": [{
            \"voltage\": \"114.00\",
            \"current\": \"1.00\",
            \"branch\": \"1\",
                        \"plugname\": \"Outlet_A8\"
        }],
        \"plug9\": [{
            \"voltage\": \"119.00\",
            \"current\": \"2.00\",
            \"branch\": \"2\",
                        \"plugname\": \"F5\"
        }],
        \"plug10\": [{
            \"voltage\": \"119.00\",
            \"current\": \"7.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Camera\"
        }],
        \"plug11\": [{
            \"voltage\": \"119.00\",
            \"current\": \"5.00\",
            \"branch\": \"2\",
                        \"plugname\": \"TV\"
        }],
        \"plug12\": [{
            \"voltage\": \"119.00\",
            \"current\": \"1.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Meraki\"
        }],
        \"plug13\": [{
            \"voltage\": \"119.00\",
            \"current\": \"4.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_13\"
        }],
        \"plug14\": [{
            \"voltage\": \"119.00\",
            \"current\": \"5.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_A14\"
        }],
        \"plug15\": [{
            \"voltage\": \"119.00\",
            \"current\": \"3.00\",
            \"branch\": \"2\",
                        \"plugname\": \"PC\"
        }],
        \"plug16\": [{
            \"voltage\": \"119.00\",
            \"current\": \"1.00\",
            \"branch\": \"2\",
                        \"plugname\": \"Outlet_A16\"
        }]
    }]
}"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| spath
| fields - _raw
| foreach powerdata{}.plug*{}.* [ eval eachplug<<MATCHSEG1>> = mvappend(eachplug<<MATCHSEG1>>, "<<MATCHSEG2>>=" . '<<FIELD>>') |  fields - <<FIELD>> ]
| foreach eachplug* [ nomv <<FIELD>> |  eval allplugs = mvappend(allplugs, "plug=<<MATCHSTR>>, " . <<FIELD>>) |  fields - <<FIELD>> ]
| mvexpand allplugs
| rename allplugs AS _raw
| kv
| timechart limit=0 max(current) AS max_current BY plugname

matoulas
Path Finder

It work!!!
Thank you so much!

matoulas
Path Finder

Hi woodcock,

In addition, I would like to be able to click on the legend label for drill down to see individual activity current occur per plug name.

Here is my search string

| search siteid=$siteid$ location=$location$
| foreach powerdata{}.plug*{}.* [ eval eachplug<> = mvappend(eachplug<>, "<>=" . '<>') | fields - <> ]
| foreach eachplug* [ nomv <> | eval allplugs = mvappend(allplugs, "plug=<>, " . <>) | fields - <> ]
| mvexpand allplugs
| rename allplugs AS _raw
| kv
| timechart limit=0 max(voltage) AS max_voltage BY plugname

The bar chart work great as expected, but need to be able to click on a single legend for the drill down on the bar chart. I don't know how to do it.

0 Karma

woodcock
Esteemed Legend

This approach rearranges each single event into multilpe events, one for each plugname. From there you can do anything easily.

0 Karma

matoulas
Path Finder

I got the warning message on my bar chart said Field 'allplugs' does not exist in the data. Only when I select individual siteid and location from my drop down.

when using this search string on my dashboard

| search siteid=$siteid$ location=$location$
| foreach powerdata{}.plug*{}.* [ eval eachplug<<MATCHSEG1>> = mvappend(eachplug<<MATCHSEG1>>, "<<MATCHSEG2>>=" . '<<FIELD>>') |  fields - <<FIELD>> ]
| foreach eachplug* [ nomv <<FIELD>> |  eval allplugs = mvappend(allplugs, "plug=<<MATCHSTR>>, " . <<FIELD>>) |  fields - <<FIELD>> ]
| mvexpand allplugs
| rename allplugs AS _raw
| kv
| timechart limit=0 max(current) AS max_current BY plugname

But when I select ALL from my drop down the warning working great.

How to bypass the the 'allplugs' when not selected ALL?

0 Karma

woodcock
Esteemed Legend

Must be a problem with your dashboard tokens.

0 Karma

matoulas
Path Finder

Never mind the warning just went away now. Thanks for all your help.

tiagofbmm
Influencer

How about this strategy:

| rex mode=sed "s/plug(\d)/plug/g" 
| spath output=pname path=powerdata{}.plug{}.plugname
| spath
| timechart max(powerdata{}.plug{}.current) as max_current,  max(powerdata{}.plug{}.voltage) as max_voltage by powerdata{}.plug{}.plugname
0 Karma

matoulas
Path Finder

Need your help see below

0 Karma

matoulas
Path Finder

Need help when using your suggestion above code is working great but why do I get a result for all the plug current from Outlet_A1 - Outlet_B8 are showing the same result like 1.97 and so on. It should show the difference for each Outlet-A1 - Outlet_B8 base on the plug current. See screenshot for details.

alt text

0 Karma

matoulas
Path Finder

Updated I got it to display all the plug now from at lest 1 digit or more

| rex mode=sed "s/plug(\d+)/plug/g"

By follow the splunk doc regular expression
https://docs.splunk.com/Documentation/Splunk/8.0.0/Knowledge/AboutSplunkregularexpressions

0 Karma

matoulas
Path Finder

Another thing that I found out when using the above code my result for all plug current and voltage is not correct.

0 Karma

matoulas
Path Finder

Hi,

I take it back got it to work now by using your strategy above code.
Once last thing, Base on my work.jpg, I would like to show or display all Legend Label in splunk bar chart dashboard, as of now only show up to 9 items on the legend. Is there away to display all legend label?

0 Karma

matoulas
Path Finder

If I performed my search like this is working

index="main"
| rex mode=sed "s/plug(\d)/plug/g"
| spath output=pname path=powerdata{}.plug{}.plugname
| spath
| timechart max(powerdata{}.plug{}.current) as max_current by powerdata{}.plug{}.plugname

alt text

but I would like to filter my search base on dropdown selection with the token as siteid=$siteid$ and location=$location$
if I want to perform my search as the below. It said no result found in dasboard.

| search siteid=$siteid$ location=$location$
| rex mode=sed "s/plug(\d)/plug/g"
| spath output=pname path=powerdata{}.plug{}.plugname
| spath
| timechart max(powerdata{}.plug{}.current) as max_current by powerdata{}.plug{}.plugname

alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...