Get Json object from the splunk log as a field

[karthi25]

I am having the splunk log in the following format:

2018-03-02T17:02:27.453185+00:00 ESP-Finance-NPE.development.abctestprocessor-dev a36c4e54-dc5a-4d23-afb3-10f1661b19b4[[APP/PROC/WEB/0]]: cf_foundation=*** cf_app_name=*** cf_app_id=a36c4e54-dc5a-4d23-afb3-10f1661b19b4 cf_org_name=**** cf_org_id=*** cf_space_name=development cf_space_id=*** .source.s_cf_apps 2018-03-02 09:02:27.452 ERROR 14 --- [TaskExecutor-82] c.tmobile.finance.service.LoggerService  : {"host_endpoint":"","domain":"CUSTOMER_FINANCE","component":"abctestProcessor","log_type":"ERROR","space_name":"development","event_source":"DEEP_PROXY","api_name":"test_abc","api_id":"a36c4e54-dc5a-4d23-afb3-10f1661b19b4","message_format":"application/json","error_code":0,"stack_trace":"com.tmobile.deep.abc.exception.FinanceSystemE"operation_name":"testEquipmentSerialNumberUpdateCompleted","testId":"testString","msisdn":"testString","guid":"testString","activityid":"testString","api_request":{"eventId":"event123","sourceId":null,"eventType":"testEquipmentSerialNumberUpdateCompleted","eventTime":{"offset":{"totalSeconds":0,"id":"Z","rules":{"fixedOffset":true,"transitions":[],"transitionRules":[]}},"hour":0,"minute":30,"second":21,"nano":298000000,"year":2018,"month":"FEBRUARY","dayOfMonth":10,"dayOfWeek":"SATURDAY","dayOfYear":41,"monthValue":2},"eventProducerId":"Produce123","eventVersion":"testString","specifications":[{"name":"testString","value":"testString"}],"auditInfo":{"customerId":"testString","accountNumber":"testString","universalLineId":"testString","lineId":"testString","phoneNumber":"testString","iamUniqueId":"testString","batchId":"testString","orderId":"testString"},"headerReference":{"activityId":"testString","applicationId":"testString","applicationUserId":"testString","authCustomerId":"testString","authFinancialAccountId":"testString","authLineOfServiceId":"testString","channelId":"testString","dealerCode":"testString","interactionId":"testString","masterDealerCode":"testString","segmentationId":"testString","senderId":"testString","sessionId":"testString","storeId":"testString","terminalId":"testString","tillId":"testString","workflowId":"testString","timestamp":{"offset":{"totalSeconds":0,"id":"Z","rules":{"fixedOffset":true,"transitions":[],"transitionRules":[]}},"hour":0,"minute":30,"second":21,"nano":298000000,"year":2018,"month":"FEBRUARY","dayOfMonth":10,"dayOfWeek":"SATURDAY","dayOfYear":41,"monthValue":2}},"payload":{"createtestRequest":{"header":{"senderid":"testString","channelid":"testString"},"tests":{"account":{"universalLineId":"testString"},"sourceTransactionTime":"2018-02-10T00:30:21.298Z","phoneNumber":"testString","purchasedEquipment":{"description":"testString","imei":"testString"},"testId":"testString"}}},"processContext":{"rootId":"67310650-1e3b-11e8-945d-a5cf584f50bc","parentId":"67310650-1e3b-11e8-945d-a5cf584f50bc","spaceName":"development"},"currentRetryCount":0,"maxRetryAttempts":0,"retryDelay":0,"taskId":null,"errorData":null,"status":null,"subStatus":null},"api_response":"org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults","httpStatusCode":"503","key":"testString","additionalAttributes":{}}

Now I want to extract all the payload content , (i.e) my query should returns the following

   {"createtestRequest":{"header":{"senderid":"testString","channelid":"testString"},"tests":{"account":{"universalLineId":"testString"},"sourceTransactionTime":"2018-02-10T00:30:21.298Z","phoneNumber":"testString","purchasedEquipment":{"description":"testString","imei":"testString"},"testId":"testString"}}},"processContext":{"rootId":"67310650-1e3b-11e8-945d-a5cf584f50bc","parentId":"67310650-1e3b-11e8-945d-a5cf584f50bc","spaceName":"development"},"currentRetryCount":0,"maxRetryAttempts":0,"retryDelay":0,"taskId":null,"errorData":null,"status":null,"subStatus":null}

I tried the following query , but it returns nothing,

index=*** sourcetype=*** "cf_foundation=px-***" cf_org_name="ESP-Finance-NPE" cf_app_name="***-dev"| rex field=_raw "eventId\"\:\"(?<eventId>[^\"]+)" |search eventId=event123 |rex "(?< payload>{[^}]+})"| table eventId,payload

Can anyone please suggest me what am doing wrong and provide me the correct solution for it.

[niketn]

@karthi25, if you are extracting eventId to filter "event123" from your _raw events then rex field extraction is not required. You can filter directly in your base search using "\"eventid\":\"event123\""

Following is a run anywhere search based on the sample data provided which extracts the payload data

| makeresults
| eval _raw="2018-03-02T17:02:27.453185+00:00 ESP-Finance-NPE.development.abctestprocessor-dev a36c4e54-dc5a-4d23-afb3-10f1661b19b4[[APP/PROC/WEB/0]]: cf_foundation=*** cf_app_name=*** cf_app_id=a36c4e54-dc5a-4d23-afb3-10f1661b19b4 cf_org_name=**** cf_org_id=*** cf_space_name=development cf_space_id=*** .source.s_cf_apps 2018-03-02 09:02:27.452 ERROR 14 --- [TaskExecutor-82] c.tmobile.finance.service.LoggerService  : {\"host_endpoint\":\"\",\"domain\":\"CUSTOMER_FINANCE\",\"component\":\"abctestProcessor\",\"log_type\":\"ERROR\",\"space_name\":\"development\",\"event_source\":\"DEEP_PROXY\",\"api_name\":\"test_abc\",\"api_id\":\"a36c4e54-dc5a-4d23-afb3-10f1661b19b4\",\"message_format\":\"application/json\",\"error_code\":0,\"stack_trace\":\"com.tmobile.deep.abc.exception.FinanceSystemE\"operation_name\":\"testEquipmentSerialNumberUpdateCompleted\",\"testId\":\"testString\",\"msisdn\":\"testString\",\"guid\":\"testString\",\"activityid\":\"testString\",\"api_request\":{\"eventId\":\"event123\",\"sourceId\":null,\"eventType\":\"testEquipmentSerialNumberUpdateCompleted\",\"eventTime\":{\"offset\":{\"totalSeconds\":0,\"id\":\"Z\",\"rules\":{\"fixedOffset\":true,\"transitions\":[],\"transitionRules\":[]}},\"hour\":0,\"minute\":30,\"second\":21,\"nano\":298000000,\"year\":2018,\"month\":\"FEBRUARY\",\"dayOfMonth\":10,\"dayOfWeek\":\"SATURDAY\",\"dayOfYear\":41,\"monthValue\":2},\"eventProducerId\":\"Produce123\",\"eventVersion\":\"testString\",\"specifications\":[{\"name\":\"testString\",\"value\":\"testString\"}],\"auditInfo\":{\"customerId\":\"testString\",\"accountNumber\":\"testString\",\"universalLineId\":\"testString\",\"lineId\":\"testString\",\"phoneNumber\":\"testString\",\"iamUniqueId\":\"testString\",\"batchId\":\"testString\",\"orderId\":\"testString\"},\"headerReference\":{\"activityId\":\"testString\",\"applicationId\":\"testString\",\"applicationUserId\":\"testString\",\"authCustomerId\":\"testString\",\"authFinancialAccountId\":\"testString\",\"authLineOfServiceId\":\"testString\",\"channelId\":\"testString\",\"dealerCode\":\"testString\",\"interactionId\":\"testString\",\"masterDealerCode\":\"testString\",\"segmentationId\":\"testString\",\"senderId\":\"testString\",\"sessionId\":\"testString\",\"storeId\":\"testString\",\"terminalId\":\"testString\",\"tillId\":\"testString\",\"workflowId\":\"testString\",\"timestamp\":{\"offset\":{\"totalSeconds\":0,\"id\":\"Z\",\"rules\":{\"fixedOffset\":true,\"transitions\":[],\"transitionRules\":[]}},\"hour\":0,\"minute\":30,\"second\":21,\"nano\":298000000,\"year\":2018,\"month\":\"FEBRUARY\",\"dayOfMonth\":10,\"dayOfWeek\":\"SATURDAY\",\"dayOfYear\":41,\"monthValue\":2}},\"payload\":{\"createtestRequest\":{\"header\":{\"senderid\":\"testString\",\"channelid\":\"testString\"},\"tests\":{\"account\":{\"universalLineId\":\"testString\"},\"sourceTransactionTime\":\"2018-02-10T00:30:21.298Z\",\"phoneNumber\":\"testString\",\"purchasedEquipment\":{\"description\":\"testString\",\"imei\":\"testString\"},\"testId\":\"testString\"}}},\"processContext\":{\"rootId\":\"67310650-1e3b-11e8-945d-a5cf584f50bc\",\"parentId\":\"67310650-1e3b-11e8-945d-a5cf584f50bc\",\"spaceName\":\"development\"},\"currentRetryCount\":0,\"maxRetryAttempts\":0,\"retryDelay\":0,\"taskId\":null,\"errorData\":null,\"status\":null,\"subStatus\":null},\"api_response\":\"org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults\",\"httpStatusCode\":\"503\",\"key\":\"testString\",\"additionalAttributes\":{}}"
| search "\"eventid\":\"event123\""
| rex "{\"eventId\":\"(?<eventId>[^\"]+)"
| rex ",\"payload\":(?<payload>.*),\"api_response\""
| table eventId payload

PS: I have retained eventId field in case you need it to be displayed in result.

[richgalloway]

According to regex101.com, your regex strings are good, except for the space before "payload".
I would try changing "cf_foundation=px-***" to cf_foundation="px-***".
If you still get nothing, delete everything after the first pipe and make sure you get the events you expect. Then add each pipe in turn to verify they do what you expect.