topic Re: Splunk DB connect - JSON datas in All Apps and Add-ons

Splunk DB connect - JSON datas

miguel1423 — Thu, 19 Oct 2023 07:44:06 GMT

Hello everyone,

I m using Splunk DB connect to get data from DB, I get three values from the DB as follow :

- Event's ID

- Json Data

- creation data of events

There is the result, how can I remove the "rawjson=" to ba able to get this data on json format ?

Regards,

Re: Splunk DB connect - JSON datas

richgalloway — Thu, 19 Oct 2023 12:24:52 GMT

Try using sed.

| rex mode=sed "s/rawjson=\\\"//"

Re: Splunk DB connect - JSON datas

miguel1423 — Thu, 26 Oct 2023 09:13:32 GMT

Hello, Thank you for your reply but it doesn't work 😞

Maybe it isn't possible to convert the JSON data that I got from DB connect 😕

Re: Splunk DB connect - JSON datas

richgalloway — Thu, 26 Oct 2023 11:58:24 GMT

Yes, it's possible. It's just text and text can be manipulated.

Please describe what you mean by "it doesn't work". I need something to work with. What is the full query you tested? What results do you get?

Other things to try:

The replace command

| replace "rawjson=" with "" in _raw

the replace function

| eval _raw=replace(_raw, "rawjson=", "")

Re: Splunk DB connect - JSON datas

miguel1423 — Thu, 26 Oct 2023 13:05:01 GMT

Hello,

Thank you for your help, I appreciate.

I m trying to explain what I want

1. We send json logs to a Mysql DB from an application server

-> this is the logs format from the application server -->

{"bam":{"facture":{"@idFFFFF":"","@idBBBBB":"","@idCCCCC":"","@idCCCCC":"","@ABCACB":"","@status":""},"Contact":{"@idContact":"","@nom":"","@prenom":"","@adresse":"","@typeContact":""},"service":{"@jobName":"XX_Abcdef_Abccc_Token_V1","@jobVersion":"x.x","@routeName":"","@routeVersion":"","@currentTime":"2023-07-03 13:00:28","@idCorrelation":"545454ssss-abcc-456ss-5454-444455555554444","@serviceDuration":"1140"}}}

If I copy this ligne on notepad and manually import it on splunk I get want I want to have (I used the default source type)

Each value is extracted so it's perfect

2. To automatiquely get the new logs from the DB server I decided to use Splunk DB connect ( maybe it's not the best choice ? )

So I configured a new input in the Splunk DB connect to get the value from the DB table

But now the data are not indexed on json format as shown below

How can I get these datas on json format as shown on the first and second capture ?

Hope iyou understand better what I m trying to do

Regards,

Re: Splunk DB connect - JSON datas

richgalloway — Thu, 26 Oct 2023 13:34:39 GMT

Thanks for the great explanation. The new screenshot is clearer and shows that I had used "j" instead of "J" in my regex. Please try this

| rex mode=sed "s/rawJson=//" | eval _raw=trim(_raw, "\"")

Re: Splunk DB connect - JSON datas

miguel1423 — Thu, 26 Oct 2023 13:43:41 GMT

Thanks,

I did the change, I get the value below but it isn't in json format ? each value still in the same field (like @idfacture; @idfactureABC; @routename)

Regards,

Re: Splunk DB connect - JSON datas

richgalloway — Thu, 26 Oct 2023 14:30:14 GMT

I see the trim function didn't remove the first quotation mark since it isn't at the beginning of the event (because of the timestamp). Here's another regex to try. It attempts to replace the event with the text after 'rawJson="' up to the last '"'.

| rex mode=sed "s/rawJson=\\\"(.*)\\\"$/\1/"

Re: Splunk DB connect - JSON datas

miguel1423 — Fri, 27 Oct 2023 07:36:24 GMT

Hello I just tried the rex but I still not json format, Do I need to export the query in json or something like that ?

Regards,

Re: Splunk DB connect - JSON datas

miguel1423 — Fri, 27 Oct 2023 09:14:02 GMT

Hello,

I just tried to remove the entire timestamp before the json data and it's work

But how can I remove the timestamp for all query with different timestamp ?

Regards,

Re: Splunk DB connect - JSON datas

miguel1423 — Fri, 27 Oct 2023 09:13:34 GMT

Re,

I tried that to remove the timestamp and it's work now but don't know if it's correct or if I need to optimse that ?

Regards,