spath

[jrowland1230]

I want to do some analysis on "status" below but having a hard time getting to "status". I start with:

| spath path=log.content
| table log.content

but that only gives me the json array from content. I've tried "spath path=log.content{}" and "spath path=log.content{}.status but it ends up empty. I want to be able to do a ternary operation on "status" like the sample below:

| mvexpand log.content{}.status

| eval Service=if('log.content{}.status'="CANCELLED", "Cancelled", if('log.content{}.status'="BAY", "Bay", NULL))

| where isnotnull(Service)
| stats count by Service

[tej57]

Hello @jrowland1230 ,

I tried ingesting the sample data in my environment and the following SPL works.

source="community_question.txt" host="my_host" sourcetype="jrowland_json"
| spath path=log output=log
| spath input=log path=content output=content
| rex field=content "status\":\"(?<status_extract>\w+)\""
| table status_extract
| eval Service=case(status_extract="CANCELLED","Cancelled",status_extract="BAY","BAY",true(),"NULL")

Please refer to the following screenshot as well:

Thanks,
Tejas.

---

If the above solution helps, an upvote is appreciated.

[jrowland1230]

{"cluster_id":"uscentral","kubernetes":{"host":"worker-uscentral","labels":{"app":"service","version":"v1"},"namespace_name":"om","pod_name":"service-"},"log":{"config":{"headers":{"Accept":"application/json","Accept-Encoding":"gzip, deflate","Content-Type":"application/json","WM_CONSUMER.ID":"222222-d1bf-4141-a275-2232323232","WM_SVC.ENV":"prod","WM_SVC.NAME":"SERVICEHISTORY","cookie":"a36e-5b4c7c82f05b","wm_qos.correlation_id":"d40031a5271c463d-00","x-o-correlation-id":"00-c0","x-user":"OMN"},"searchParams":{"keyWag":"846","storeNumber":"xx"}},"content":"[{\"status\":\"SERVICE_COMPLETE\",\"OId\":\"eb6fc890-526cb1b\",\"vehicle\":{\"vehicleId\":\"54c4b31\",\"vin\":\"2c3cdxbg8jh255808\",\"year\":\"2018\",\"make\":\"DODGE\",\"model\":\"CHARGER\",\"licensePlate\":\"777\",\"licensePlateState\":\"texas\",\"documentType\":\"vehicle\",\"sourceUpdateRequired\":false,\"isDually\":false},\"customer\":{\"id\":null,\"wmtProfileId\":\"-a0b4-447f-bd99-\",\"communicationConsent\":null,\"firstName\":\"xxx\",\"lastName\":\"xxx\",\"phoneNumber\":\"1111111\",\"countryCode\":1,\"email\":null,\"address\":{\"address1\":\"222 Lane\",\"address2\":null,\"city\":\"long\",\"state\":\"Texas\",\"zip\":\"77535\",\"country\":\"United States\"},\"createdBy\":null,\"dateCreated\":null,\"updatedBy\":null,\"dateUpdated\":null,\"cId\":\"-923a-4c076501f8b0b\",\"documentType\":\"customer\",\"ccpaOptOut\":false,\"legacyIds\":null,\"blockingKey\":null,\"similarRecords\":null,\"createForced\":false,\"linkSource\":null,\"recordSource\":null},\"odometer\":{\"value\":5,\"unit\":\"MILE\"},\"keyTag\":\"777\",\"serviceCart\":{\"serviceItems\":[{\"id\":\"5a92-97304651e9fe\",\"iteId\":\"370122\",\"name\":\"High mileage featured\",\"upc\":\"999\",\"quantity\":0,\"serviceType\":\"OIL_AND_LUBE\",\"components\":[{\"componentType\":\"OIL\",\"product\":{\"itId\":\"99\",\"upc\":\"00071611012225\",\"name\":\"Pennzoil High Mileage 5W20 Motor Oil Ecobox, 1 Quart -\",\"quantity\":5.900,\"retailPrice\":20.72,\"cusredit\":0,\"includedQuantity\":5,\"attributes\":[{\"key\":\"brand\",\"value\":\"Pennzoil\"}]},\"configurations\":[]},{\"componentType\":\"OIL_FILTER\",\"product\":{\"itemId\":\"100992364\",\"upc\":\"00060223\",\"name\":\"FRAM Core 11665 Oil Filter - Offer Valid for In-store Oil Change Only Fits select: 2014-2018 JEEP, 2015-2019 JEEP UNLIMITED\",\"quantity\":1,\"retailPrice\":1.52,\"customerCredit\":0,\"includedQuantity\":1,\"attributes\":[{\"key\":\"brand\",\"value\":\"FRAM\"}]},\"configurations\":[]}],\"retailPrice\":44.88,\"laborCost\":29.76,\"ifNeeded\":false,\"serviceIfNecessary\":false}],\"total\":47.33},\"storeNumber\":\"744\",\"creationDate\":\"2023-05-21T22:53:49.915774Z\",\"documentType\":\"wr\",\"amountPaid\":0,\"markDowns\":[],\"isNewOrder\":true,\"isCarryInOrder\":false,\"isCarryOutOrder\":false,\"isFulfillmentInNextGenBay\":true,\"isVehicleDamageCapturedInSW\":false}]","context":{"duration":"-","level":1,"parentTxId":"00-cb4ec2ec5795a1a7a11-d40031a5271c463d-00","sessionId":"a08f812f-012-18f9d848c23b26","topTxId":"a08f812f-8f9d848c238d4","txId":"a09d848c23775"},"event":"UPSTREAM RESPONSE - GET_ACTIVE_SERVICE_DETAILS","level":30,"msg":"","name":"stdout","oo_env":"prod","reqId":"00-cb4ec2d7e2f031a5271c463d-00","resolverInfo":{},"tags":["info"],"tenant":"US-B2C-undefined","time":1716334661094,"timings":{"dns":2,"download":0,"firstByte":417,"request":0,"tcp":0,"tls":15,"total":434,"wait":0},"type":"INFO","url":"https://localhost/active"},"time":"2024-05-21T23:37:41.094957544Z"}

[ITWhisperer]

The best way to do this is probably by using the json functions in combination with spath. Try something like this:

| spath path=log.content output=content
| eval content=json_array_to_mv(content)
| mvexpand content
| spath input=content path=status
| eval Service=if(status="CANCELLED", "Cancelled", if(status="BAY", "Bay", null()))
| where isnotnull(Service)
| stats count by Service

[jrowland1230]

Within the json under the node"content" there is another array where I need to access a value serviceCart.serviceItems{}. serviceType  "serviceType" as shown here:

\"serviceCart\":{\"serviceItems\":[{\"id\":\"5a92-97304651e9fe\",\"iteId\":\"370122\",\"name\":\"High mileage featured\",\"upc\":\"999\",\"quantity\":0,\"serviceType\":\"OIL_AND_LUBE\",\"components\":[{\"componentType\":\"OIL\",\"product\":{\"itId\":\"99\",\"upc\":\"00071611012225\",\"name\":\"Pennzoil High Mileage 5W20 Motor Oil Ecobox, 1 Quart -\",\"quantity\":5.900,\"retailPrice\":20.72,\"cusredit\":0,\"includedQuantity\":5,\"attributes\":[{\"key\":\"brand\",\"value\":\"Pennzoil\"}]},\"configurations\":[]},{\"componentType\":\"OIL_FILTER\",\"product\":{\"itemId\":\"100992364\",\"upc\":\"00060223\",\"name\":\"FRAM Core 11665 Oil Filter - Offer Valid for In-store Oil Change Only Fits select: 2014-2018 JEEP, 2015-2019 JEEP UNLIMITED\",\"quantity\":1,\"retailPrice\":1.52,\"customerCredit\":0,\"includedQuantity\":1,\"attributes\":[{\"key\":\"brand\",\"value\":\"FRAM\"}]}

 Using the same technique above I am not able to obtain the value. Here's what I tried.

| spath path=log.content output=content
| eval content=json_array_to_mv(content)
| mvexpand content
| spath input=content path=status
| spath input=content path=serviceCart.serviceItems{}.serviceType

| eval Service=if((serviceCart.serviceItems{}.serviceType="OIL" OR serviceCart.serviceItems{}.serviceType="TIRE") AND status="CANCELLED", "Cancelled", if((serviceCart.serviceItems{}.serviceType="OIL" OR serviceCart.serviceItems{}.serviceType="TIRE") AND status="BAY", "Bay", null()))

| where isnotnull(Service)

| stats count by Service

But "serviceType" is empty in the ternary check. However when I check it in "table serviceCart.serviceItems{}.serviceType" I see the value. I tried using mvexpand on the array "serviceCart.serviceItems" (not shown above) as well but still empty

[ITWhisperer · Accepted solution]

Try like this (or put the fields in the if function in single quotes)

| spath path=log.content output=content
| eval content=json_array_to_mv(content)
| mvexpand content
| spath input=content path=status
| spath input=content path=serviceCart.serviceItems{}.serviceType output=serviceType

| eval Service=if((serviceType="OIL" OR serviceType="TIRE") AND status="CANCELLED", "Cancelled", if((serviceType="OIL" OR serviceType="TIRE") AND status="BAY", "Bay", null()))

[jrowland1230]

Base on my complete solution is there a way to remove duplicates based on two values (workOrderId and Status) before aggregating ? from "| spath input=content path=workOrderId"

index="wcnp_acc-omni" "*acc-omni-service-prod*"
| spath path=log.content output=content
| eval content=json_array_to_mv(content)
| mvexpand content
| spath input=content path=status
| spath input=content path=serviceCart.serviceItems{}.serviceType output=serviceType
| eval created=if(serviceType="OIL_AND_LUBE" AND status="CREATED", 1, 0)
| eval completed=if(serviceType="OIL_AND_LUBE" AND status="SERVICE_COMPLETE", 1, 0)
| where completed > 0 OR created > 0
| stats sum(created) as createdTotal, sum(completed) as completedTotal
| eval total = (completedTotal/createdTotal) * 100
| table total, createdTotal, completedTotal
| rename total as "Total Completion Rate Oil/Lube" createdTotal as "Total Created" completedTotal as "Total Completed"

[ITWhisperer]

| dedup workOrderId Status

[jrowland1230]

Thank you for the quick turnaround. I just missed the OUPUT.

[jrowland1230]

Thank you for the swift response. It looks to be working as expected.

[ITWhisperer]

Rather than sharing a picture of the event (which is not a lot of use), please could you share the raw event in a code block </> (anonymised of course)