Splunk Search

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

faramarz
Path Finder

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. alt text

Thanks for help in advance.

0 Karma
1 Solution

somesoni2
Revered Legend

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.

View solution in original post

somesoni2
Revered Legend

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.

View solution in original post

aljohnson_splun
Splunk Employee
Splunk Employee

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

0 Karma

faramarz
Path Finder

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

0 Karma

faramarz
Path Finder

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?

0 Karma

bmacias84
Champion

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

0 Karma

faramarz
Path Finder

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?

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!