topic Re: fields are not extracted properly in Splunk Search

fields are not extracted properly

srivenna — Wed, 12 Jan 2022 19:33:50 GMT

recently we onboarded these logs but most of the fields are not extracted though these values are mentioned with =. I am trying to extract batch_id , tran_id and pricing hashcode and rules hashcode. I tried to extract from GUI but i am seeing lot of mismatches. can anyone help me with this.

here are sample logs

{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014308933}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:02.322|[DefaultThreadPool-18] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|batch_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E02:414|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996302000}

{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014334358}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:27.035|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996327000}

Re: fields are not extracted properly

yuanliu — Thu, 13 Jan 2022 07:08:52 GMT

Clearly the logs are in JSON. However, it is unclear whether logs are ingested with a JSON sourcetype, that is, whether JSON extraction is performed at indexing time. If they are, Splunk would already have fields like logGroup, aws_acctid, cwtimestamp and, most importantly, cwmessage. All key-value pairs are in cwmessage.

If cwmessage is already available to you, try

| rename _raw as temp, cwmessage as _raw | kv pairdelim="|" kvdelim="=" | rename _raw as cwmessage, temp as _raw

kv is an alias of extract. If the log is ingested without JSON format but _raw events are still valid JSON as you illustrated, add spath command at the beginning, i.e.,

Note the forward and backward renames are just to preserve original fields. If that's not a concern for next filters, "rename cwmessage AS _raw" before kv suffices.

Example: Given dataset

_time	_raw
2022-01-12 06:05:02	{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014308933}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:02.322\|[DefaultThreadPool-18] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS\|component=LD-PRICING\|user_id=c9273wne\|seller_id=165700007\|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02\|tran_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85\|batch_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85\|dealset_id=116784\|execution_type=WholeLoan\|loan_count=1\|time=\|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E02:414\|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996302000}
2022-01-12 06:05:27	{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014334358}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:27.035\|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS\|component=LD-PRICING\|user_id=c9273wne\|seller_id=165700007\|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02\|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d\|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d\|dealset_id=116784\|execution_type=WholeLoan\|loan_count=1\|time=\|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457\|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996327000}

the above filters will render something like the following, including fields of your interest.

_time

_raw

appcode

aws_acctid

appshortname

aws_region

batch_id

component

cwmessage

cwtimestamp

dealset_id

execution_time

lifestyle

loan_count

logGroup

logStream

messageId

seller_id

session_id

splunkdata.rvt

splunkdata.shard_id

splunkdata.splkhf

tran_id

user_id

2022-01-12 06:05:02

FVV

189693026861

ldcs

us-east-1

9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85

LD-PRICING

2022-01-12 14:05:02.322|[DefaultThreadPool-18] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|batch_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E02:414|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007

1641996302000

116784

WholeLoan

devl-shared

ldcs-devl-eb-06-webapp-Application

ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log

ID:SOADevl-ems08.752D61D05D2DBE2E02:414

165700007

D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02

1642014308933

000000000020

spitsi-acpt-log-heavy-4

9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85

c9273wne

2022-01-12 06:05:27

{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014334358}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:27.035|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996327000}

FVV

189693026861

ldcs

us-east-1

751b1112-0511-4dbd-b94c-a6409c23b20d

LD-PRICING

2022-01-12 14:05:27.035|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007

1641996327000

116784

WholeLoan

devl-shared

ldcs-devl-eb-06-webapp-Application

ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log

ID:SOADevl-ems08.752D61D05D2DBE2E0A:457

165700007

D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02

1642014334358

000000000020

spitsi-acpt-log-heavy-4

751b1112-0511-4dbd-b94c-a6409c23b20d

c9273wne

Hope this helps.

Re: fields are not extracted properly

srivenna — Thu, 13 Jan 2022 14:31:01 GMT

Thank you for your response. yes Splunk has extracted all these fields like logGroup, aws_acctid, cwtimestamp and, most importantly, cwmessage. how can i extract fields from cwmessagge seperated with pipe. Mainly i am looking for fields tran_id, batch_id and pricing hascode and rules hashcode. Do i need to write any props or i can do field extraction with the query?

Re: fields are not extracted properly

srivenna — Thu, 13 Jan 2022 14:46:56 GMT

I tried this

| spath
| rename _raw as temp, cwmessage as _raw
| kv pairdelim="|" kvdelim="="

All = fields are extracted. But fields with " : " are not extracted. I am specifically looking for Pricing hashcode and rules hashcode. Thanks again.

Re: fields are not extracted properly

yuanliu — Sat, 15 Jan 2022 03:37:42 GMT

If you already see fields like logGroup, that means spath is redundant. (See Alternatives to the spath command.) To capture those key-value pairs separated by :, try

The rex command is to establish a unique delimiter. The pairs are separated by " - " (space-space), but "-" appears in some values. I notice that "+" is not used anywhere in cwmessage, therefore "+" would be a good candidate. The above does not restore cwmessage to its original content as I sense that you wouldn't use it further.

Using the same sample data, the output is

_time

_raw

Pricing_Info___Pricing_Hashcode

Rules_Hashcode

appcode

aws_acctid

aws_appshortname

aws_region

batch_id

component

cwmessage

cwtimestamp

dealset_id

execution_type

investorCommitmentIdentifier

lifecycle

loand_count

logGroup

logStream

messageId

poolIdentifier

sellerId

sellerLoanIdentifier

seller_id

session_id

splunkdata.rvt

splunkdata.shard_id

splunkdata.splkhf

tran_id

uniqueClientDealIdentifier

user_id

2022-01-12 06:05:02

1761264532

-1500207091

FVV

189693026861

ldcs

us-east-1

9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85

LD-PRICING

2022-01-12 14:05:02.322|[DefaultThreadPool-18] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|batch_id=9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E02:414|ENDOFFIELDS + Pricing Info ~ Pricing Hashcode: 1761264532 + Rules Hashcode: -1500207091 + uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 + sellerLoanIdentifier: BTP22_0111_B10 + poolIdentifier: null + investorCommitmentIdentifier: 116784 + sellerId: 165700007

1641996302000

116784

WholeLoan

116784

devl-shared

ldcs-devl-eb-06-webapp-Application

ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log

ID:SOADevl-ems08.752D61D05D2DBE2E02:414

null

165700007

BTP22_0111_B10

165700007

D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02

1642014308933

000000000020

spitsi-acpt-log-heavy-4

9a6e8ba3-2c01-4b18-bbfb-88a854bbdb85

a37801e4-dbe6-4c3a-bc26-17d1a78a0b28

c9273wne

2022-01-12 06:05:27

{"logGroup": "ldcs-devl-eb-06-webapp-Application", "logStream": "ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log", "aws_acctid": "189693026861", "aws_region": "us-east-1", "splunkdata": {"shard_id": "000000000020", "splkhf": "spitsi-acpt-log-heavy-4", "rvt": 1642014334358}, "lifecycle": "devl-shared", "aws_appshortname": "ldcs", "appcode": "FVV", "cwmessage": "2022-01-12 14:05:27.035|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457|ENDOFFIELDS - Pricing Info ~ Pricing Hashcode: 1761264532 - Rules Hashcode: -1500207091 - uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 - sellerLoanIdentifier: BTP22_0111_B10 - poolIdentifier: null - investorCommitmentIdentifier: 116784 - sellerId: 165700007 ", "cwtimestamp": 1641996327000}

1761264532

-1500207091

FVV

189693026861

ldcs

us-east-1

751b1112-0511-4dbd-b94c-a6409c23b20d

LD-PRICING

2022-01-12 14:05:27.035|[DefaultThreadPool-20] LD-PRICING-INFO c.f.l.pricing.mapper.DealSetsMapper STARTOFFIELDS|component=LD-PRICING|user_id=c9273wne|seller_id=165700007|session_id=D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02|tran_id=751b1112-0511-4dbd-b94c-a6409c23b20d|batch_id=751b1112-0511-4dbd-b94c-a6409c23b20d|dealset_id=116784|execution_type=WholeLoan|loan_count=1|time=|messageId=ID:SOADevl-ems08.752D61D05D2DBE2E0A:457|ENDOFFIELDS + Pricing Info ~ Pricing Hashcode: 1761264532 + Rules Hashcode: -1500207091 + uniqueClientDealIdentifier: a37801e4-dbe6-4c3a-bc26-17d1a78a0b28 + sellerLoanIdentifier: BTP22_0111_B10 + poolIdentifier: null + investorCommitmentIdentifier: 116784 + sellerId: 165700007

1641996327000

116784

WholeLoan

116784

devl-shared

ldcs-devl-eb-06-webapp-Application

ip-10-108-18-243 (i-004009051755596bb) - ld-pricing.log

ID:SOADevl-ems08.752D61D05D2DBE2E0A:457

null

165700007

BTP22_0111_B10

165700007

D86C9BAF3F308C7838E4A52BC0DA0938.LDNG-UI-cl02

1642014334358

000000000020

spitsi-acpt-log-heavy-4

751b1112-0511-4dbd-b94c-a6409c23b20d

a37801e4-dbe6-4c3a-bc26-17d1a78a0b28

c9273wne

Re: fields are not extracted properly

srivenna — Mon, 17 Jan 2022 15:31:58 GMT

Yes! It helped. Perfectly communicated, and works so well.
Thank you!