topic Re: Expand the multivalue fields in Splunk Search

Expand the multivalue fields

RSS_STT — Thu, 18 Jan 2024 14:08:47 GMT

Trying to expand the multivalue field with one to one mapping as shown in image.

mvexpand create multiple row with all column matching value.

Actual data with multivalue.

child	child_Name	dv_class	n_name	direction	name	parent
55555

Re: Expand the multivalue fields

gcusello — Wed, 17 Jan 2024 08:20:22 GMT

Hi @RSS_STT,

I suppose that your search is something like this:

<your-search> | stats values(child_Name) AS child_Name values(dv_u_parent_class) AS dv_u_parent_class values(fqdn_name) AS fqdn_name values(direction) AS direction values(name) AS name values(parent) AS parent BY child

You shoud try something like this:

<your-search> | stats values(fqdn_name) AS fqdn_name values(name) AS name BY child child_Name dv_u_parent_class direction parent

I could be more detailed if you can share your search.

Ciao.

Giuseppe

Re: Expand the multivalue fields

RSS_STT — Wed, 17 Jan 2024 08:33:25 GMT

Yes, I was trying the first query shared by you previously but second query shared by you also creating 96 record where i'm expecting only 4 record.

Re: Expand the multivalue fields

gcusello — Wed, 17 Jan 2024 08:44:09 GMT

Hi @RSS_STT,

probably there are more combinations of your values, not only 4.

You have two solutions: use less fields as keys in the stats command, but you'll have some filed with multivalue.

Otherwise you should identify some rules to filter your results.

Anyway, the only way to have only one value in a stats command is to put it in the BY clause.

There's also another solution, but in this way you loose some results: instead values, you could use the first option, taking only one value for each: I don't know (but I don't think) that this is acceptable for you!

Ciao.

Giuseppe

Re: Expand the multivalue fields

ITWhisperer — Wed, 17 Jan 2024 08:44:29 GMT

It is not clear how you arrived at your current state, and it might be easier to solve with some sight of your current search and events (as @gcusello has indicated), however, assuming you still want to go forward from where you seem to be, you could try something like this:

Re: Expand the multivalue fields

PickleRick — Wed, 17 Jan 2024 12:30:55 GMT

As @gcusello already noticed, you probably used a lot of values() in your stats.

As you can see yourself, it results with a list of multivalued fields. The problem with that is that each of those multivalued fields is a separate entity and there is no relationship between them. So you can reliably split those values into single rows unless you have a very very strong guarantee for some properties of your data.

For example, for source data like

field1	field2	field3
1	2
1		3
1	4	5
1	6
1	8
1		9
1		11

You'd get a result like this:

values(field1)	values(field2)	values(field3)
1	2 4 6 8	3 5 9 11

If you tried "unpacking" it assuming that the values from the two latter colums match 1-1 you'd get completely ridiculous results.

Re: Expand the multivalue fields

RSS_STT — Thu, 18 Jan 2024 06:48:21 GMT

it's possible to write partial data in to lookup file on base on some condition.

Like...

if dv_u_parent_class = ci_appld than outputlookup append=true abc.csv

|where dv_u_parent_class != ci_appld

and run time query should show rest two events.

child	child_Name	dv_u_parent_class	fqdn_name	direction	name	parent
55555	xyz PROD	ci_appld	xyz.srv.com	R toY	xyz	111111
55555	abc PROD	ci_appld	xyz.srv.com	R to Y	xyz	222222
55555	zzzz-FSE2	ci_netcom	xyz.srv.com	Y to R	xyz	333333
55555	abc.srv.com	ci_esx_app	xyz.srv.com	Y to R	xyz	444444

Re: Expand the multivalue fields

gcusello — Thu, 18 Jan 2024 06:58:59 GMT

Hi @RSS_STT ,

as I said, you can use my search and after use some conditions to filter results.

Ciao.

Giuseppe

Re: Expand the multivalue fields

RSS_STT — Thu, 18 Jan 2024 08:04:30 GMT

Im able to get that result but not getting logic to write filter data into lookup and later drop the event which written to lookup.

Re: Expand the multivalue fields

ITWhisperer — Thu, 18 Jan 2024 09:57:37 GMT

Logic that you could use could go something like this

<your search> | appendpipe [| where <conditions for events you want to output> | outputlookup <your csv> | where false() ``` This removes all the events so that they are not appended to your main event pipeline ``` ] | where <conditions for events you want to keep i.e. not the events you wrote to the csv>