topic How to get JSON fields data into a table? in Dashboards & Visualizations

How to get JSON fields data into a table?

anooshac — Wed, 08 Mar 2023 07:38:14 GMT

Hi everyone,

I have asked a similar question and i got the answer.

https://community.splunk.com/t5/Dashboards-Visualizations/How-to-format-JSON-data-into-a-table/m-p/615115#M50466

But my doubt is if the fields in the JSON file are dynamic how can i get those into table.

{ "Info": { "Unit": "ABC", "Project": "XYZ", "Analysis Summary": { "DB1": { "Available": "1088kB", "Used": "173.23kB", "Used(%)": "15.92%", "Status": "OK" }, "DB2": { "Available": "4096kB", "Used": "1591.85kB", "Used(%)": "38.86%", "Status": "OK" }, "DB3": { "Available": "128kB", "Used(%)": "2.6%", "Status": "OK" }, "DB4": { "Available": "16500kB", "Used": "6696.0", "Used(%)": "40.58%", "Status": "OK" }, "DB5": { "Available": "22000kB", "Used": "9800.0", "Used(%)": "44.55%", "Status": "OK" } }, "RAM_Tracking": { "a": "2", "b": "1088.0", "c": "32.1220703125", }, "Database2_info": { "a": "4", "b": "4096.0", "c": "654.3212890625", }, "Database3_info": { "a": "5", "b": "6696", "c": "9800", }, "Database4_info": { "a": "6", "b": "128.0", "c": "21.086", } } }

As you see in the field "Used" is missing in DB3.But i want to show it in table as empty.

Database available used used% status DB1 4096KB 1582.07kB 38.62% OK DB2 1088kB 172.8kB 15.88% OK DB3 128KB NA 0% OK DB4 22000KB 9800.0KB 44.55% OK DB5 16500KB 6696.0KB 40.58% OK

Wherever there is no data i want to keep it as "NA". Till now i have only used constant data. Is it possible to create a table like this using dynamic data? Can anyone please help me.

Re: How to get JSON fields data into a table?

gcusello — Wed, 08 Mar 2023 07:56:46 GMT

Hi @anooshac,

using the "spath" command (https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Spath), you transform your json logs in table format that you can organize as you like.

If you have some empty fields, you can usi the "fillnull" command (https://docs.splunk.com/Documentation/Splunk/9.0.4/SearchReference/Fillnull) to put the "NA" value instead NULL or empty.

so you could have something like this:

remember to rename the fields from the json format to the table format to have the column fields you want.

Ciao.

Giuseppe

Re: How to get JSON fields data into a table?

ITWhisperer — Wed, 08 Mar 2023 08:06:22 GMT

Assuming the incorrect JSON format is an artefact of copy/paste or spoofed example generation. try the following

| spath "Info.Analysis Summary" output=AnalysisSummary | rex field=AnalysisSummary max_match=0 "(?<Database>\"\w+\":\s*\{[^\}]+\})" | mvexpand Database | rex field=Database "(?ms)\"(?<Database>[^\"]+)\":\s+(?<Summary>.*)" | spath input=Summary | fillnull value="NA"

Re: How to get JSON fields data into a table?

kamlesh_vaghela — Fri, 10 Mar 2023 06:53:04 GMT

@anooshac

Can you please check the below link for your ask?

https://community.splunk.com/t5/Dashboards-Visualizations/How-to-format-JSON-data-into-a-table/m-p/633736/highlight/true#M51954