Splunk Search

using regex to reformat json messages

rcmiller11
New Member

I have a VidyoPortal that gives me its responses formatted this way through its event notification system:

**VDY\x00\x00\xFA**{"sequenceNum":1549002625629,"roomNotification":null,"userNotification":{"referenceNumber":null,"applicationName":null,"applicationVersion":null,"deviceModel":null,"endpointPublicIPAddress":"","accessType":"","roomType":"","roomOwner":"","applicationOs":null,"callCompletionCode":"0","extension":null,"endpointGUID":"BA8-0200323238353132-8C53EC8501659CFF","participantId":0,"roomID":0,"audioState":0,"videoState":0,"extData":null,"extDataType":0,"conferenceName":null,"callerName":null,"tenantName":null,"callState":"Online","uniqueCallID":null,"conferenceType":null,"endpointType":"D","callerID":null,"direction":null,"routerID":null,"gwid":null,"gwprefix":null},"alert":null,"creationTimestamp":3589263127594056,"queueTimestamp":3589263127646846,"wireTimestamp":3589263128426891,"externalStatusNotificationUrl":null,"externalUsername":null,"externalPassword":null,"plainTextExternalPassword":null,"vidyoStatusNotificationUrl":null,"vidyoUsername":null,"vidyoPassword":null,"plainTextVidyoPassword":null,"tenantId":0}

I need to remove the leading set of characters to get it as properly formatted JSON. Also, with some of the messages i'm getting nested JSON that has those characters in the body of the message. I have also found it in both this format (VDY\x00\x00\xFA) and this format (VDY\x00\x00)

Can anyone assist with the regex i can use when querying to remove those characters? without them the JSON is properly formatted and i can work with it.

Tags (1)
0 Karma
1 Solution

chrisyounger
SplunkTrust
SplunkTrust

It would be best to remove those characters at index time. You can do this with SEDCMD like so. On the indexers (of heavy forwarder if the goes through that) then add this line to the props.conf file:

[SSN-CC-Anon]
SEDCMD-Anon = s/^[^{]*//g

You can also do this at search time like so:

| makeresults 
| eval _raw = "VDY\x00\x00\xFA{\"sequenceNum\":1549002625629,\"roomNotification\":null,\"userNotification\":{\"referenceNumber\":null,\"applicationName\":null,\"applicationVersion\":null,\"deviceModel\":null,\"endpointPublicIPAddress\":\"\",\"accessType\":\"\",\"roomType\":\"\",\"roomOwner\":\"\",\"applicationOs\":null,\"callCompletionCode\":\"0\",\"extension\":null,\"endpointGUID\":\"BA8-0200323238353132-8C53EC8501659CFF\",\"participantId\":0,\"roomID\":0,\"audioState\":0,\"videoState\":0,\"extData\":null,\"extDataType\":0,\"conferenceName\":null,\"callerName\":null,\"tenantName\":null,\"callState\":\"Online\",\"uniqueCallID\":null,\"conferenceType\":null,\"endpointType\":\"D\",\"callerID\":null,\"direction\":null,\"routerID\":null,\"gwid\":null,\"gwprefix\":null},\"alert\":null,\"creationTimestamp\":3589263127594056,\"queueTimestamp\":3589263127646846,\"wireTimestamp\":3589263128426891,\"externalStatusNotificationUrl\":null,\"externalUsername\":null,\"externalPassword\":null,\"plainTextExternalPassword\":null,\"vidyoStatusNotificationUrl\":null,\"vidyoUsername\":null,\"vidyoPassword\":null,\"plainTextVidyoPassword\":null,\"tenantId\":0}" 
| rex mode=sed "s/^[^{]*//" 
| spath

Hope this is helpful

View solution in original post

rcmiller11
New Member

Thanks that was very helpful! it removed the bad string from the results and gave me a good JSON format

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

It would be best to remove those characters at index time. You can do this with SEDCMD like so. On the indexers (of heavy forwarder if the goes through that) then add this line to the props.conf file:

[SSN-CC-Anon]
SEDCMD-Anon = s/^[^{]*//g

You can also do this at search time like so:

| makeresults 
| eval _raw = "VDY\x00\x00\xFA{\"sequenceNum\":1549002625629,\"roomNotification\":null,\"userNotification\":{\"referenceNumber\":null,\"applicationName\":null,\"applicationVersion\":null,\"deviceModel\":null,\"endpointPublicIPAddress\":\"\",\"accessType\":\"\",\"roomType\":\"\",\"roomOwner\":\"\",\"applicationOs\":null,\"callCompletionCode\":\"0\",\"extension\":null,\"endpointGUID\":\"BA8-0200323238353132-8C53EC8501659CFF\",\"participantId\":0,\"roomID\":0,\"audioState\":0,\"videoState\":0,\"extData\":null,\"extDataType\":0,\"conferenceName\":null,\"callerName\":null,\"tenantName\":null,\"callState\":\"Online\",\"uniqueCallID\":null,\"conferenceType\":null,\"endpointType\":\"D\",\"callerID\":null,\"direction\":null,\"routerID\":null,\"gwid\":null,\"gwprefix\":null},\"alert\":null,\"creationTimestamp\":3589263127594056,\"queueTimestamp\":3589263127646846,\"wireTimestamp\":3589263128426891,\"externalStatusNotificationUrl\":null,\"externalUsername\":null,\"externalPassword\":null,\"plainTextExternalPassword\":null,\"vidyoStatusNotificationUrl\":null,\"vidyoUsername\":null,\"vidyoPassword\":null,\"plainTextVidyoPassword\":null,\"tenantId\":0}" 
| rex mode=sed "s/^[^{]*//" 
| spath

Hope this is helpful

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...