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!

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...