topic Re: Create a table based on values from two JSON payloads in Splunk Search

Create a table based on values from two JSON payloads

charan986 — Tue, 10 Mar 2020 00:13:47 GMT

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

Re: Create a table based on values from two JSON payloads

to4kawa — Tue, 10 Mar 2020 00:26:00 GMT

what's index and sourcetype both?

Re: Create a table based on values from two JSON payloads

charan986 — Tue, 10 Mar 2020 00:28:08 GMT

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

Re: Create a table based on values from two JSON payloads

to4kawa — Tue, 10 Mar 2020 00:44:51 GMT

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

Re: Create a table based on values from two JSON payloads

charan986 — Tue, 10 Mar 2020 00:50:14 GMT

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

Re: Create a table based on values from two JSON payloads

to4kawa — Tue, 10 Mar 2020 00:57:10 GMT

I see, you can do it.

Re: Create a table based on values from two JSON payloads

charan986 — Tue, 10 Mar 2020 01:10:10 GMT

@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

Re: Create a table based on values from two JSON payloads

dmarling — Thu, 12 Mar 2020 13:04:59 GMT

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.