Getting Data In

How to remove characters in my json raw data so it can be indexed in son formate?

appache
Path Finder

{"ts":"11 03 2016 06:03:56.390","th":"sample-product","user":"apple","device":"iphone","errorCode":"","level":"INFO","msg":"Publishing event to cache "TimeZones" with message "Message@4eedf6e5"."}

If we remove the quotes for TimeZones and Message@4eedf6e5 it will be converted as son formate as below

{
"ts":"11 03 2016 06:03:56.390",
"th":"sample-product",
"user":"apple",
"device":"iphone",
"errorCode":"",
"level":"INFO",
"msg":"Publishing event to cache TimeZones with message Message@4eedf6e5."
}

Can someone could help me out in this....
Thanks in advance.

0 Karma
1 Solution

gokadroid
Motivator

Your data has four sets of ", basically

{" which starts the json
":" which divides a key and value
"," which divides one key value pair from another
"} which ends your json data.

Every " besides the above four combinations (assumption based on above data) can be safely removed. Based on this can you try this in your props.conf as it seems to be working in my local with the above dataset:

[your:sourcetype]
 SEDCMD-rep_1 = s/{"/{'/g
 SEDCMD-rep_2 = s/":"/':'/g
 SEDCMD-rep_3 = s/","/','/g
 SEDCMD-rep_4 = s/"}/'}/g
 SEDCMD-rep_5 = s/"//g
 SEDCMD-rep_6 = s/'/"/g

Above SEDCMD portion should be added in addition to other properties/settings you already might be having for your:sourcetype. These six lines above do not represent the only ones to be put in, so please do not delete the other properties for your:sourcetype which might already be present like PREFIX_SOURCETYPE SHOULD_LINEMERGE etc.

Logic above is to remove each group of " one at a time in combination with other { or : or , and replace with single quote to keep the json structure. Then in the end once all the above four groups of double quotes are replaced with single quotes, replace all the remaining " with blank.

Once all the " are replaced either with single quote or blank, now it is a good time for SEDCMD-rep_6 = s/'/"/g to replace back all the single quotes with double quotes to restore your json format.

NOTE: You can use any other symbol like ~ rather than single quote in above sed replacementsif you feel single quote can be part of your data.

View solution in original post

gokadroid
Motivator

Your data has four sets of ", basically

{" which starts the json
":" which divides a key and value
"," which divides one key value pair from another
"} which ends your json data.

Every " besides the above four combinations (assumption based on above data) can be safely removed. Based on this can you try this in your props.conf as it seems to be working in my local with the above dataset:

[your:sourcetype]
 SEDCMD-rep_1 = s/{"/{'/g
 SEDCMD-rep_2 = s/":"/':'/g
 SEDCMD-rep_3 = s/","/','/g
 SEDCMD-rep_4 = s/"}/'}/g
 SEDCMD-rep_5 = s/"//g
 SEDCMD-rep_6 = s/'/"/g

Above SEDCMD portion should be added in addition to other properties/settings you already might be having for your:sourcetype. These six lines above do not represent the only ones to be put in, so please do not delete the other properties for your:sourcetype which might already be present like PREFIX_SOURCETYPE SHOULD_LINEMERGE etc.

Logic above is to remove each group of " one at a time in combination with other { or : or , and replace with single quote to keep the json structure. Then in the end once all the above four groups of double quotes are replaced with single quotes, replace all the remaining " with blank.

Once all the " are replaced either with single quote or blank, now it is a good time for SEDCMD-rep_6 = s/'/"/g to replace back all the single quotes with double quotes to restore your json format.

NOTE: You can use any other symbol like ~ rather than single quote in above sed replacementsif you feel single quote can be part of your data.

appache
Path Finder

Thankyou for you quick response, but there is no difference in my data its still the same i have tried masking another fields also but even that didnt work and also i have tried indexed_extractions=json. Is there any other way to do it.

0 Karma

gokadroid
Motivator

this is how my props.conf showed up:

[multixyz]
SEDCMD-rep_1 = s/{"/{'/g
SEDCMD-rep_2 = s/":"/':'/g
SEDCMD-rep_3 = s/","/','/g
SEDCMD-rep_4 = s/"}/'}/g
SEDCMD-rep_5 = s/"//g
SEDCMD-rep_6 = s/'/"/g
DATETIME_CONFIG = 
NO_BINARY_CHECK = true
category = Custom
pulldown_type = true

This is how my inputs.conf showed up which sorted

[tcp://12125]
connection_host = dns
index = multixyzjson
sourcetype = multixyz

These were my events which were fed:

{"ts":"11 03 2016 06:03:56.390","th":"sample-product","user":"apple","device":"iphone","errorCode":"","level":"INFO","msg":"Publishing event to cache "TimeZones" with message "Message@4eedf6e5"."}

0 Karma

appache
Path Finder

Thankyou gokadroid Got it 🙂

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...