Splunk Search

Help with Regex extraction from request

siksaw33
Path Finder

please help extract adsId,offerName, currentProductDescription, offerAccountToken, offerType, offerIdentifier

message={"name":"com. ","level":"info","message":"Create -->|Request identifier : 09accf30-6cf7-4e4f-a633-c19808eff766|CreateAccountOfferEnrollment.v1|REQUEST ---> {\"correlationId\":\"09accf30-6cf7-4e4f-a633-c19808eff766\",\"ccpId\":\"HA6952B\",\"callId\":\"0109\",\"adsId\":\"camar\",\"customerId\":\"63038\",\"eventType\":\"CVP-INSTANT\",\"channelIdentifier\":\"CVP\",\"lineOfBusiness\":\"CCSG\",\"offerName\":\"Additional\",\"offerIdentifier\":\"A000\",\"sourceProductCode\":\"2X\",\"currentProductIdentifier\":\"2X\",\"currentProductDescription\":\"Pl\",\"destinationProductCode\":\"2X\",\"destinationProductName\":\"Plat\",\"fulfillmentCode\":\"GNAS\",\"requestHasSupps\":true,\"offerType\":\"consumer-stand-alone-supp\",\"offerAccountToken\":\"YAS\",\"marketName\":\"US\",\"numberOfSupps\":1,\"calledInAccountToken\":\"YAS\",\"fullName\":{\"firstName\":\"M\",\"lastName\":\"C\",\"middleName\":\"A\",\"prefix\":\"\",\"suffix\":\"\"},\"communicationInformation\":{\"channel\":\"EMAIL\",\"communicationVariables\":[],\"locale\":\"en_US\",\"physicalAddress\":{\"city\":\"P\",\"state\":\"FL\",\"zipCode\":\"33\",\"lines\":[\"48 \",\"#0114\",\"\"]},\"emailAddress\":\"cru@gmail.com\",\"isoCountryCode\":\"840\"},\"enrollmentInformation\":{\"id\":\"2023\",\"is_customer_offline\":false,\"channel_received_datetime\":\"20230109T171713.842 GMT\",\"dynamic_journey\":\"DYNAMIC_INSTANT\",\"rep_id\":\"HA6952B\",\"country_code\":\"840\",\"journey\":\"INSTANT_DECISION\",\"journey_stage\":\"SUPP",\"applicants\":[{\"number\":0,\"amex_relationship\":{\"relationships\":[{\"number\":\"3726\",\"type\""CARD\"}]},\"type\""PRIMARY\"},{\"number\":1,\"type\""NONPRIMARY\",\"has_spending_limit\":false,\"is_signature_available\":true,\"has_cash_restriction\":false,\"experience_id\""829e34d6-e89f-422b-b355-811b1aa2c79c\",\"names\":[{\"language\""EN\",\"name\":{\"first\""V\",\"last\""C\"}}],\"identifiers\":[{\"system\""DELIVERY_METHOD_IDENTIFIER\",\"id\""510DELVIDP256Cn+ has_same_address_as_primary\":false,\"emboss_name\""V\",\"language\""EN\",\"birth_date\""19\",\"spending_limit\":0,\"experience_choices\":[{\"selected_id\""USA_CONSUMER \",\"feature_name\""CARD_DESIGN\"}],\"product\":{\"offer_arrangement_id\""de7c960c46c7\",\"source_code\""A0000FYC4T\",\"short_product_id\""L81\",\"sub_product_code\""2X\"},\"addresses\":[{\"type\""HOME\",\"address\":{\"line1\""7B\",\"city\""HOUSTON\",\"region\""TX\",\"postal_code\""77028-4570\",\"country\""840\"}},{\"type\""TEMPORARY_ADDRESS\",\"address\":{\"line1\""790\",\"city\""HO\",\"region\""T\",\"postal_code\""77\",\"country\""840\"}}]}]},\"misProcessId\""3016428984\"}"}

 

 

@ITWhisperer @VatsalJagani please help

Labels (3)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

As always, I strongly recommend not to use regex to handle structured data as simple strings because there is no guarantee that the string orders will not change.  As much as possible, use SPL's built-in capabilities.  You data looks like compliant JSON with another compliant JSON enclosed inside message.message.  spath is your friend.

Assuming that for some reason, your raw data did not extract message.message, you can do the following:

 

| rename message AS data
| spath input=data
| eval REQUEST = replace(message, ".*REQUEST ---> ", "")
| spath input=REQUEST
| fields - REQUEST data message
| table *

 

Now you have all you need.  Your sample data gives. (Another point: When you illustrate complex data, use code box.  Lots of JSON markups got transformed into smileys which I had to convert back.)

adsIdcallIdcalledInAccountTokenccpIdchannelIdentifiercommunicationInformation.channelcommunicationInformation.emailAddresscommunicationInformation.isoCountryCodecommunicationInformation.localecommunicationInformation.physicalAddress.city
communicationInformation.physicalAddress.lines{}
communicationInformation.physicalAddress.statecommunicationInformation.physicalAddress.zipCodecorrelationIdcurrentProductDescriptioncurrentProductIdentifiercustomerIddestinationProductCodedestinationProductNameenrollmentInformation.applicants{}.amex_relationship.relationships{}.numberenrollmentInformation.applicants{}.amex_relationship.relationships{}.typeenrollmentInformation.applicants{}.experience_idenrollmentInformation.applicants{}.has_cash_restrictionenrollmentInformation.applicants{}.has_spending_limitenrollmentInformation.applicants{}.identifiers{}.idenrollmentInformation.applicants{}.identifiers{}.systemenrollmentInformation.applicants{}.is_signature_availableenrollmentInformation.applicants{}.names{}.languageenrollmentInformation.applicants{}.names{}.name.firstenrollmentInformation.applicants{}.names{}.name.last
enrollmentInformation.applicants{}.number
enrollmentInformation.applicants{}.type
enrollmentInformation.channel_received_datetimeenrollmentInformation.country_codeenrollmentInformation.dynamic_journeyenrollmentInformation.idenrollmentInformation.is_customer_offlineenrollmentInformation.journeyenrollmentInformation.journey_stageenrollmentInformation.rep_ideventTypefulfillmentCodefullName.firstNamefullName.lastNamefullName.middleNamefullName.prefixfullName.suffixlevellineOfBusinessmarketNamenamenumberOfSuppsofferAccountTokenofferIdentifierrequestHasSuppsofferTyperequestHasSuppssourceProductCode
camar0109YASHA6952BCVPEMAILcru@gmail.com840en_USP
48
#0114
 
FL3309accf30-6cf7-4e4f-a633-c19808eff766Pl2X630382XPlat3726CARD829e34d6-e89f-422b-b355-811b1aa2c79cfalsefalse510DELVIDP256Cn+ has_same_address_as_primaryDELIVERY_METHOD_IDENTIFIERtrueENVC
0
1
PRIMARY
NONPRIMARY
20230109T171713.842 GMT840DYNAMIC_INSTANT2023falseINSTANT_DECISIONSUPPHA6952BCVP-INSTANTGNASMCA  infoCCSGUScom.1YASA000Additionalconsumer-stand-alone-supptrue2X

 

View solution in original post

Tags (1)

yuanliu
SplunkTrust
SplunkTrust

As always, I strongly recommend not to use regex to handle structured data as simple strings because there is no guarantee that the string orders will not change.  As much as possible, use SPL's built-in capabilities.  You data looks like compliant JSON with another compliant JSON enclosed inside message.message.  spath is your friend.

Assuming that for some reason, your raw data did not extract message.message, you can do the following:

 

| rename message AS data
| spath input=data
| eval REQUEST = replace(message, ".*REQUEST ---> ", "")
| spath input=REQUEST
| fields - REQUEST data message
| table *

 

Now you have all you need.  Your sample data gives. (Another point: When you illustrate complex data, use code box.  Lots of JSON markups got transformed into smileys which I had to convert back.)

adsIdcallIdcalledInAccountTokenccpIdchannelIdentifiercommunicationInformation.channelcommunicationInformation.emailAddresscommunicationInformation.isoCountryCodecommunicationInformation.localecommunicationInformation.physicalAddress.city
communicationInformation.physicalAddress.lines{}
communicationInformation.physicalAddress.statecommunicationInformation.physicalAddress.zipCodecorrelationIdcurrentProductDescriptioncurrentProductIdentifiercustomerIddestinationProductCodedestinationProductNameenrollmentInformation.applicants{}.amex_relationship.relationships{}.numberenrollmentInformation.applicants{}.amex_relationship.relationships{}.typeenrollmentInformation.applicants{}.experience_idenrollmentInformation.applicants{}.has_cash_restrictionenrollmentInformation.applicants{}.has_spending_limitenrollmentInformation.applicants{}.identifiers{}.idenrollmentInformation.applicants{}.identifiers{}.systemenrollmentInformation.applicants{}.is_signature_availableenrollmentInformation.applicants{}.names{}.languageenrollmentInformation.applicants{}.names{}.name.firstenrollmentInformation.applicants{}.names{}.name.last
enrollmentInformation.applicants{}.number
enrollmentInformation.applicants{}.type
enrollmentInformation.channel_received_datetimeenrollmentInformation.country_codeenrollmentInformation.dynamic_journeyenrollmentInformation.idenrollmentInformation.is_customer_offlineenrollmentInformation.journeyenrollmentInformation.journey_stageenrollmentInformation.rep_ideventTypefulfillmentCodefullName.firstNamefullName.lastNamefullName.middleNamefullName.prefixfullName.suffixlevellineOfBusinessmarketNamenamenumberOfSuppsofferAccountTokenofferIdentifierrequestHasSuppsofferTyperequestHasSuppssourceProductCode
camar0109YASHA6952BCVPEMAILcru@gmail.com840en_USP
48
#0114
 
FL3309accf30-6cf7-4e4f-a633-c19808eff766Pl2X630382XPlat3726CARD829e34d6-e89f-422b-b355-811b1aa2c79cfalsefalse510DELVIDP256Cn+ has_same_address_as_primaryDELIVERY_METHOD_IDENTIFIERtrueENVC
0
1
PRIMARY
NONPRIMARY
20230109T171713.842 GMT840DYNAMIC_INSTANT2023falseINSTANT_DECISIONSUPPHA6952BCVP-INSTANTGNASMCA  infoCCSGUScom.1YASA000Additionalconsumer-stand-alone-supptrue2X

 

Tags (1)

richgalloway
SplunkTrust
SplunkTrust

What have you tried so far?

---
If this reply helps you, Karma would be appreciated.
0 Karma

siksaw33
Path Finder

Nothing. I am  a noob, non-technical PM. I want to learn splunk search, if you can share a beginners link and also please help solve this that would be great. @richgalloway 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Generally, the community is more inclined to help those who have put in some effort on their own.

Fields can be extracted using the rex command.  I find it easiest, especially when new, to use one rex command for each field to extract.

| rex "adsId\\\\\":\\\\\"(?<adsId>[^\\]+)"
---
If this reply helps you, Karma would be appreciated.
0 Karma

siksaw33
Path Finder
  1. @richgalloway This gives me error
    Error in 'rex' command: Encountered the following error while compiling the regex 'adsId\\":\\"(?<adsId>[^\]+)': Regex: missing terminating ] for character class.
  2. do I use the same one for other fields? just replace adsID with offerName etc?
  3. also how did you build the regex any knowledge resource or link you can share? how do I learn that so I can be helped by the community?
0 Karma

richgalloway
SplunkTrust
SplunkTrust

@siksaw33 wrote:

@richgalloway This gives me error
Error in 'rex' command: Encountered the following error while compiling the regex 'adsId\\":\\"(?<adsId>[^\]+)': Regex: missing terminating ] for character class.

That error means the escaping is off.  The '\\":\\"' part tells me there is one too many backslashes before each quotation mark.  Try 

| rex "adsId\\\\":\\\\"(?<adsId>[^\\]+)"

do I use the same one for other fields? just replace adsID with offerName etc?

Yes

also how did you build the regex any knowledge resource or link you can share? how do I learn that so I can be helped by the community?

There are many resources on the Internet for learning and experimenting with regular expressions.  I like regex101.com for testing expressions.  Also, there are a few talks available at conf.splunk.com.


 

---
If this reply helps you, Karma would be appreciated.
0 Karma

siksaw33
Path Finder

Thank you but I still get the same error with the new solution

@richgalloway 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...