Solved: How to format JSON data into a table?

anooshac · ‎09-02-2022

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 · ‎09-02-2022

@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

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 · ‎09-02-2022

@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

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.

anooshac · ‎09-27-2022

Hi @kamlesh_vaghela , I have changed the file format to this,

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

And Changed the query to

| spath output=Analysis_Summary Info.Analysis_Summary
| rex field=Analysis_Summary "\"(?<Database>[^\"]+)\":{ \"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

But i am not able to collect the data.It says tmp does not exist.Is there any mistake in the regular expression?

kamlesh_vaghela · ‎09-27-2022

@anooshac

I think you should 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

My Sample Search :

| makeresults | eval _raw="{\"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\"}}}}"
| 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.

anooshac · ‎09-28-2022

hi, @kamlesh_vaghela , still im getting as temp not found.. is it due to the new format of the data? is it becuase of new line?

kamlesh_vaghela · ‎09-28-2022

@anooshac

Yeah, maybe a new line can be an issue. Can you please try this?

YOUR_SEARCH
| rex field=_raw mode=sed "s/(\t+|\s)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"available\"\:\"(?<available>[^,]+)\",\"used\"\:\"(?<used>[^,]+)\",\"used%\"\:\"(?<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

My Sample Search :

| makeresults | eval _raw="{
    \"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\"
                               }
			}
}}"
| rex field=_raw mode=sed "s/(\t+|\s)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"available\"\:\"(?<available>[^,]+)\",\"used\"\:\"(?<used>[^,]+)\",\"used%\"\:\"(?<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.

anooshac · ‎09-28-2022

Hi @kamlesh_vaghela ,still it is the same. Actually my data is more, i have only written a few part of it in the question. if i use

| spath output=Analysis_Summary Info.Analysis_Summary
| rex field=Analysis_Summary .....

will this be able to help?

Below is my data.

{
    "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"
                               }
			},
"Database1_info":{
},
"Database2_info":{
},
"Database3_info":{
}
}}

Can you please help me. Not able to find out the mistake.

kamlesh_vaghela · ‎09-28-2022

@anooshac

Even your provided data works for me.

| makeresults | eval _raw="{
    \"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\"
                               }
            },
\"Database1_info\":{
},
\"Database2_info\":{
},
\"Database3_info\":{
}
}}"
| rex field=_raw mode=sed "s/(\t+|\s)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"available\"\:\"(?<available>[^,]+)\",\"used\"\:\"(?<used>[^,]+)\",\"used%\"\:\"(?<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

Can you please share _raw from search.like

YOUR_INDEX | table _raw

KV

anooshac · ‎09-29-2022

Hi, @kamlesh_vaghela ,This is what the "index | table _raw " looks like. I am not sure whats causing to be tmp to be not found in the query.

{
    "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": "0",
                "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",
        }
    }
}

kamlesh_vaghela · ‎09-29-2022

@anooshac

Can you please try this?

YOUR_SEARCH
| rex field=_raw mode=sed "s/(\t+|\s)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"Available\"\:\"(?<available>[^,]+)\",\"Used\"\:\"(?<used>[^,]+)\",\"Used\(%\)\"\:\"(?<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

| makeresults | eval _raw="{
    \"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\": \"0\",
                \"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\",
        }
    }
}"
| rex field=_raw mode=sed "s/(\t+|\s)//g" max_match=0
| rex field=_raw "\"(?<Database>[^\"]+)\":{" max_match=0 
| rex field=_raw "\"Available\"\:\"(?<available>[^,]+)\",\"Used\"\:\"(?<used>[^,]+)\",\"Used\(%\)\"\:\"(?<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

there is a minor variation between previous events and last events. Just update regular expression if required.

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.

anooshac · ‎09-29-2022

@kamlesh_vaghela, thank you so much.. this helped me a lot. As you said i made some changes in the regular expression. Thank you so much once again.

kamlesh_vaghela · ‎09-29-2022

Glad to help you @anooshac

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

anooshac · ‎03-07-2023

Hi, @kamlesh_vaghela

How to use the query if the data is dynamic. That is

{
    "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 data "Used" is missing in DB3.But when i use this query i am not able to see any data related to DB3. In such cases how can i modify the query?can you please explain..

kamlesh_vaghela · ‎03-08-2023

@anooshac

Can you please try this?

YOUR_SEARCH
| 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

My Sample Search :

| makeresults 
| eval _raw="{\"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\"}}}" 
| 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

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.

kamlesh_vaghela · ‎03-23-2023

@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 · ‎03-23-2023

Hi @kamlesh_vaghela ,

Im getting as tmp does not exist. Not sure what is the problem.

kamlesh_vaghela · ‎03-29-2023

@anooshac

Maybe I need to revisit your raw data again.

Can you please share _raw from the below search?

index="YOUR_INDEX" |head 1 | table _raw

just copy and paste in reply in the code block. No other formatting.

KV

anooshac · ‎03-29-2023

Hi @kamlesh_vaghela

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

This is the data.

kamlesh_vaghela · ‎03-30-2023

@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

Incase still not working then, remove all statements and try adding them one by one to check at which point its breaking.

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

kamlesh_vaghela · ‎03-08-2023

Attaching Screenshot

anooshac · ‎03-17-2023

I have changed the data as this in every section.

                "Available": 1088,
                "Used": 173.23,
                "Used(%)": 15.92,
                "Status": "OK"

I have modified the rex to get numbers but unfortunately it gives as tmp not found. I am not sure what is the mistake. Used "\d+\.?\d*" to extract any numbers.

How to format JSON data into a table?

chart

table

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

Transform your security operations with Splunk Enterprise Security

Splunk Admins and App Developers | Earn a $35 gift card!