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

anooshac
Communicator

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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2022-09-28 at 12.12.35 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.

0 Karma

anooshac
Communicator

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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2022-09-28 at 1.37.45 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.

 

0 Karma

anooshac
Communicator

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

anooshac
Communicator

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

 

 

 

 

 

 

 

 

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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
Communicator

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

Tags (2)
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

Hi @kamlesh_vaghela ,

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

anooshac
Communicator

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.

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

 

Screenshot 2023-03-30 at 4.19.49 PM.png

 

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.

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Attaching Screenshot

 

Screenshot 2023-03-08 at 8.01.43 PM.png

0 Karma

anooshac
Communicator

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.

 

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...