Split and create new row

[dashield]

I do have a CSV file that consist of below column

• EventName
• Start Time
• Username
• severity
• alertid

The data on the alertid became a list when user assigned multiple alert. 

Challenge:

How to separate the list from alertid, create a new entry each and copy the same value of the remaining column.

Below are the sample entry of CSV file.

Event Name,Start Time,Username,severity,alertid
"alert assigned","1617229938497","sampleuser","5","82574,82573,82572,82569,82568,82567"

------------------

Desired result.

Event Name,Start Time,Username,severity,alertid
"alert assigned","1617229938497","sampleuser","5","82574"
"alert assigned","1617229938497","sampleuser","5","82573"
"alert assigned","1617229938497","sampleuser","5","82572"
"alert assigned","1617229938497","sampleuser","5","82569"
"alert assigned","1617229938497","sampleuser","5","82568"
"alert assigned","1617229938497","sampleuser","5","82567"

[tscroggins · Accepted solution]

@dashield 

It's a two-step process: 1) convert alertid to a multi-valued field and 2) expand alertid into multiple events, copying single-valued field values into each new event:

| inputlookup foo.csv
| eval alertid=split(alertid, ",")
| mvexpand alertid

To reverse the operation:

| stats values(alertid) as alertid by "Event Name" "Start Time" Username severity
| eval alertid=mvjoin(alertid, ",")

[dashield]

It worked exactly looking for. Thank you.