Getting Data In

How to extract a JSON value based on a condition

charan986
Engager

I have payload as below and I need the StartTime and EndTime values where the payload has the first IsAvailable is equal to true

 "StatusList": [
     {
       "date": "2020-03-13T00:00:00Z",
       "status": [
         {
           "StartTime": "2020-03-13T06:30:00Z",
           "EndTime": "2020-03-13T08:30:00Z",
           "IsAvailable": false,
           "score": 91.05
         },
         {
           "StartTime": "2020-03-13T08:30:00Z",
           "EndTime": "2020-03-13T10:30:00Z",
           "IsAvailable": false,
           "score": 94.29
         },
         {
           "StartTime": "2020-03-13T10:30:00Z",
           "EndTime": "2020-03-13T12:30:00Z",
           "IsAvailable": **true**,
           "score": 100
         },
         {
           "StartTime": "2020-03-13T12:30:00Z",
           "EndTime": "2020-03-13T14:30:00Z",
           "IsAvailable": true,
           "score": 96.1
         },
         {
           "StartTime": "2020-03-13T14:30:00Z",
           "EndTime": "2020-03-13T16:30:00Z",
           "IsAvailable": true,
           "score": 90.39
         },
         {
           "StartTime": "2020-03-13T16:30:00Z",
           "EndTime": "2020-03-13T18:30:00Z",
           "IsAvailable": false,
           "score": 0
         }
       ],

How can I achieve this?

0 Karma

woodcock
Esteemed Legend

Your stuff is not valid JSON so first fix that. Once that is true, just do this:

| makeresults 
| eval _raw="{
    \"StatusList\": [{
        \"date\": \"2020-03-13T00:00:00Z\",
        \"status\": [{
                \"StartTime\": \"2020-03-13T06:30:00Z\",
                \"EndTime\": \"2020-03-13T08:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 91.05
            },
            {
                \"StartTime\": \"2020-03-13T08:30:00Z\",
                \"EndTime\": \"2020-03-13T10:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 94.29
            },
            {
                \"StartTime\": \"2020-03-13T10:30:00Z\",
                \"EndTime\": \"2020-03-13T12:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 100
            },
            {
                \"StartTime\": \"2020-03-13T12:30:00Z\",
                \"EndTime\": \"2020-03-13T14:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 96.1
            },
            {
                \"StartTime\": \"2020-03-13T14:30:00Z\",
                \"EndTime\": \"2020-03-13T16:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 90.39
            },
            {
                \"StartTime\": \"2020-03-13T16:30:00Z\",
                \"EndTime\": \"2020-03-13T18:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 0
            }
        ]
    }]
}"
| rename COMMENT AS "I have payload as below and I need the StartTime and EndTime values where the payload has the first IsAvailable is equal to true" 
| kv
| eval FirstTruePos = mvfind('StatusList{}.status{}.IsAvailable', "true")
| eval StartTime = mvindex('StatusList{}.status{}.StartTime', FirstTruePos)
| eval EndTime = mvindex('StatusList{}.status{}.EndTime', FirstTruePos)
| eval MvField = "1 2 3 4 5 6 7"
| makemv MvField
| eval MvField = mvindex(MvField, FirstTruePos)
0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="{ \"appointment\": {
\"appointmentId\": 2,
\"key\": \"\",
\"location\": {
\"latitude\": 30.7086735,
\"longitude\": -96.42541609999999
},
\"jobDuration\": 60,
\"Type\": \"Medium\",
\"timeSlotStartTime\": \"0001-01-01T00:00:00Z\",
\"timeSlotEndTime\": \"0001-01-01T00:00:00Z\",
\"isVehicleOnly\": false,
\"softId\": 12331 }, \"StatusList\": [
{
\"date\": \"2020-03-19T00:00:00Z\",
\"status\": [
{
\"StartTime\": \"2020-03-19T06:30:00Z\",
\"EndTime\": \"2020-03-19T08:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T08:30:00Z\",
\"EndTime\": \"2020-03-19T10:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T10:30:00Z\",
\"EndTime\": \"2020-03-19T12:30:00Z\",
\"IsAvailable\": true,
\"score\": 100
},
{
\"StartTime\": \"2020-03-19T12:30:00Z\",
\"EndTime\": \"2020-03-19T14:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T14:30:00Z\",
\"EndTime\": \"2020-03-19T16:30:00Z\",
\"IsAvailable\": true,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T16:30:00Z\",
\"EndTime\": \"2020-03-19T18:30:00Z\",
\"IsAvailable\": true,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T18:30:00Z\",
\"EndTime\": \"2020-03-19T20:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
}
],
\"error\": {
\"message\": \"\"
    }
} ] }"
| spath path=StatusList{}.status{} output=status
| spath path=StatusList{}.date 
| spath path=StatusList{}.error.message output=error_message
| spath
| fields - _* StatusList*
| stats values(*) as * by status
| spath input=status
| fields - status
| rename appointment.* as *
| table StartTime EndTime IsAvailable score *
| search IsAvailable="true"
0 Karma

charan986
Engager

Hi @to4kawa thanks for responding, may I know how can I generate a table with only one row with StartTime EndTime values for first IsAvailable = true

0 Karma

to4kawa
Ultra Champion

for first IsAvailable = true

In my answer sample, '"StartTime": "2020-03-19T10:30:00Z"` right?

....
| spath path=StatusList{}.status{} output=status
| fields - _*
| mvexpand status
| spath input=status
| table StartTime EndTime IsAvailable score
| search IsAvailable="true"
| head 1
0 Karma

to4kawa
Ultra Champion
....
| spath path=StatusList{}.status{} output=status
| fields - _*
| stats count by status
| spath input=status
| fields - status count
| search IsAvailable="true"

If you only want the fields StartTime EndTime IsAvailable score , this query is better.

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

hi @charan986, If this is a valid json then it should parsed before indexing. But if it is not then try this query:

| makeresults 
| eval _raw="\"StatusList\": [
{
\"date\": \"2020-03-13T00:00:00Z\",
\"status\": [
{
\"StartTime\": \"2020-03-13T06:30:00Z\",
\"EndTime\": \"2020-03-13T08:30:00Z\",
\"IsAvailable\": false,
\"score\": 91.05
},
{
\"StartTime\": \"2020-03-13T08:30:00Z\",
\"EndTime\": \"2020-03-13T10:30:00Z\",
\"IsAvailable\": false,
\"score\": 94.29
},
{
\"StartTime\": \"2020-03-13T10:30:00Z\",
\"EndTime\": \"2020-03-13T12:30:00Z\",
\"IsAvailable\": true,
\"score\": 100
},
{
\"StartTime\": \"2020-03-13T12:30:00Z\",
\"EndTime\": \"2020-03-13T14:30:00Z\",
\"IsAvailable\": true,
\"score\": 96.1
},
{
\"StartTime\": \"2020-03-13T14:30:00Z\",
\"EndTime\": \"2020-03-13T16:30:00Z\",
\"IsAvailable\": true,
\"score\": 90.39
},
{
\"StartTime\": \"2020-03-13T16:30:00Z\",
\"EndTime\": \"2020-03-13T18:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
}
]," 
| eval _raw=replace(replace(_raw, "\"StatusList\": \[", ""), "\],", "") 
| spath output=IsAvailable status{}.IsAvailable 
| spath output=StartTime status{}.StartTime 
| spath output=EndTime status{}.EndTime
| eval zipped=mvzip(IsAvailable,mvzip(StartTime, EndTime)) 
| mvexpand zipped 
| where match(zipped, "true") 
| eval zipped=split(zipped, ",") 
| eval StartTime=mvindex(zipped,0), EndTime=mvindex(zipped,1), IsAvailable=mvindex(zipped,2) | fields - _raw, _time, zipped
0 Karma

charan986
Engager

Hi @manjunathmeti, please find the valid payload where I need to extract the values of StartTime and EndTime where the payload encounters its first isAvailable = true
Using your query the table generates 3 rows where isAvailable =true, whereas I need only one row where the first isAvailable is true is encountered. can you pls let me know what needs to be changed

{ "appointment": {
"appointmentId": 2,
"key": "",
"location": {
"latitude": 30.7086735,
"longitude": -96.42541609999999
},
"jobDuration": 60,
"Type": "Medium",
"timeSlotStartTime": "0001-01-01T00:00:00Z",
"timeSlotEndTime": "0001-01-01T00:00:00Z",
"isVehicleOnly": false,
"softId": 12331 }, "StatusList": [
{
"date": "2020-03-19T00:00:00Z",
"status": [
{
"StartTime": "2020-03-19T06:30:00Z",
"EndTime": "2020-03-19T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T08:30:00Z",
"EndTime": "2020-03-19T10:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T10:30:00Z",
"EndTime": "2020-03-19T12:30:00Z",
"IsAvailable": true,
"score": 100
},
{
"StartTime": "2020-03-19T12:30:00Z",
"EndTime": "2020-03-19T14:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T14:30:00Z",
"EndTime": "2020-03-19T16:30:00Z",
"IsAvailable": true,
"score": 0
},
{
"StartTime": "2020-03-19T16:30:00Z",
"EndTime": "2020-03-19T18:30:00Z",
"IsAvailable": true,
"score": 0
},
{
"StartTime": "2020-03-19T18:30:00Z",
"EndTime": "2020-03-19T20:30:00Z",
"IsAvailable": false,
"score": 0
}
],
"error": {
"message": ""
}
} ] }

0 Karma

charan986
Engager

Hi @manjunathmeti , I have not put the whole payload where I have an array of status , can you let me know how can i extract all the status values from below payload
{ "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": ""
}
} ] }

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Hi @charan986,

Try this:

| makeresults 
| eval _raw="{ \"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\": \"\"
    }
} ] }" 
| spath path=slotStatusList{} output=slotStatusList 
| mvexpand slotStatusList 
| spath input=slotStatusList path=status{} output=status 
| mvexpand status 
| spath input=status 
| spath input=slotStatusList 
| spath 
| table StartTime, EndTime, IsAvailable, score, appointment*, error*, commonID
0 Karma

charan986
Engager

Thanks for responding @manjunathmeti , using the above query i'm able to get only the first status of SlotStatusList array, As show in the picture from the URL below, I need all the status list values from the SlotStausList.
and also can you pls let me know how can get the value of start and end time values where there is a isAvailable = true in the whole payload

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...