topic Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect? in All Apps and Add-ons

How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

gsrivastava — Mon, 18 Sep 2017 09:34:12 GMT

I am creating a DB Input in DB Connect v3
My DB columns contains JSON values.
I am getting correct raw data in Splunk, but on selecting Table mode, the field does not have correct values. for ex, if the column name is status and the JSON it contains is : "[{"datasetId":1,"refreshStatus":16}]", then the field created in splunk is status but it only contains the value : '[{'.
All the rows in Splunk contains this same value '[{'
May be Splunk is using double quote as a delimiter to separate fields, but the JSON itself contains the quotes.

Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

burwell — Tue, 19 Sep 2017 00:13:12 GMT

I am using DB Connect 2.4.0 with JSON data. A sample might be.

"record":{"subfield1":"value1","subfield2","value2"}

I use "SELECT record['subfield1'] AS subfield1.. WHERE record['subfield'] IS NOT NULL" to get the value.

And if subfield1 itself is JSONlike then I do something like rex field=subfield1 "\"somefield\":(?<somefieldT>.*?)[,}]" in order to get somefield out.

Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

gsrivastava — Tue, 19 Sep 2017 08:53:43 GMT

Thanks @burwell for responding. But I want complete json in a field and handle that json in splunk itself.
Sample JSON array is-

[  
   {  
      "set":3,
      "ids":[  
         9
      ],
      "status":12,
      "code":570,
      "idDetails":[  
         {  
            "id":9,
            "status":12,
            "code":570
         }
      ]
   },
   {  
      "set":1,
      "ids":[  
         5,
         8
      ],
      "status":21,
      "code":601,
      "idDetails":[  
         {  
            "id":5,
            "status":21,
            "code":601
         },
         {  
            "id":8,
            "status":21,
            "code":601
         }
      ]
   }
]

With this kind of nested JSON and with multiple keys having the same name, its not possible to extract all the fields in SQL itself.

Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

burwell — Thu, 21 Sep 2017 01:18:06 GMT

Hello gsrivastava . I am seeing my fields with the JSON. Try SELECT field and then do | table field

I am seeing {"key":"value,"key2":"value2"} etc.

Maybe you can show your SELECT with LIMIT 1 and the output you get.

Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

gsrivastava — Wed, 25 Oct 2017 05:45:28 GMT

May be this happens because Splunk IFX uses comma(,) as a default delimiter to separate and extract fields but the JSON itself contains commas(,) , therefore the default regex which is generated separates the field when it encounters the first comma(,) .
So to extract any JSON field, one has to edit the original regex and make any changes required to extract the entire JSON field instead of '[}'

Re: How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

stefan_d — Tue, 31 Jul 2018 14:21:50 GMT

Hi there!

Have you managed to solve this problem?
I also have a similar issue, is it possible to grab the JSON data from a column in your DB input?