topic How to reference transposed column::row value in Dashboards & Visualizations

How to reference transposed column::row value

Cuyose — Tue, 04 Apr 2017 16:31:25 GMT

I have a panel in a dashboard that parses xml, and displays them with transpose. This is a head 1, so there will only ever be one row. I want to set a token based on the row 1 value of column=ActivityId for use in another panel.

I suppose I can create a redundant hidden search associated using that query without the transpose to assign that token, but I would like to just see if I can set it all within that panel xml in the dashboard.

base search
| head 1 
| rex field=_raw "(?is)request=(?<xmlData>.+)$" 
| fields xmlData 
| xmlkv xmlData 
| fields - _* xml* 
| transpose 
| rename column as KEY, "row 1" as VALUE 
| sort KEY

This gives me the clean table of key value pairs, and I want to use the VALUE of KEY=ActivityId. Then use it like this in my dashboard

         <done>
            <set token="activity_id">$result.ActivityId$</set>
          </done>

Re: How to reference transposed column::row value

DalJeanis — Tue, 04 Apr 2017 17:31:19 GMT

Yes, you can do that, and you're pretty close to the right XML. Look at the example in the accepted answer here, which uses finalized rather than done, for the same effect.

https://answers.splunk.com/answers/442254/how-to-set-a-token-from-a-base-search-in-my-dashbo.html

Re: How to reference transposed column::row value

Cuyose — Tue, 04 Apr 2017 17:47:34 GMT

I can get the xml to work, the issue I am having is that I cant reference the transposed values. so activity_id is set to the literal string $result.ActivityId$ because a field ActivityId doesn't exist after the transpose

Re: How to reference transposed column::row value

somesoni2 — Tue, 04 Apr 2017 18:31:21 GMT

Try to change the your panel search like this

base search
 | head 1 
 | rex field=_raw "(?is)request=(?<xmlData>.+)$" 
 | fields xmlData 
 | xmlkv xmlData 
 | fields - _* xml* 
 | transpose 
 | rename column as KEY, "row 1" as VALUE 
 | eval KEY=if(KEY="ActivityId","1.ActivityId",KEY)
 | sort KEY
 | eval KEY=if(KEY="1.ActivityId","ActivityId",KEY)

And use this to set the token

<set token="activity_id">$result.VALUE$</set>

Basically make the row with ActivityID as the first row so that $result.VLAUE$ will contain value corresponding to ActivityID.

Re: How to reference transposed column::row value

Cuyose — Tue, 04 Apr 2017 19:28:54 GMT

Ahh, clever. I see now. Basically you are making sure that the key you want the value for is first in the list, then reset its displayed KEY.

This solved the issue in an unexpected way. If I wanted to set 2 tokens instead of just one, would this be possible? Like setting what you want with a 1.KEY, 2.KEY, etc. The problem I see then is assigning it to a token. since that is $result.VALUE$ it would be cool to do $result.1.VALUE$ $result.2.VALUE$ etc

Re: How to reference transposed column::row value

woodcock — Tue, 04 Apr 2017 20:27:21 GMT

Another trick is that you can copy this to an otherwise invisible field beginning with an underscore (such as _key) which means that the data will not be visible inside of your visualization but will be accessible by reference.