How to split stats values() into other rows?

[bofasplunkguy]

I am trying to show a "primary" and "secondary" IP in rows to recreate a spreadsheet. I currently have a search like:

search query | stats values (IP) as IPs by user

This will return all of my users, with the corresponding IPs. Some users have only one, while others also have a secondary. I would like the primary and secondary to be separate columns, rather than having both combined in a single cell. I was trying to use rex to separate them, but my fields come back empty:

|rex field=IPs "(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})[\r\n]"(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"
|table user primary secondary

I think the issue is with the linebreak [r/n] as I can extract just the primary this way. Please advise how to split the stats values into separate rows, either using regex or if there is a better way.

[Habanero]

Hello @bofasplunkguy,

I am in the same predicament as yours. Did you ever find an answer to your problem?

[DanielPi]

Hi @Habanero,

I’m a Community Moderator in the Splunk Community.

This question was posted 5 years ago, so it might not get the attention you need for your question to be answered. We recommend that you post a new question so that your issue can get the  visibility it deserves. To increase your chances of getting help from the community, follow these guidelines in the Splunk Answers User Manual when creating your post.

Thank you! 

[Vijeta]

You can try mvexpand-

search query | stats values (IP) as IPs by user | mvexpand IPs

[bofasplunkguy]

mvexpand breaks the values back into separate rows, which is how they already are before the stats values() command.

I want these in separate columns, not separate rows. Does that make sense? I want to end up with a table with three columns like:

| table user primary secondary