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?
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
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.
Hi @kamlesh_vaghela , This is how the data looks.
Can you please copy _raw and paste it here in the code </> block?
{
"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.
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.
Thank you so much.. It is working fine..