topic Re: spath in Splunk Search

spath

jrowland1230 — Tue, 21 May 2024 21:07:26 GMT

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

Re: spath

ITWhisperer — Tue, 21 May 2024 21:19:30 GMT

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)

Re: spath

jrowland1230 — Wed, 22 May 2024 00:08:18 GMT

{"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"}

Re: spath

tej57 — Wed, 22 May 2024 06:40:27 GMT

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.

Re: spath

ITWhisperer — Wed, 22 May 2024 08:26:11 GMT

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

Re: spath

jrowland1230 — Wed, 22 May 2024 12:33:06 GMT

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

Re: spath

jrowland1230 — Wed, 22 May 2024 16:53:17 GMT

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

Re: spath

ITWhisperer — Wed, 22 May 2024 17:13:46 GMT

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()))

Re: spath

jrowland1230 — Wed, 22 May 2024 18:25:53 GMT

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

Re: spath

jrowland1230 — Wed, 29 May 2024 16:53:44 GMT

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"

Re: spath

ITWhisperer — Wed, 29 May 2024 16:29:49 GMT

| dedup workOrderId Status