Create a table based on values from two JSON payloads

[charan986]

Hi I've two different payloads returned from my search and I need to create a table from values extracted from the payloads, please find the find the payloads below

Payload 1

---

{ "appointment": {
"appointmentId": 0,
"key": "",
"Durations": 60,
"JType": "Medium",
"StartTime": "0001-01-01T00:00:00Z",
"EndTime": "0001-01-01T00:00:00Z",
"isDegOnly": false,
"softId": 112892 },

"commonID": "REDRF3243",

"slotStatusList": [
{
"date": "2020-03-24T00:00:00Z",
"status": [
{
"StartTime": "2020-03-24T06:30:00Z",
"EndTime": "2020-03-24T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-24T08:30:00Z",
"EndTime": "2020-03-24T10:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-24T10:30:00Z",
"EndTime": "2020-03-24T12:30:00Z",
"IsAvailable": true,
"score": 100
}
],
"error": {
"message": ""
}
},
{
"date": "2020-03-25T00:00:00Z",
"status": [
{
"StartTime": "2020-03-25T06:30:00Z",
"EndTime": "2020-03-25T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-25T08:30:00Z",
"EndTime": "2020-03-25T10:30:00Z",
"IsAvailable": true,
"score": 92.44
},
{
"StartTime": "2020-03-25T10:30:00Z",
"EndTime": "2020-03-25T12:30:00Z",
"IsAvailable": false,
"score": 0
}
],
"error": {
"message": ""
}
} ] }

Payload 2

---

{
"Appointment": {
"Durations": 60,
"TimeSlotStartTime": "0001-01-01T00:00:00+00:00",
"TimeSlotEndTime": "0001-01-01T00:00:00+00:00",
"IsDefOnly": false,
"Job": "Medium",
"SoftId": 113291
},
"commonID": "REDRF3243",
"GraceMinutes": 0,
"BufferMinutes": 0,
"RouteRequestList": [
{
"Date": "2020-04-02T00:00:00+00:00",
"JBID": 11936
},
{
"Date": "2020-04-03T00:00:00+00:00",
"JBID": 11936
}
]
}

I need a table created like below where the Jtype, StartTime, EndTime comes from payload1 and JBID comes from Payload2 and the results needs to be merged based on the COMMONID. and there is logic where I need only the first encountered "IsAvailable": true start and end time values

Please advise how can I achieve this

1.  JBID    JType                                START_TIME             END_TIME               COMMONID
 2.  11936    Medium                            2020-03-24T10:30:00    2020-03-24T12:30:00      REDRF3243

[dmarling]

Hello @charan986,

Assuming that the start and end times you want are always in the first IsAvailable=true status in the slotStatusList array and the first JBID in the RouteRequestList array, lines 6 and 7 in the below query will return the table that you are looking for:

| makeresults
| eval data="{ \"appointment\": {\"appointmentId\": 0,\"key\": \"\",\"Durations\": 60,\"JType\": \"Medium\",\"StartTime\": \"0001-01-01T00:00:00Z\",\"EndTime\": \"0001-01-01T00:00:00Z\",\"isDegOnly\": false,\"softId\": 112892 },\"commonID\": \"REDRF3243\",\"slotStatusList\": [{\"date\": \"2020-03-24T00:00:00Z\",\"status\": [{\"StartTime\": \"2020-03-24T06:30:00Z\",\"EndTime\": \"2020-03-24T08:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-24T08:30:00Z\",\"EndTime\": \"2020-03-24T10:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-24T10:30:00Z\",\"EndTime\": \"2020-03-24T12:30:00Z\",\"IsAvailable\": true,\"score\": 100}],\"error\": {\"message\": \"\"}},{\"date\": \"2020-03-25T00:00:00Z\",\"status\": [{\"StartTime\": \"2020-03-25T06:30:00Z\",\"EndTime\": \"2020-03-25T08:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-25T08:30:00Z\",\"EndTime\": \"2020-03-25T10:30:00Z\",\"IsAvailable\": true,\"score\": 92.44},{\"StartTime\": \"2020-03-25T10:30:00Z\",\"EndTime\": \"2020-03-25T12:30:00Z\",\"IsAvailable\": false,\"score\": 0}],\"error\": {\"message\": \"\"}} ] }
{\"Appointment\": {\"Durations\": 60,\"TimeSlotStartTime\": \"0001-01-01T00:00:00+00:00\",\"TimeSlotEndTime\": \"0001-01-01T00:00:00+00:00\",\"IsDefOnly\": false,\"Job\": \"Medium\",\"SoftId\": 113291},\"commonID\": \"REDRF3243\",\"GraceMinutes\": 0,\"BufferMinutes\": 0,\"RouteRequestList\": [{\"Date\": \"2020-04-02T00:00:00+00:00\",\"JBID\": 11936},{\"Date\": \"2020-04-03T00:00:00+00:00\",\"JBID\": 11936}]}"
| rex field=data max_match=0 "(?<data>[^\n]+)"
| mvexpand data
| eval statuses=spath(data, "slotStatusList{}.status{}"), commonID=spath(data, "commonID"), firstAvailable=mvindex(mvfilter(match(statuses, "\"IsAvailable\": true")),0), StartTime=spath(firstAvailable, "StartTime"), EndTime=spath(firstAvailable, "EndTime"), JBID=mvindex(spath(data, "RouteRequestList{}.JBID"),0), JType=spath(data, "appointment.JType")
| stats values(JBID) as JBID values(JType) as JType values(StartTime) as StartTime values(EndTime) as EndTime by commonID

Dealing with multivalued fields in json is always a pain, but can be managed a bit by managing the parent array using mvfilter and mvindex and then using spath on the resulting output, which is what I am doing with statuses=spath(data, "slotStatusList{}.status{}") that creates a multivalued list of the different json payloads of the status array in the slotStatusList array and firstAvailable=mvindex(mvfilter(match(statuses, "\"IsAvailable\": true")),0) which returns the first status that contains "IsAvailable": true. Let me know if anything doesn't make sense.

[to4kawa]

what's index and sourcetype both?

[charan986]

Hi @to4kawa, thanks for responding
these payloads are returned from the same index and source type, if thats what you're asking

[to4kawa]

https://answers.splunk.com/answers/808272/need-to-extract-a-json-value-based-on-a-condition.html
How's this?

[charan986]

I tried using the query from manjunathmeti but its returns empty JBID, So I wanted to post both the payloads and the complete requirement

[to4kawa]

I see, you can do it.

[charan986]

@to4kawa Please find my query below, this results in the table with empty JBID

 1.  JBID    JType                          START_TIME             END_TIME               COMMONID
  2.       Medium                        2020-03-24T10:30:00    2020-03-24T12:30:00      REDRF3243

My search -- | spath body output=body
| spath commonID output= commonID
| eval _raw=body | spath appointment output=appointment
| eval _raw=appointment | spath JType output= JType

| eval _raw=body | spath slotStatusList{} output=slotStatusList
| eval _raw=slotStatusList | spath status output=status

| spath output=IsAvailable status{}.IsAvailable
| spath output=StartTime status{}.StartTime
| spath output=EndTime status{}.EndTime
| where match(IsAvailable, "true") | eval zipped=mvzip(IsAvailable,mvzip(StartTime, EndTime))
| mvexpand zipped
| where match(zipped, "true")
| eval zipped=split(zipped, ",")
| eval IsAvailable=mvindex(zipped,0), StartTime=mvindex(zipped,1), EndTime=mvindex(zipped,2)
| spath body output=body
| eval _raw=body | spath RouteRequestList{} output=RouteRequestList
| eval _raw=RouteRequestList | spath JBID output= JBID
| stats first(*) as * by commonID
| table JBID JType StartTime EndTime commonID