Dashboards & Visualizations

How to reference transposed column::row value

Cuyose
Builder

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>
Tags (3)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

Cuyose
Builder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

Cuyose
Builder

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

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