Splunk DB connect - JSON datas

[miguel1423]

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 ?

miguel1423_0-1697701369771.png

Regards,

[richgalloway]

Try using sed. 

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

[miguel1423]

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 😕 

[richgalloway]

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=", "")

[miguel1423]

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)

miguel1423_0-1698324626218.png

Each value is extracted so it's perfect

miguel1423_2-1698324764006.png

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

miguel1423_3-1698325033042.png

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

miguel1423_4-1698325229904.png

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,

[richgalloway]

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, "\"")

[miguel1423]

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)

miguel1423_0-1698327791213.png

Regards,

[richgalloway]

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/"

[miguel1423]

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 ? 

miguel1423_0-1698398007041.png

Regards,

[miguel1423]

Hello,

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

miguel1423_0-1698393429833.png

miguel1423_2-1698393509701.png

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

Regards,

[miguel1423]

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 ? 

miguel1423_1-1698392174050.png

Regards,