How to split by comma and use values as field names?

[JykkeDaMan]

I have the following fields, where some of them might be null, empty, whatnot values.

I would like to split the Services values, which might have 1-N values separated by a comma, to separate columns/fields prefixed with "Sp.".

For example:

| makeresults 
| eval Platform="p1", Ent="ent1", Ext="100", Fieldx=null(), Fieldy="" , Services="user,role,func1,func2" 
| append [
| makeresults 
| eval Platform="p1", Ent="ent2", Ext="100", Fieldx="", Fieldy=null(), Services="user2,role2,func4,func8,func5,role3"
]
| fields _time Platform Ent Ext Fieldx Fieldy Services

Gives an example like:

_time	Platform	Ent	Ext	Filedx	Fieldy	Services
2022-09-30 08:56:11	p1	ent1	100			user,role,func1,func2
2022-09-30 08:56:11	p1	ent2	100			user2,role2,func4,func8,func5,role3

How do I split the Services into a separate fields?

I think I cannot just use stats list() by "All_fields" due to those possible null values in other fields.

_time

Platform

Ent

Ext

Fieldx

Fieldy

Services

Sp.func1

Sp.func2

Sp.func4

Sp.func5

Sp.func8

Sp.role

Sp.role2

Sp.role3

Sp.user

Sp.user2

2022-09-30 09:07:00

p1

ent1

100

user,role,func1,func2

func1

func2

role

user

2022-09-30 09:07:00

p1

ent2

100

user2,role2,func4,func8,func5,role3

func4

func5

func8

role2

role3

user2

[JykkeDaMan]

Indeed, stats(*) as * by <field_always_exist> :-).

[ITWhisperer]

| eval service=split(Services,",")
| streamstats count as _row
| mvexpand service
| eval {service}=service
| stats values(*) as * by _row
| fields - _row service

[kamlesh_vaghela · Accepted solution]

@JykkeDaMan 

Can you please try this?

| makeresults 
| eval Platform="p1", Ent="ent1", Ext="100", Fieldx=null(), Fieldy="" , Services="user,role,func1,func2" 
| append 
    [| makeresults 
    | eval Platform="p1", Ent="ent2", Ext="100", Fieldx="", Fieldy=null(), Services="user2,role2,func4,func8,func5,role3"
        ] 
| fields _time Platform Ent Ext Fieldx Fieldy Services | eval a=1 | accum a
| eval Services_mv = split(Services,",")
| mvexpand Services_mv
| eval Sp.{Services_mv}=Services_mv
|fields - Services_mv
|stats values(*) as * by a
|fields - a

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.