topic Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ? in Splunk Search

How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

faramarz — Thu, 23 Jul 2015 18:31:57 GMT

I have messed up log data for a specific type of event, and I wanted to fix it manually. Basically, when the data was entered, it was entered with a \" in the program, with the slash for escaping to insert a " character in a string, instead of just simply a ". I wanted to go through all the old records and simply replace the \" with a ". How would I go about doing this? It seems like I'd use some variation of regex and the replace command, but I can't figure out how to do that. Thanks!

Picture included to further demonstrate what I mean.

Thanks for help in advance.

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

bmacias84 — Thu, 23 Jul 2015 19:15:00 GMT

How does this data come into splunk (script, etc)?

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

faramarz — Thu, 23 Jul 2015 19:17:58 GMT

it's a log file fed in by another server and script. I could change the script, but it wouldn't affect the past events already in the system. Looking to cleanse it and make the data more accessible, you know what I mean?

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

somesoni2 — Thu, 23 Jul 2015 19:21:52 GMT

You can't update the indexed data but you can update the raw data at search time. Try something like this

your base search |  rex mode=sed "s/(\\\\\")/\"/g" | ...rest of the search

Update

I think this should work.

event | search event="Out of Stock" | rex mode=sed "s/(\\\\\")/\"/g" | spath event |...rest of the search

Basically, first remove the junk characters and then do the spath to get the field extraction.

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

faramarz — Thu, 23 Jul 2015 20:13:14 GMT

I still can't search the individual fields. I'd like to make them searchable at the least, if not correct the error. Is there a way to do that? I tried:

event| spath event | search event="Out of Stock" | rex mode=sed "s/(\\\\\")/\"/g" | rename body.recordtype AS recordtype, body.action AS action, body.apikey AS apikey, body.id AS id, body.displayname AS displayname, body.upc AS upc, body.sku AS sku, body.manufacturer AS manufacturer, body.description AS description, body.color AS color, body.size AS size, body.baseprice AS baseprice, body.averagecost AS averagecost, body.category AS category, body.taxable AS taxable, body.ishazmatitem AS ishazmatitem, body.inventory AS inventory | eval x=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(recordtype, action, ","), apikey, ","), id, ","), displayname, ","), upc, ","), sku, ","), manufacturer, ","), description, ","), color, ","), size, ","), baseprice, ","),  averagecost, ","),  category, ","), taxable, ","),  ishazmatitem, ","),  inventory, ",")| mvexpand x | eval x = split(x,",") | eval recordtype=mvindex(x,0) | eval action=mvindex(x,1) | eval apikey=mvindex(x,2) | eval id=mvindex(x,3) | eval displayname=mvindex(x,4) | eval upc=mvindex(x,5) | eval sku=mvindex(x,6) | eval manufacturer=mvindex(x,7) | eval description=mvindex(x,8) | eval color=mvindex(x,9) | eval size=mvindex(x,10) | eval baseprice=mvindex(x,11) | eval averagecost=mvindex(x,12) | eval category=mvindex(x,13) | eval taxable=mvindex(x,14) | eval ishazmatitem=mvindex(x,15) | eval inventory=mvindex(x,16) | table recordtype, action, apikey, id, displayname, upc, sku, manufacturer, description, color, size, baseprice, averagecost, category, taxable, ishazmatitem, inventory

The issue is body.recordtype and the rest of body.* doesn't work to search it. Does anyone know how to make it searchable?

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

aljohnson_splun — Thu, 23 Jul 2015 20:40:35 GMT

@somesoni2 I'd think you could covert this to answer ?

Re: How can I manually change badly formatted data using a search with regex to replace all \" with just a single quote " ?

faramarz — Thu, 23 Jul 2015 20:46:07 GMT

I was looking for something like this:

event | spath event | search event="Out of Stock" | rex mode=sed "s/(\\\\\")/\"/g" | rex field=body "\"recordtype\":(?[^,]+)," | eval body.recordtype = recordtype

which is repeatable for each field