topic Split pattern into multiple rows? in Splunk Search

Split pattern into multiple rows?

LearningGuy — Fri, 14 Jul 2023 19:04:11 GMT

Split pattern in CSV lookup format into multiple rows
| lookup table.csv
Note that the number of SubnetID-IP pair is not always 4. It could be from 1 to 4 or more (1... N)
The example below is only one row, but the real data has multiple rows (eg. Comp-B to Comp-Z)

Before the split:
Company Location SubnetID-IP
Comp-A Loc- A [{"subnet_id":"101","ip_address":"192.168.1.1"},{"subnet_id":"102","ip_address":"192.168.1.2"},{"subnet_id":"103","ip_address":"192.168.1.3"},{"subnet_id":"104","ip_address":"fd12:3456:789a:1::1"}]

After the split:

Company Location IP subnetID
Comp-A Loc-A 192.168.1.1 101
Comp-A Loc-A 192.168.1.2 102
Comp-A Loc-A 192.168.1.3 103
Comp-A Loc-A fd12:3456:789a:1::1 104

Thank you so much for your help

Re: Split pattern into multiple rows

yuanliu — Thu, 13 Jul 2023 23:48:01 GMT

spath is your fiend.

| spath input=Subnet-IP path={} | mvexpand {} | spath input={}

Your sample data gives

Company	Location	ip_address	subnet_id	{}
Comp-A	Loc-A	192.168.1.1	101	{"subnet_id":"101","ip_address":"192.168.1.1"}
Comp-A	Loc-A	192.168.1.2	102	{"subnet_id":"102","ip_address":"192.168.1.2"}
Comp-A	Loc-A	192.168.1.3	103	{"subnet_id":"103","ip_address":"192.168.1.3"}
Comp-A	Loc-A	fd12:3456:789a:1::1	104	{"subnet_id":"104","ip_address":"fd12:3456:789a:1::1"}

This is an emulation that you can play with and compare to your real data

| makeresults | eval Company = "Comp-A", Location = "Loc-A", Subnet-IP = "[{\"subnet_id\":\"101\",\"ip_address\":\"192.168.1.1\"},{\"subnet_id\":\"102\",\"ip_address\":\"192.168.1.2\"},{\"subnet_id\":\"103\",\"ip_address\":\"192.168.1.3\"},{\"subnet_id\":\"104\",\"ip_address\":\"fd12:3456:789a:1::1\"}]"

Re: Split pattern into multiple rows

LearningGuy — Fri, 14 Jul 2023 18:03:45 GMT

Your first suggestion worked. Thank you so much

| spath input=Subnet-IP path={}
| mvexpand {}
| spath input={}

I ran your second suggestion and received this "Error in 'makeresults' command: This command must be the first command of a search."
What is the purpose of the following commands?
Note that my search is using | inputlookup test.csv. It looks like makeresults didn't work with inputlookup. Thank you

| makeresults
| eval Company = "Comp-A", Location = "Loc-A", Subnet-IP = "[{\"subnet_id\":\"101\",\"ip_address\":\"192.168.1.1\"},{\"subnet_id\":\"102\",\"ip_address\":\"192.168.1.2\"},{\"subnet_id\":\"103\",\"ip_address\":\"192.168.1.3\"},{\"subnet_id\":\"104\",\"ip_address\":\"fd12:3456:789a:1::1\"}]"

Re: Split pattern into multiple rows

yuanliu — Sat, 15 Jul 2023 05:32:43 GMT

The makeresults block is meant to emulate inputlookup as a way of comparison in case the solution gives unexpected output.