How to format JSON data into a table?

[anooshac]

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?

[kamlesh_vaghela]

@anooshac 

Can you please share your updated JSON event?

KV

[anooshac]

Hi @kamlesh_vaghela , This is how the data looks.

[kamlesh_vaghela]

@anooshac 

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

[anooshac]

{
    "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.

[kamlesh_vaghela]

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

[anooshac]

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