topic Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns in Splunk Search

(Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 11:35:26 GMT

Hi all,

I'm trying to dynamically add columns to two fixed columns based on the environment value selected. For instance, this is the input data:

Environment	Application	CatridgeType	Cartridge	Version
DEV	A-1	User	Alpha	1.1
DEV	A-2	Product	Beta	1.2
UAT	A-1	User	Alpha	1.2
SVP	A-1	User	Alpha	1.4
SVP	A-1	User	Sigma	1.5
SVP	A-2	Product	Beta	1.2
SVP	A-3	System	Gamma	1.5

And I would like to create a table such as the following:

CartridgeType	Cartridge	DEV:A-1	DEV:A-2
User	Alpha	1.1
Product	Beta		1.2

Some key things to note:

- The first two columns should stay constant, however depending on the environment value selected in the search (e.g. Environment="DEV"), the environment value should be combined with the 'Application' value to create another column, in which the values are the corresponding 'Version' value. The tricky party is making the fields after "Cartridge" dynamic, for instance, if Environment="SVP", I would expect the following:

CartridgeType	Cartridge	SVP:A-1	SVP:A-2	SVP:A-3
User	Alpha	1.4
User	Sigma	1.5
Product	Beta		1.2
System	Gamma			1.5

Is this possible to do whilst making to only show the latest version value?

Thank you so much for any help!

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 11:50:39 GMT

| eval {Environment}:{Application}='Version'

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 12:01:12 GMT

Hi @cmtri
Thanks for your answer but I'm still a little confused. The following doesn't seem to be working?

index="main" sourcetype="data"
| eval {Environment}:{Application}="Version"
| table CartridgeType Cartridge {Environment}:{Application}

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 12:12:56 GMT

Version should be surrounded in single-quotes (') rather than double-quotes (") to use the value of the Version field. You don't need these single-quotes, so you can remove them if you prefer. If you also table out and then eval the fields after they will automatically appear in the table, so the below should work:

index="main sourcetype="data"

| table CartridgeType, Cartridge

| eval {Environment}:{Application}='Version'

The second line is also valid like below (no quotes):

| eval {Environment}:{Application}=Version

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 12:20:28 GMT

The table is only showing the CartridgeType, Cartridge fields however isn't showing the dynamic fields after it.

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 12:24:39 GMT

Sorry, you need to bring the two fields you're using into the table, then drop them later to do it like that!

You can then use the fields command to remove them from the table.

index="main sourcetype="data"

| table CartridgeType, Cartridge, Environment, Application

| eval {Environment}:{Application}=Version

| fields - Environment, Application

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 12:30:30 GMT

Still is showing only CartridgeType and Cartridge fields.

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 12:37:02 GMT

You can try the below to see a full search:

| makeresults 1
| eval CartridgeType="User"
| eval Cartridge="Alpha"
| eval Environment="DEV"
| eval Application="A-1"
| eval Version="1.1"
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version

This should generate a table like below:

CartridgeType	Cartridge	DEV:A-1
User	Alpha	1.1

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 12:40:07 GMT

This seems to work! Seems like the issue was not including version in the table clause.

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 13:23:22 GMT

Hi @cmtri Thought it was working, but there is slightly one more issue.

This is what I entered,

| makeresults | eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,User,Sigma,1.5
SVP,A-2,Product,Beta,1.2
SVP,A-3,System,Gamma,1.5
SVP,A-2,User,Alpha,1.5" | multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version

Line 1 and 5 of the resulting table should be combined together - meaning that since it has the same cartridgetype and cartridge, it should be on one line. However, the result is producing it as seperate rows. Also, thanks for your help so far!

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 13:32:15 GMT

This stats values command should do it!

| makeresults
| eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,User,Sigma,1.5
SVP,A-2,Product,Beta,1.2
SVP,A-3,System,Gamma,1.5
SVP,A-2,User,Alpha,1.5"
| multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version
| stats values by CartridgeType, Cartridge

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

cmtri — Mon, 16 Aug 2021 13:36:17 GMT

And use this to make the columns the original names rather than the values(abc) result:

| rename values(*) as *

Thanks! If this solves it please mark as answered.

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 13:36:41 GMT

thank you, we are so close! @cmtri

Your solution solves the line issue however now each of the dynamic columns have 'values' at the front: E.g.
'values(SVP:A-1)'

. Is there a way to remove the brackets and the 'values' prefix, so it goes back to being 'SVP:A-1' and so on for all the dynamically created columns?

Re: (Table) Add dynamically generated columns (from field values) onto pre-existing columns

shakSplunk — Mon, 16 Aug 2021 13:37:30 GMT

too fast, cheers!