Dashboards & Visualizations

How to get JSON fields data into a table?

anooshac
Communicator

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.

Labels (3)
Tags (3)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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"
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...