Dashboards & Visualizations

How to format JSON data into a table?

anooshac
Communicator

Hi all, i have the json data as below.

 

{
    "Info": 
        {
            "Unit": "ABC",
            "Project": "XYZ",
            "Analysis Summary": {
				"DB 1":{"available": "1088kB","used": "172.8kB","used%": "15.88%","status":"OK"},
                "DB2 2":{"available": "4096KB","used": "1582.07kB","used%": "38.62%","status":"OK"},
                "DB3 3":{"available": "128KB","used": "0","used%": "0%","status":"OK"},
                "DB4 4":{"available": "16500KB","used": "6696.0KB","used%": "40.58%","status":"OK"},
                "DB5 5":{"available": "22000KB","used": "9800.0KB","used%": "44.55%","status":"OK"}
			}
}}

 

I want to create a table like this

 

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

 

I know how to extract the data but i am not able to put data in this format in table. Anyone have idea on this?

Labels (2)
Tags (3)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac 

Can you please try this?

 

YOUR_SEARCH
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"available\"\:\s\"(?<available>[^,]+)\",\"used\"\:\s\"(?<used>[^,]+)\",\"used%\"\:\s\"(?<used_p>[^,]+)\",\"status\"\:\"(?<status>[^}]+)\"}" max_match=0 
| eval tmp = mvzip(mvzip(mvzip(mvzip(Database,available),used),used_p),status) 
| mvexpand tmp 
| eval Database=mvindex(split(tmp,","),0), available=mvindex(split(tmp,","),1), used=mvindex(split(tmp,","),2), used_p=mvindex(split(tmp,","),3), status=mvindex(split(tmp,","),4)
| table Database available used used_p status

 

Screenshot 2022-09-02 at 8.57.13 PM.png

 

I hope this will help you. 

 

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac 

Can you please share your updated JSON event?

 

KV

0 Karma

anooshac
Communicator

Hi @kamlesh_vaghela , This is how the data looks.2023-03-21_13h26_33.png

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac 

Can you please copy _raw and paste it here in the code </> block? 

0 Karma

anooshac
Communicator

 

{
    "Info": {
        "Unit": "ABC",
        "Project": "XYZ",
        "Analysis Summary": {
            "DB1": {
                "Available": 1088,
                "Used": 173.23,
                "Used(%)": 15.92,
                "Status": "OK"
            },
            "DB2": {
                "Available": 4096,
                "Used": 1591.85,
                "Used(%)": 38.86,
                "Status": "OK"
            },
            "DB3": {
                "Available": 128,
                "Used(%)": 2.6,
                "Status": "OK"
            },
            "DB4": {
                "Available": 16500,
                "Used": 6696.0,
                "Used(%)": 40.58,
                "Status": "OK"
            },
            "DB5": {
                "Available": 22000,
                "Used": 9800.0,
                "Used(%)": 44.55,
                "Status": "OK"
            }
        }
    }
}

Hi @kamlesh_vaghela ,

 

This is the data.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac 

Can you please try this?

 

| makeresults 
| eval _raw="{
    \"Info\": {
        \"Unit\": \"ABC\",
        \"Project\": \"XYZ\",
        \"Analysis Summary\": {
            \"DB1\": {
                \"Available\": 1088,
                \"Used\": 173.23,
                \"Used(%)\": 15.92,
                \"Status\": \"OK\"
            },
            \"DB2\": {
                \"Available\": 4096,
                \"Used\": 1591.85,
                \"Used(%)\": 38.86,
                \"Status\": \"OK\"
            },
            \"DB3\": {
                \"Available\": 128,
                \"Used(%)\": 2.6,
                \"Status\": \"OK\"
            },
            \"DB4\": {
                \"Available\": 16500,
                \"Used\": 6696.0,
                \"Used(%)\": 40.58,
                \"Status\": \"OK\"
            },
            \"DB5\": {
                \"Available\": 22000,
                \"Used\": 9800.0,
                \"Used(%)\": 44.55,
                \"Status\": \"OK\"
            }
        }
    }
}" 
| rex field=_raw mode=sed "s/(\n\s+)//g" max_match=0
| rex field=_raw mode=sed "s/(^.*\"Analysis Summary\"\: )//g" max_match=0
| rex field=_raw mode=sed "s/(,\"RAM_Tracking.*)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\": \{\"Available" max_match=0
| rex field=_raw ":\s?(?<Data>\{.+?})" max_match=0
| eval tmp = mvzip(Database,Data,"|")
| mvexpand tmp
| eval Database=mvindex(split(tmp,"|"),0), Data=mvindex(split(tmp,"|"),1)
| rex field=Data "{\"Available\"\:\s?\"?(?<available>[^,]+)\"?,(\"Used\"\:\s?\"?(?<used>[^,]+)\"?,)?\"Used\(%\)\"\:\s?\"?(?<used_p>[^,]+)\"?,\"Status\"\:\s?\"(?<status>[^}]+)\"}"
| table Database available used used_p status

 

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

 

0 Karma

anooshac
Communicator

Thank you so much.. It is working fine..

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...