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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...