Splunk Search

Get Json object from the splunk log as a field

karthi25
Path Finder

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.

0 Karma

niketn
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...