topic Re: How to format JSON data into a table? in Dashboards & Visualizations

How to format JSON data into a table?

anooshac — Fri, 02 Sep 2022 14:31:52 GMT

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?

Re: How to format JSON data into a table?

kamlesh_vaghela — Fri, 02 Sep 2022 15:27:33 GMT

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

Re: How to format JSON data into a table?

anooshac — Mon, 05 Sep 2022 05:54:19 GMT

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

Re: How to format JSON data into a table?

anooshac — Wed, 28 Sep 2022 06:24:07 GMT

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?

Re: How to format JSON data into a table?

kamlesh_vaghela — Wed, 28 Sep 2022 06:43:01 GMT

@anooshac

I think you should try this.

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.

Re: How to format JSON data into a table?

anooshac — Wed, 28 Sep 2022 07:13:53 GMT

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?

Re: How to format JSON data into a table?

kamlesh_vaghela — Wed, 28 Sep 2022 08:08:02 GMT

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

Re: How to format JSON data into a table?

anooshac — Wed, 28 Sep 2022 09:25:13 GMT

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.

Re: How to format JSON data into a table?

kamlesh_vaghela — Wed, 28 Sep 2022 12:11:13 GMT

@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

Re: How to format JSON data into a table?

anooshac — Thu, 29 Sep 2022 11:01:47 GMT

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

Re: How to format JSON data into a table?

kamlesh_vaghela — Thu, 29 Sep 2022 11:34:52 GMT

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

Re: How to format JSON data into a table?

anooshac — Fri, 30 Sep 2022 06:44:57 GMT

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

Re: How to format JSON data into a table?

kamlesh_vaghela — Fri, 30 Sep 2022 06:47:06 GMT

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.

Re: How to format JSON data into a table?

anooshac — Tue, 07 Mar 2023 09:47:34 GMT

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

Re: How to format JSON data into a table?

kamlesh_vaghela — Wed, 08 Mar 2023 14:30:30 GMT

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

Re: How to format JSON data into a table?

kamlesh_vaghela — Wed, 08 Mar 2023 14:32:03 GMT

Attaching Screenshot

Re: How to format JSON data into a table?

anooshac — Fri, 17 Mar 2023 11:15:50 GMT

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.

Re: How to format JSON data into a table?

kamlesh_vaghela — Mon, 20 Mar 2023 18:57:31 GMT

@anooshac

Can you please share your updated JSON event?

Re: How to format JSON data into a table?

anooshac — Tue, 21 Mar 2023 07:59:15 GMT

Hi @kamlesh_vaghela , This is how the data looks.

Re: How to format JSON data into a table?

kamlesh_vaghela — Tue, 21 Mar 2023 08:08:35 GMT

@anooshac

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