Looking for regex help in parsing data from curl output

[bowesmana]

I'm using curl in Spluk to download some data from an API and to build a lookup of the downloaded data. The data comes back as a single field value (curl_message). The first line is the effect field list for the lookup I am going to create and then there is data, of which one field MAY be multi-line. So in this example

iNote	iWine	Type	iUser	Vintage	Wine	SortWine	Locale	Producer	Varietal	MasterVarietal	Designation	Vineyard	Country	Region	SubRegion	Appellation	TastingDate	Defective	fAllowComments	Views	Name	fHelpful	fFavorite	Rating	EventLocation	EventTitle	iEvent	EventDate	EventEndDate	TastingNotes	fLikeIt	CNotes	CScore	LikeVotes	LikePercent	Votes	Comments	cLabels
9537078	1519682	Red	94404	2012	Wolf Blass Black Label	Wolf Blass Black Label	Australia, South Australia	Wolf Blass	Cabernet-Shiraz Blend	Red Blend	Black Label	Unknown	Australia	South Australia	Unknown	Unknown	11/27/2021	False	True	17	username		0	93							True	4	92	2	1	0	0	237
9537066	2452851	White	94404	2014	Xanadu Chardonnay Reserve Margaret River	Xanadu Chardonnay Reserve Margaret River	Australia, Western Australia, South West Australia, Margaret River	Xanadu	Chardonnay	Chardonnay	Reserve	Unknown	Australia	Western Australia	South West Australia	Margaret River	11/3/2021	False	True	23	username		0	95						Seems to be improving. A perfect accompaniment to prosciutto	True	6	92.8333333333333	6	1	0	0	35
9516281	2778467	White	94404	2016	Weingut Thörle Saulheimer Hölle Riesling trocken	Thörle, Weingut Saulheimer Hölle Riesling trocken	Germany, Rheinhessen	Weingut Thörle	Riesling	Riesling	trocken	Saulheimer Hölle	Germany	Rheinhessen	Unknown	Unknown	11/28/2021	False	True	135	username		0	93						Paired well with Barramundi and sweet potato fries

Colour: Pale gold
Nose: Medium
P: Lemon rind, peach, orange
S: cream
T: honey
Palate: Dry, high acidity, medium alcohol, full bodied, pronounced flavour, medium finish
P: pear, peach, lemon
S: bread
T: nutmeg, caramel
B: 1
L: .5
I: .5
C: 1
Very good wine	True	5	91	3	1	0	0	45
9431031	3300231	Red	94404	2017	Girolamo Russo Etna 'a Rina	Girolamo Russo Etna 'a Rina	Italy, Sicily, Etna DOC	Girolamo Russo	Nerello Blend	Nerello Mascalese	'a Rina	Unknown	Italy	Sicily	Unknown	Etna DOC	10/2/2021	False	True	65	username		0	93							True	50	90.2888888888889	28	0.964285714285714	0	0	378
9431030	3580970	Red	94404	2019	Swinging Bridge Shiraz William J.	Swinging Bridge Shiraz William J.	Australia, New South Wales, Central Ranges, Orange	Swinging Bridge	Shiraz	Syrah	William J.	Unknown	Australia	New South Wales	Central Ranges	Orange	9/11/2021	False	True	0	username		0	92							True	1	92	1	1	0	0	10
Primary Black cherry, liquorice, dried herbs, black pepper, black olive, blackberry
9431025	3157557	Red - Sparkling	94404	2008	Seppelt Shiraz Show Sparkling Great Western	Seppelt Shiraz Show Sparkling Great Western	Australia, Victoria, Western Victoria, Great Western	Seppelt	Shiraz	Syrah	Show Sparkling	Unknown	Australia	Victoria	Western Victoria	Great Western	10/15/2021	False	True	252	username		0	95						Deep ruby, pronounced nose, lots of jammy red and black fruits, bubbles washing inside the mouth, filling the mouth with flavour. The finish lingering forever.	True	5	93.25	3	1	0	0	28

there is the header (starting iNote...) and then 6 data 'rows' that need to be expanded. I have used

| makemv tokenizer="(.*)\n" curl_message

but when the 'TastingNotes' field is multi-line, as in the line starting 9516281, then of course that fails to extract the multi-line value. In that case, the tasting note should be from (Paired well... to Very good wine)

I have tried playing with rex and max_match=0. I know that a valid line starts with a (currently) 7 digit number, so I know I will never have that in the tasting note text, but I can't figure out what the correct regex might be.

I got as far as 

(?s)((iNote|^\d{7}).*?)^\d{7}

but don't know how to exclude the end match part, which is the start of the next entry and in any case that doesn't work as the tokenizer regex

I don't want the data to go to an index, so I could write a scripted input that get the data and uses sed/awk to break out the events then REST API to create the lookup, but that seems like overkill.

[bowesmana]

OK, I think I figured it out - it was original a tab delimited input, but I changed it to csv and used this

| rex field=curl_message max_match=0 "(?<row>(\"[^\"]*\",?){39})"
| fields row 
| mvexpand row
| rex field=row max_match=0 "(?s)\"(?<x>[^\"]*)\",?"
| fields x
| eval iNote=mvindex(x,0,0),
iWine=mvindex(x,1,1),
...

using mvindex for each of the 39 fields.

Any other suggestions welcome

[ITWhisperer · Accepted solution]

You could do something like this (I have made some assumptions about your data e.g. Primary black cherry ... didn't seem to be in the right place)

| makeresults
| eval curl_message="\"iNote\",\"iWine\",\"Type\",\"iUser\",\"Vintage\",\"Wine\",\"SortWine\",\"Locale\",\"Producer\",\"Varietal\",\"MasterVarietal\",\"Designation\",\"Vineyard\",\"Country\",\"Region\",\"SubRegion\",\"Appellation\",\"TastingDate\",\"Defective\",\"fAllowComments\",\"Views\",\"Name\",\"fHelpful\",\"fFavorite\",\"Rating\",\"EventLocation\",\"EventTitle\",\"iEvent\",\"EventDate\",\"EventEndDate\",\"TastingNotes\",\"fLikeIt\",\"CNotes\",\"CScore\",\"LikeVotes\",\"LikePercent\",\"Votes\",\"Comments\",\"cLabels\"
\"9537078\",\"1519682\",\"Red\",\"94404\",\"2012\",\"Wolf Blass Black Label\",\"Wolf Blass Black Label\",\"Australia, South Australia\",\"Wolf Blass\",\"Cabernet-Shiraz Blend\",\"Red Blend\",\"Black Label\",\"Unknown\",\"Australia\",\"South Australia\",\"Unknown\",\"Unknown\",\"11/27/2021\",\"False\",\"True\",\"17\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"\",\"True\",\"4\",\"92\",\"2\",\"1\",\"0\",\"0\",\"237\"
\"9537066\",\"2452851\",\"White\",\"94404\",\"2014\",\"Xanadu Chardonnay Reserve Margaret River\",\"Xanadu Chardonnay Reserve Margaret River\",\"Australia, Western Australia, South West Australia, Margaret River\",\"Xanadu\",\"Chardonnay\",\"Chardonnay\",\"Reserve\",\"Unknown\",\"Australia\",\"Western Australia\",\"South West Australia\",\"Margaret River\",\"11/3/2021\",\"False\",\"True\",\"23\",\"username\",\"\",\"0\",\"95\",\"\",\"\",\"\",\"\",\"\",\"Seems to be improving. A perfect accompaniment to prosciutto\",\"True\",\"6\",\"92.8333333333333\",\"6\",\"1\",\"0\",\"0\",\"35\"
\"9516281\",\"2778467\",\"White\",\"94404\",\"2016\",\"Weingut Thörle Saulheimer Hölle Riesling trocken\",\"Thörle, Weingut Saulheimer Hölle Riesling trocken\",\"Germany, Rheinhessen\",\"Weingut Thörle\",\"Riesling\",\"Riesling\",\"trocken\",\"Saulheimer Hölle\",\"Germany\",\"Rheinhessen\",\"Unknown\",\"Unknown\",\"11/28/2021\",\"False\",\"True\",\"135\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"Paired well with Barramundi and sweet potato fries

Colour: Pale gold
Nose: Medium
P: Lemon rind, peach, orange
S: cream
T: honey
Palate: Dry, high acidity, medium alcohol, full bodied, pronounced flavour, medium finish
P: pear, peach, lemon
S: bread
T: nutmeg, caramel
B: 1
L: .5
I: .5
C: 1
Very good wine\",\"True\",\"5\",\"91\",\"3\",\"1\",\"0\",\"0\",\"45\"
\"9431031\",\"3300231\",\"Red\",\"94404\",\"2017\",\"Girolamo Russo Etna 'a Rina\",\"Girolamo Russo Etna 'a Rina\",\"Italy, Sicily, Etna DOC\",\"Girolamo Russo\",\"Nerello Blend\",\"Nerello Mascalese\",\"'a Rina\",\"Unknown\",\"Italy\",\"Sicily\",\"Unknown\",\"Etna DOC\",\"10/2/2021\",\"False\",\"True\",\"65\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"\",\"True\",\"50\",\"90.2888888888889\",\"28\",\"0.964285714285714\",\"0\",\"0\",\"378\"
\"9431030\",\"3580970\",\"Red\",\"94404\",\"2019\",\"Swinging Bridge Shiraz William J.\",\"Swinging Bridge Shiraz William J.\",\"Australia, New South Wales, Central Ranges, Orange\",\"Swinging Bridge\",\"Shiraz\",\"Syrah\",\"William J.\",\"Unknown\",\"Australia\",\"New South Wales\",\"Central Ranges\",\"Orange\",\"9/11/2021\",\"False\",\"True\",\"0\",\"username\",\"\",\"0\",\"92\",\"\",\"\",\"\",\"\",\"\",\"Primary Black cherry, liquorice, dried herbs, black pepper, black olive, blackberry\",\"True\",\"1\",\"92\",\"1\",\"1\",\"0\",\"0\",\"10\"
\"9431025\",\"3157557\",\"Red - Sparkling\",\"94404\",\"2008\",\"Seppelt Shiraz Show Sparkling Great Western\",\"Seppelt Shiraz Show Sparkling Great Western\",\"Australia, Victoria, Western Victoria, Great Western\",\"Seppelt\",\"Shiraz\",\"Syrah\",\"Show Sparkling\",\"Unknown\",\"Australia\",\"Victoria\",\"Western Victoria\",\"Great Western\",\"10/15/2021\",\"False\",\"True\",\"252\",\"username\",\"\",\"0\",\"95\",\"\",\"\",\"\",\"\",\"\",\"Deep ruby, pronounced nose, lots of jammy red and black fruits, bubbles washing inside the mouth, filling the mouth with flavour. The finish lingering forever.\",\"True\",\"5\",\"93.25\",\"3\",\"1\",\"0\",\"0\",\"28\""
| rex field=curl_message max_match=0 "(?<row>(\"[^\"]*\",?){39})"
| fields row 
| mvexpand row
| rex field=row max_match=0 "(?s)\"(?<x>[^\"]*)\",?"
| fields x
| fields - _time
| streamstats count as row 
| eval fields=if(row==1,x,null())
| filldown fields
| where row>1
| eval namevalue=mvzip(fields,x,"=")
| fields namevalue row
| mvexpand namevalue
| eval _name=mvindex(split(namevalue,"="),0)
| eval _value=mvindex(split(namevalue,"="),1)
| eval {_name}=_value
| fields - namevalue _name _value
| stats values(*) as * by row

[bowesmana]

Nice @ITWhisperer I was wondering how to push the titles down so I didn't have to hard code the mvindex offsets. I rarely use filldown, but this is a perfect use case.

Here's a sample of the data when collecting it as CSV, not tab delimeted. I will need to validate whether I am ever using commas in the data and maybe revisit the tab variant.  (EDIT: Yes I am but the created lookup contains quoted field data, so commas are not treated as field delimeters)

Neat technique for row association with the streamstats to anchor the data before the mvepand.

Thanks!

\"iNote\",\"iWine\",\"Type\",\"iUser\",\"Vintage\",\"Wine\",\"SortWine\",\"Locale\",\"Producer\",\"Varietal\",\"MasterVarietal\",\"Designation\",\"Vineyard\",\"Country\",\"Region\",\"SubRegion\",\"Appellation\",\"TastingDate\",\"Defective\",\"fAllowComments\",\"Views\",\"Name\",\"fHelpful\",\"fFavorite\",\"Rating\",\"EventLocation\",\"EventTitle\",\"iEvent\",\"EventDate\",\"EventEndDate\",\"TastingNotes\",\"fLikeIt\",\"CNotes\",\"CScore\",\"LikeVotes\",\"LikePercent\",\"Votes\",\"Comments\",\"cLabels\"
\"9537078\",\"1519682\",\"Red\",\"94404\",\"2012\",\"Wolf Blass Black Label\",\"Wolf Blass Black Label\",\"Australia, South Australia\",\"Wolf Blass\",\"Cabernet-Shiraz Blend\",\"Red Blend\",\"Black Label\",\"Unknown\",\"Australia\",\"South Australia\",\"Unknown\",\"Unknown\",\"27/11/2021\",\"False\",\"True\",\"17\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"\",\"True\",\"4\",\"92\",\"2\",\"1\",\"0\",\"0\",\"237\"
\"9537066\",\"2452851\",\"White\",\"94404\",\"2014\",\"Xanadu Chardonnay Reserve Margaret River\",\"Xanadu Chardonnay Reserve Margaret River\",\"Australia, Western Australia, South West Australia, Margaret River\",\"Xanadu\",\"Chardonnay\",\"Chardonnay\",\"Reserve\",\"Unknown\",\"Australia\",\"Western Australia\",\"South West Australia\",\"Margaret River\",\"3/11/2021\",\"False\",\"True\",\"23\",\"username\",\"\",\"0\",\"95\",\"\",\"\",\"\",\"\",\"\",\"Seems to be improving. A perfect accompaniment to prosciutto\",\"True\",\"6\",\"92.8333333333333\",\"6\",\"1\",\"0\",\"0\",\"35\"
\"9516281\",\"2778467\",\"White\",\"94404\",\"2016\",\"Weingut Thörle Saulheimer Hölle Riesling trocken\",\"Thörle, Weingut Saulheimer Hölle Riesling trocken\",\"Germany, Rheinhessen\",\"Weingut Thörle\",\"Riesling\",\"Riesling\",\"trocken\",\"Saulheimer Hölle\",\"Germany\",\"Rheinhessen\",\"Unknown\",\"Unknown\",\"28/11/2021\",\"False\",\"True\",\"138\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"Paired well with Barramundi and sweet potato fries

Colour: Pale gold
Nose: Medium
P: Lemon rind, peach, orange
S: cream
T: honey
Palate: Dry, high acidity, medium alcohol, full bodied, pronounced flavour, medium finish
P: pear, peach, lemon
S: bread
T: nutmeg, caramel
B: 1
L: .5
I: .5
C: 1
Very good wine\",\"True\",\"5\",\"91\",\"3\",\"1\",\"0\",\"0\",\"45\"
\"9478838\",\"1973540\",\"White - Sweet/Dessert\",\"94404\",\"2008\",\"Telmo Rodríguez Moscatel Málaga Molino Real\",\"Rodríguez, Telmo Moscatel Málaga Molino Real\",\"Spain, Andalucía, Málaga\",\"Telmo Rodríguez\",\"Moscatel\",\"Muscat\",\"Molino Real\",\"Unknown\",\"Spain\",\"Andalucía\",\"Unknown\",\"Málaga\",\"14/11/2021\",\"False\",\"True\",\"47\",\"username\",\"\",\"0\",\"91\",\"\",\"\",\"\",\"\",\"\",\"Beautiful colour. As LUXURYSOTOGRANDE says, great with meringue. Eaten with Eton mess - great pairing.
^
Colour: Medium amber
Nose: Pronounced
Primary: Floral, honey, sultanas, dried fruit
Tertiary: honey

Palate: 
Sweetness: Medium
Acid: Medium
Alcohol: Medium
Body: Full
Intensity: Pronounced
Primary:
Secondary:
Tertiary:
Finish: long
BLIC:1+1+1+.5=3.5(very good)\",\"True\",\"3\",\"92.3333333333333\",\"3\",\"1\",\"0\",\"0\",\"27\"
\"9431031\",\"3300231\",\"Red\",\"94404\",\"2017\",\"Girolamo Russo Etna 'a Rina\",\"Girolamo Russo Etna 'a Rina\",\"Italy, Sicily, Etna DOC\",\"Girolamo Russo\",\"Nerello Blend\",\"Nerello Mascalese\",\"'a Rina\",\"Unknown\",\"Italy\",\"Sicily\",\"Unknown\",\"Etna DOC\",\"2/10/2021\",\"False\",\"True\",\"65\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"\",\"True\",\"50\",\"90.2888888888889\",\"28\",\"0.964285714285714\",\"0\",\"0\",\"378\"
\"9431029\",\"2748345\",\"Red\",\"94404\",\"2013\",\"Vasarelli Cabernet Franc Family Reserve\",\"Vasarelli Cabernet Franc Family Reserve\",\"Australia, South Australia, Fleurieu, McLaren Vale\",\"Vasarelli\",\"Cabernet Franc\",\"Cabernet Franc\",\"Family Reserve\",\"Unknown\",\"Australia\",\"South Australia\",\"Fleurieu\",\"McLaren Vale\",\"9/10/2021\",\"False\",\"True\",\"30\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"This was a real surprise, having languished in the cellar, but it really benefited from the bottle age. Wish I had more e\",\"True\",\"3\",\"91.6666666666667\",\"2\",\"1\",\"0\",\"0\",\"1\"
\"9430768\",\"2665536\",\"Red\",\"94404\",\"2016\",\"Bass Phillip Gamay\",\"Bass Phillip Gamay\",\"Australia, Victoria, Gippsland\",\"Bass Phillip\",\"Gamay\",\"Gamay\",\"Unknown\",\"Unknown\",\"Australia\",\"Victoria\",\"Gippsland\",\"Gippsland\",\"23/09/2021\",\"False\",\"True\",\"42\",\"username\",\"\",\"0\",\"92\",\"\",\"\",\"\",\"\",\"\",\"Medium garnet
Nose: Medium, cranberry, red cherry, blackberry
Tertiary: wet leaves and leather
Palate: Dry, medium tannin, medium alcohol, medium body, pronounced flavour intensity, high acidity
Cranberry, sour cherry, spice, blackberry
Tertiary cigar box
Very good\",\"\",\"3\",\"92.6666666666667\",\"1\",\"1\",\"0\",\"0\",\"9\"
\"9403764\",\"2624186\",\"Red\",\"94404\",\"2014\",\"Elderton Shiraz Single Vineyard Command\",\"Elderton Shiraz Single Vineyard Command\",\"Australia, South Australia, Barossa\",\"Elderton\",\"Shiraz\",\"Syrah\",\"Single Vineyard\",\"Command\",\"Australia\",\"South Australia\",\"Barossa\",\"Barossa\",\"2/10/2021\",\"False\",\"True\",\"229\",\"username\",\"\",\"0\",\"93\",\"\",\"\",\"\",\"\",\"\",\"Deep garnet
Nose
Pronounced intensity
Primary Black pepper, black fruit, eucalyptus
Secondary Leather
Tertiary Espresso

Palate
Dry, medium tannins, medium bodied, pronounced flavour, high acidity.
Primary Blackberry, black cherry
Tertiary Chocolate Leather
Long finish

Balance 1
Length 1
Intensity 1
Complexity 1

Outstanding wine

Great with a beef/cheese/bacon burger\",\"True\",\"9\",\"91\",\"6\",\"1\",\"0\",\"0\",\"455\"
\"9351221\",\"2446705\",\"Red - Sparkling\",\"94404\",\"1001\",\"Robert Stein Shiraz Sparkling Shiraz\",\"Robert Stein Shiraz Sparkling Shiraz\",\"Australia, New South Wales, Central Ranges, Mudgee\",\"Robert Stein\",\"Shiraz\",\"Syrah\",\"Sparkling Shiraz\",\"Unknown\",\"Australia\",\"New South Wales\",\"Central Ranges\",\"Mudgee\",\"3/09/2021\",\"False\",\"True\",\"98\",\"username\",\"\",\"0\",\"92\",\"\",\"\",\"\",\"\",\"\",\"Smooth, round, soft bubbles and full mouth. Sweet, with a long finish\",\"True\",\"2\",\"92\",\"1\",\"1\",\"0\",\"0\",\"5\"

[yuanliu]

Can you clarify "changed it to CSV" - this means using a script, correct?

Another question: Splunk can use CSV for lookup, why not just give the CSV to REST API?  If the CSV is fully conformant, Splunk can directly use it. (I am recently looking at various API returns.  Haven't encountered a tab delimited one, but always good to learn how to handle various situations.)

[bowesmana]

@yuanliu It wasn't a script, just a curl command (webtools app) in the SPL where the API supports a format parameter. I realise I could have made the whole thing script based and used the REST API, but wanted to avoid that - most things are possible in SPL, so was looking for an SPL specific solution.

[yuanliu]

I just realized that the original data is not properly quoted, therefore standard conformant script will not work. Any custom script will also have to make assumptions about data, too.

[ITWhisperer]

Can you repost your sample data as comma separated values?