We have imported Json data with the following custom_fields.
{
"id": 100,
"custom_fields":
[{
"id": 1,
"name": "Date",
"value": "2017-12-15"
}, {
"id": 2,
"name": "Apply_Time",
"value": 1.5
}, {
"id": 3,
"name": "Actual_Time",
"value": 0
}],
"created_on": "2017-12-15",
"updated_on": "2017-12-15"
}
I want to make the value of custom_fields look like the table below.
| id | Date | Apply_Time | Actual_Time | created_on | updated_on |
| 100 | 2017-12-15 | 1.5 | 0 | 2017-12-15 | 2017-12-15 |
I tried spath command etc, but I did not solve it.
Thank you for your instruction.
Hi
this solution must be adapted to your real solution, especially spath
command configuration.
Anyway, try something like this
index=mt_index sourcetype="json_no_timestamp"
| spath
| rename custom_fields{}.id AS id2 custom_fields{}.name AS name custom_fields{}.value AS value
| rex "\{\"id\":\s+(?<id>\d+),"
| eval
id=mvindex(id,0),
Date=mvindex(value,0),
Apply_Time=mvindex(value,1),
Actual_Time=mvindex(value,2),
created_on=mvindex(created_on,0),
updated_on=mvindex(updated_on,0)
| table id Date Apply_Time Actual_Time created_on updated_on
Bye.
Giuseppe
@TAmemiya, Please try the following run anywhere search which mimics the sample data as per the question. The | makeresults
and | eval _raw
command before | spath
are used to create sample data, you can replace with your base search.
| makeresults
| eval _raw="{
\"id\": 100,
\"custom_fields\":
[{
\"id\": 1,
\"name\": \"Date\",
\"value\": \"2017-12-15\"
}, {
\"id\": 2,
\"name\": \"Apply_Time\",
\"value\": 1.5
}, {
\"id\": 3,
\"name\": \"Actual_Time\",
\"value\": 0
}],
\"created_on\": \"2017-12-15\",
\"updated_on\": \"2017-12-15\"
}"
| spath
| rename custom_fields{}.id as sub_id
| rename "custom_fields{}.*" as "*"
| eval name=mvappend(name,"id","created_on","updated_on")
| eval value=mvappend(value,id,created_on,updated_on)
| eval custom_fields=mvzip(name,value)
| mvexpand custom_fields
| eval custom_fields=split(custom_fields,",")
| eval name=mvindex(custom_fields,0)
| eval value=mvindex(custom_fields,1)
| table name value
| transpose header_field=name column_name=name
| fields - name
Refer to multivalue eval functions in Splunk Documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions
Thank you for your reply, Mr. Niketnilay.
I'll try it immediately.
Hi
this solution must be adapted to your real solution, especially spath
command configuration.
Anyway, try something like this
index=mt_index sourcetype="json_no_timestamp"
| spath
| rename custom_fields{}.id AS id2 custom_fields{}.name AS name custom_fields{}.value AS value
| rex "\{\"id\":\s+(?<id>\d+),"
| eval
id=mvindex(id,0),
Date=mvindex(value,0),
Apply_Time=mvindex(value,1),
Actual_Time=mvindex(value,2),
created_on=mvindex(created_on,0),
updated_on=mvindex(updated_on,0)
| table id Date Apply_Time Actual_Time created_on updated_on
Bye.
Giuseppe