Unable to parse key-value pair from JSON log data

[rajnish1202]

Hi all,
I am trying to parse key-value pair from my JSON log data. I am unable to parse JSON logs into our Splunk instance appropriately. Below are the sample logs and options I have tried.

I am using below phrase in props.conf and transforms.conf on my indexer. These files are located in D:\Program Files\Splunk\etc\system\local directory of my indexer(using local directory only make sure these files are hit always, will move to the right place once it start working fine).

Props.conf
[my_source_type]
KV_MODE = json
TRANSFORMS-mifid_log = my_data_json_extraction
BREAK_ONLY_BEFORE_DATE = true

Transforms.conf
[my_data_json_extraction]
SOURCE_KEY = _raw
DEST_KEY = _raw
REGEX = ^([^{]+)({.+})$
FORMAT = $2

Below is my log data:

27/09/2017 09:54:41,{ "securityReqID": "", "securityResponseID": "", "securityResponseType": "SecurityResponseType_ACCEPT_AS_IS", "instrument": { "symbol": "", "symbolSfx": "", "tenorValue": "TenorValue_1_Business_Day", "startDateTime": "0", "endDateTime": "0", "repoTenorDateTime": "0", "securityID": "", "securityIDSource": "", "secAltIDGrp": [ { "securityAltID": "InterestRate:Option:Swaption", "securityAltIDSource": "100" }, { "securityAltID": "IR", "securityAltIDSource": "101" }, { "securityAltID": "O", "securityAltIDSource": "102" }, { "securityAltID": "450", "securityAltIDSource": "103" }, { "securityAltID": "c3c9d32e-5ca5-4204-94c7-58aa265ee7ed", "securityAltIDSource": "SRD_ID" }, { "securityAltID": "", "securityAltIDSource": "SRD_REF" } ], "creditSpecInst": null, "fXOSpecInst": null, "product": "", "cFICode": "", "securityType": "", "instrOptionDet": null, "securitySubType": "", "maturityMonthYear": "", "maturityDate": "0", "tickIncrement": 0, "couponPaymentDate": "0", "issueDate": "0", "repoCollateralSecurityType": "", "repurchaseTerm": 0, "repurchaseRate": 0, "factor": 0, "creditRating": "", "instrRegistry": "", "countryOfIssue": "", "contRegOfIssue": "", "stateOrProvinceOfIssue": "", "localeOfIssue": "", "redemptionDate": "0", "strikePrice": 0, "premiumDel": "PremiumDel_SPOT", "strikeCurrency": "", "optAttribute": "", "contractMultiplier": 0, "couponRate": 0, "securityExchange": "", "issuer": "", "encodedIssuerLen": 0, "encodedIssuer": "", "securityDesc": "", "encodedSecurityDescLen": 0, "encodedSecurityDesc": "", "pool": "", "contractSettlMonth": "", "cPProgram": "CPProgram_PROGRAM_3_A_3", "cPRegType": "", "evntGrp": [ ], "datedDate": "0", "interestAccrualDate": "0", "securityStatus": "SecurityStatus_Active", "settleOnOpenFlag": "", "instrmtAssignmentMethod": "InstrmtAssignmentMethod_Random", "strikeMultiplier": 0, "strikeValue": 0, "minPriceIncrement": 0, "positionLimit": 0, "nTPositionLimit": 0, "instrumentParties": [ ], "unitOfMeasure": "", "timeUnit": "", "maturityTime": "", "reIssueDate": "", "parValue": "", "securityGroup": "", "minPriceIncrementAmount": 0, "unitOfMeasureQty": 0, "securityXML": null, "productComplex": "", "priceUnitOfMeasure": "", "priceUnitOfMeasureQty": 0, "settlMethod": "SettlMethod_Cash_settlement_required", "exerciseStyle": 0, "optPayoutAmount": 0, "priceQuoteMethod": "PriceQuoteMethod_STANDARD", "listMethod": "ListMethod_PRE_LISTED_ONLY", "capPrice": 0, "floorPrice": 0, "putOrCall": "PutOrCall_Put", "flexibleIndicator": false, "flexProductEligibilityIndicator": false, "valuationMethod": "", "contractMultiplierUnit": 0, "flowScheduleType": 0, "restructuringType": "", "seniority": "", "notionalPercentageOutstanding": 0, "originalNotionalPercentageOutstanding": 0, "attachmentPoint": 0, "detachmentPoint": 0, "strikePriceDeterminationMethod": 0, "strikePriceBoundaryMethod": 0, "strikePriceBoundaryPrecision": 0, "underlyingPriceDeterminationMethod": "UnderlyingPriceDeterminationMethod_Regular", "optPayoutType": "OptPayoutType_Vanilla", "displayGroup": "", "optionStrategy": "OptionStrategy_Cap", "complexEvents": [ ], "addInstrDescr": null, "netPremium": "", "execDeltaHedge": "ExecDeltaHedge_NO_HEDGE", "hedgeTradeType": "HedgeTradeType_NO_DELTA_HEDGE", "ordTypeRules": [ ], "swapSubClass": "SwapSubClass_AMTZ", "indexSeries": 0, "indexAnnexVersion": 0 }, "instrumentExtension": null, "undInstrmtGrp": [ { "underlyingInstrument": null, "creditSpecUndInst": null } ], "currency": "", "text": "", "encodedTextLen": 0, "encodedText": "", "instrmtLegGrp": [ ], "securityReportID": 0, "clearingBusinessDate": "0", "stipulations": [ ], "spreadOrBenchmarkCurveData": null, "yieldData": null, "corporateAction": "", "marketSegmentGrp": [ ], "applicationSequenceControl": null, "transactTime": "0", "baseTradingRules": null, "preTradeLIS": 0, "preTradeSSTI": 0, "postTradeLIS": 0, "postTradeSSTI": 0, "lastUpdateTime": "0" }
27/09/2017 09:59:48,{ "securityReqID": "", "securityResponseID": "", "securityResponseType": "SecurityResponseType_ACCEPT_AS_IS", "instrument": { "symbol": "", "symbolSfx": "", "tenorValue": "TenorValue_1_Business_Day", "startDateTime": "0", "endDateTime": "0", "repoTenorDateTime": "0", "securityID": "", "securityIDSource": "", "secAltIDGrp": [ { "securityAltID": "US912828TM25", "securityAltIDSource": "4" }, { "securityAltID": "FixedIncome:Bond:Sovereign", "securityAltIDSource": "100" }, { "securityAltID": "FI", "securityAltIDSource": "101" }, { "securityAltID": "BND", "securityAltIDSource": "102" }, { "securityAltID": "300", "securityAltIDSource": "103" }, { "securityAltID": "27fad5cf-b064-4d2b-b391-ea348582ac15", "securityAltIDSource": "SRD_ID" }, { "securityAltID": "", "securityAltIDSource": "SRD_REF" } ], "creditSpecInst": null, "fXOSpecInst": null, "product": "", "cFICode": "", "securityType": "", "instrOptionDet": null, "securitySubType": "", "maturityMonthYear": "", "maturityDate": "0", "tickIncrement": 0, "couponPaymentDate": "0", "issueDate": "0", "repoCollateralSecurityType": "", "repurchaseTerm": 0, "repurchaseRate": 0, "factor": 0, "creditRating": "", "instrRegistry": "", "countryOfIssue": "", "contRegOfIssue": "", "stateOrProvinceOfIssue": "", "localeOfIssue": "", "redemptionDate": "0", "strikePrice": 0, "premiumDel": "PremiumDel_SPOT", "strikeCurrency": "", "optAttribute": "", "contractMultiplier": 0, "couponRate": 0, "securityExchange": "", "issuer": "", "encodedIssuerLen": 0, "encodedIssuer": "", "securityDesc": "", "encodedSecurityDescLen": 0, "encodedSecurityDesc": "", "pool": "", "contractSettlMonth": "", "cPProgram": "CPProgram_PROGRAM_3_A_3", "cPRegType": "", "evntGrp": [ ], "datedDate": "0", "interestAccrualDate": "0", "securityStatus": "SecurityStatus_Active", "settleOnOpenFlag": "", "instrmtAssignmentMethod": "InstrmtAssignmentMethod_Random", "strikeMultiplier": 0, "strikeValue": 0, "minPriceIncrement": 0, "positionLimit": 0, "nTPositionLimit": 0, "instrumentParties": [ ], "unitOfMeasure": "", "timeUnit": "", "maturityTime": "", "reIssueDate": "", "parValue": "", "securityGroup": "", "minPriceIncrementAmount": 0, "unitOfMeasureQty": 0, "securityXML": null, "productComplex": "", "priceUnitOfMeasure": "", "priceUnitOfMeasureQty": 0, "settlMethod": "SettlMethod_Cash_settlement_required", "exerciseStyle": 0, "optPayoutAmount": 0, "priceQuoteMethod": "PriceQuoteMethod_STANDARD", "listMethod": "ListMethod_PRE_LISTED_ONLY", "capPrice": 0, "floorPrice": 0, "putOrCall": "PutOrCall_Put", "flexibleIndicator": false, "flexProductEligibilityIndicator": false, "valuationMethod": "", "contractMultiplierUnit": 0, "flowScheduleType": 0, "restructuringType": "", "seniority": "", "notionalPercentageOutstanding": 0, "originalNotionalPercentageOutstanding": 0, "attachmentPoint": 0, "detachmentPoint": 0, "strikePriceDeterminationMethod": 0, "strikePriceBoundaryMethod": 0, "strikePriceBoundaryPrecision": 0, "underlyingPriceDeterminationMethod": "UnderlyingPriceDeterminationMethod_Regular", "optPayoutType": "OptPayoutType_Vanilla", "displayGroup": "", "optionStrategy": "OptionStrategy_Cap", "complexEvents": [ ], "addInstrDescr": null, "netPremium": "", "execDeltaHedge": "ExecDeltaHedge_NO_HEDGE", "hedgeTradeType": "HedgeTradeType_NO_DELTA_HEDGE", "ordTypeRules": [ ], "swapSubClass": "SwapSubClass_AMTZ", "indexSeries": 0, "indexAnnexVersion": 0 }, "instrumentExtension": null, "undInstrmtGrp": [ { "underlyingInstrument": { "underlyingSymbol": "", "underlyingSymbolSfx": "", "underlyingSecurityID": "US912828TM25", "underlyingSecurityIDSource": "4", "undSecAltIDGrp": [ ], "underlyingProduct": 0, "underlyingCFICode": "", "underlyingSecurityType": "", "underlyingSecuritySubType": "", "underlyingMaturityMonthYear": "", "underlyingMaturityDate": "0", "underlyingCouponPaymentDate": "0", "underlyingIssueDate": "0", "underlyingRepoCollateralSecurityType": "", "underlyingRepurchaseTerm": 0, "underlyingRepurchaseRate": 0, "underlyingPriceType": "", "underlyingFactor": 0, "underlyingCreditRating": "", "underlyingInstrRegistry": "", "underlyingCountryOfIssue": "", "underlyingStateOrProvinceOfIssue": "", "underlyingLocaleOfIssue": "", "underlyingRedemptionDate": "0", "underlyingStrikePrice": 0, "underlyingStrikeCurrency": "", "underlyingOptAttribute": "", "underlyingContractMultiplier": 0, "underlyingCouponRate": 0, "underlyingSecurityExchange": "", "underlyingIssuer": "", "encodedUnderlyingIssuerLen": 0, "encodedUnderlyingIssuer": "", "underlyingSecurityDesc": "", "encodedUnderlyingSecurityDescLen": 0, "encodedUnderlyingSecurityDesc": "", "underlyingCPProgram": "", "underlyingCPRegType": "", "underlyingCurrency": "", "underlyingQty": 0, "underlyingPx": 0, "underlyingDirtyPrice": 0, "underlyingEndPrice": 0, "underlyingStartValue": 0, "underlyingCurrentValue": 0, "underlyingEndValue": 0, "underlyingStipulations": [ ], "underlyingAllocationPercent": 0, "underlyingSettlementType": "UnderlyingSettlementType_TOM", "underlyingCashAmount": 0, "underlyingCashType": "UnderlyingCashType_FIXED", "underlyingUnitOfMeasure": "", "underlyingTimeUnit": "", "underlyingCapValue": 0, "undlyInstrumentParties": [ ], "underlyingSettlMethod": "", "underlyingAdjustedQuantity": 0, "underlyingFXRate": 0, "underlyingFXRateCalc": "UnderlyingFXRateCalc_Divide", "underlyingMaturityTime": "", "underlyingPutOrCall": "UnderlyingPutOrCall_Put", "underlyingExerciseStyle": 0, "underlyingUnitOfMeasureQty": 0, "underlyingPriceUnitOfMeasure": "", "underlyingPriceUnitOfMeasureQty": 0, "underlyingContractMultiplierUnit": 0, "underlyingFlowScheduleType": 0, "underlyingRestructuringType": "", "underlyingSeniority": "", "underlyingNotionalPercentageOutstanding": 0, "underlyingOriginalNotionalPercentageOutstanding": 0, "underlyingAttachmentPoint": 0, "underlyingDetachmentPoint": 0, "underlyingAssetType": "" }, "creditSpecUndInst": null } ], "currency": "", "text": "", "encodedTextLen": 0, "encodedText": "", "instrmtLegGrp": [ ], "securityReportID": 0, "clearingBusinessDate": "0", "stipulations": [ ], "spreadOrBenchmarkCurveData": null, "yieldData": null, "corporateAction": "", "marketSegmentGrp": [ ], "applicationSequenceControl": null, "transactTime": "0", "baseTradingRules": null, "preTradeLIS": 0, "preTradeSSTI": 0, "postTradeLIS": 0, "postTradeSSTI": 0, "lastUpdateTime": "0" }

Unfortunately I am able to parse only first log message from the log as json and remaining log entries are not formatted appropriatly as json data. Below is the sample image from my indexer.

Many thanks in advance for the help.

Regards,
Rajnish Kumar

[DalJeanis]

Check the input file and see what kind of character is after the final } of the first JSON.

The symptoms seem to imply that that end-of-JSON special character is not being recognized as $.

I'd probably try this.

 SOURCE_KEY = _raw
 DEST_KEY = _raw
 REGEX = ^[^,]+,({.+})
 FORMAT = $1   

The only significant difference is removing the $ from the end. The rest of the reformat is just because I personally prefer not to have unneeded capture groups.

If that didn't work, I'd try this one...

 REGEX = ^[^,]+,({.+})($|[\n|\r])

[rajnish1202]

Hi DalJeanis,
Thanks for your response. as you can see in the sample log, I have date/time after final '{' of my first JSON.

I tried option you provided unfortunately no luck.

Thanks,
Rajnish

[ajackal]

I had some issues with is parsing JSON when there was white space in the event. When I removed all the white space it parsed correctly. Example (exaggerated for clarity):

{ "eventdata" : "some data" } --> did not parse correctly
{"eventdata":"some data"}--> parsed correctly

I was generating logs through the PowerShell cmdlet ConvertTo-Json $input
By adding the -Compress switch I have gotten Splunk to reliably index all events.

Not sure this is your issue, but worth as shot. Hope this helps!

[gjanders]

The plus icon next to instrument for example should expand and show values below this level.

You may wish to use spath to expand various fields, by default Splunk will only auto-find fields until a certain depth within the JSON data.

[rajnish1202]

Hi Garethatiag,
Many thanks for your response. My proble is that splunk is parsing only first log entry in the log file in proper format as you can see in the image shared. I want to parse all the entried the way it does with first log entry.

[gjanders]

Perhaps in props.conf you can add this under your sourcetype stanza:

TIME_PREFIX = ^
TIME_FORMAT = %d/%m/%Y %H:%M:%S

And see if that helps?

[rajnish1202]

Unfortunately it did not work. Time is actually being parsed already. Result is same after adding these two lines.

[gjanders]

I'm not clear on what the issue is here, if the events are correctly broken, the JSON format works on the first event, it should also work on any other event that has valid JSON data.

If there is any syntax error in the JSON data Splunk will not parse it as expected...

[rajnish1202]

Yes I am surprised too and thats why I posted this question here. First event in the log file gets parsed appropriately in the json format but subsequent events dont(as shown in the image, first event is at 9:54 and parsed well, second one is at 9:59 and does not parse as expected). I could not think of any reason why this would happen so thought of asking the experts on splunk web.

P.S. There is no syntex error that I could see.