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
SplunkTrust
SplunkTrust
| 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

View solution in original post

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