How to get JSON fields data into a table?

[anooshac]

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/6... 

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.

[kamlesh_vaghela]

@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/6...

KV

[ITWhisperer]

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"

[gcusello]

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:

<your_search>
| spath
| rename <json-fields> ...
| fillnull value="NA" available
| fillnull value="NA" used
| fillnull value="NA" used%
| fillnull value="NA" status
| table ...

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

Ciao.

Giuseppe