topic Re: Extracting Key Value pair from JSON input in Getting Data In

Extracting Key Value pair from JSON input

rajnish1202 — Tue, 29 Sep 2020 15:59:25 GMT

Hi,
I need helkp regarding extraction of key value pair from a json input being forwarded to out indexer. I have already tried options like below but did not work.
1.
[your_sourcetype]
INDEXED_EXTRACTIONS = json
KV_MODE = none
2.
[your_sourcetype]
KV_MODE = JSON

3.
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Spath

Below is my sample json
27/09/2017 13:51:34,{ "securityReqID": "a1ec4c76-f31d-437f-9d8f-21c8e6c971e9", "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:IRSwap:Basis", "securityAltIDSource": "100" }, { "securityAltID": "IR", "securityAltIDSource": "101" }, { "securityAltID": "S", "securityAltIDSource": "102" }, { "securityAltID": "450", "securityAltIDSource": "103" }, { "securityAltID": "TP DMS", "securityAltIDSource": "104" }, { "securityAltID": "a56993fe-7fc9-4c14-87b8-03ef68f9bef1", "securityAltIDSource": "SRD_ID" }, { "securityAltID": "", "securityAltIDSource": "SRD_REF" }, { "securityAltID": "EZ5QSR68XWY1", "securityAltIDSource": "4" }, { "securityAltID": "NA/Swap Flt Flt GBP 20180815", "securityAltIDSource": "105" }, { "securityAltID": "Rates Swap Basis GBP-LIBOR-BBA 1 MNTH GBP-LIBOR-BBA 3 MNTH 20180815", "securityAltIDSource": "106" } ], "creditSpecInst": null, "fXOSpecInst": null, "product": "", "cFICode": "SRAISC", "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": { "bondID": "", "bondBasket": "", "marketDate": "0", "spotDateOffsetMultiplier": "", "spotDateOffsetPeriod": "SpotDateOffsetPeriod_Days", "spotDateOffsetDayType": "SpotDateOffsetDayType_Calendar_Days", "tradeForwardStartPeriod": "TradeForwardStartPeriod_Days", "tradeForwardStartMultiplier": "", "tradeFwdStartPrdDayType": "TradeFwdStartPrdDayType_Calendar_Days", "tradeFwdStartPrdBussDayConv": "TradeFwdStartPrdBussDayConv_Calendar_Days", "effectiveDate": "0", "adjustedEffectiveDate": "0", "effectiveDateType": "EffectiveDateType_Unadjusted", "bussCentresEffDateGrp": [ ], "effectiveDateBussDayConv": "EffectiveDateBussDayConv_None", "tradeTenorPeriod": "TradeTenorPeriod_Days", "tradeTenorPeriodMultiplier": "", "rollConventionValue": "", "stubPosition": "StubPosition_Front", "unadjustedEndDate": "0", "adjustedEndDate": "1534291200000000", "bussCentresEndDateGrp": [ ], "endDateBussDayConv": "EndDateBussDayConv_None", "periodIndex": "", "dayCount": 0, "notionalAmountCurrency": "GBP", "notionalAmountMultiplier": "", "spreadAppliedTo": "SpreadAppliedTo_No", "duplicateInstrument": "DuplicateInstrument_No", "initialIndexLevelValue": "", "lagPeriod": "LagPeriod_Months", "lagPeriodMultiplier": 0, "initialIndexDate": "0", "finalIndexDate": "0", "maturityDateOffsetMultiplier": "", "maturityDateOffsetPeriod": "MaturityDateOffsetPeriod_Days", "maturityDateOffsetPrdDayType": "MaturityDateOffsetPrdDayType_Calendar_Days", "bussCentresMaturityDateGrp": [ ], "maturityDateBussDayConv": "MaturityDateBussDayConv_None", "maturityDateRelativeTo": "MaturityDateRelativeTo_Adjusted_End_Date", "bussCentresPremiumDateGrp": [ ], "premiumDateBussDayConv": "PremiumDateBussDayConv_None", "premiumDateRelativeTo": "PremiumDateRelativeTo_Adjusted_End_Date", "fLSwapStream": "", "fLIndexCurrency1": "", "fLIndexMarket1": "FLIndexMarket1_GBP_LIBOR_BBA", "fLIndexTenorPeriod1": "FLIndexTenorPeriod1_Months", "fLIndexTenorPeriodMultiplier1": "1", "fLNotionalAmountCurrency1": "", "fLCalcFreqPeriod1": "FLCalcFreqPeriod1_Days", "fLCalcFreqPeriodMultiplier1": 0, "bussCentresFLCalcFreqGrp": [ ], "fLCalcBussDayConv1": "FLCalcBussDayConv1_None", "fLPayFreqPeriod1": "FLPayFreqPeriod1_Days", "fLPayFreqPeriodMult1": "", "bussCentresFLPayFreqGrp": [ ], "fLPayBussDayConv1": "FLPayBussDayConv1_None", "fLPayRelativeTo1": "FLPayRelativeTo1_Calculation_Start_Date", "fLPayDateOffsetPeriod1": "FLPayDateOffsetPeriod1_Days", "fLPayDateOffsetPeriodMult1": "", "fLPayDaysOffsetDayType1": "FLPayDaysOffsetDayType1_Calendar_Days", "fLCutOffDateOffstPrd1": "FLCutOffDateOffstPrd1_Days", "fLCutOffDateOffstPrdMult1": "", "bussCentresFLCutOffDays1Grp": [ ], "fLCutOffDateRelativeTo1": "FLCutOffDateRelativeTo1_Payment_Date", "fLFxngDaysPeriod1": "FLFxngDaysPeriod1_Days", "fLFxngDaysPeriodMult1": 0, "bussCentresFLFxngDaysGrp": [ ], "fLFxngDayDayType1": "FLFxngDayDayType1_Calendar_Days", "fLFxngDayBussDayConv1": "FLFxngDayBussDayConv1_None", "fLFxngDayRelativeTo1": "FLFxngDayRelativeTo1_Reset_Date", "fLResetFreqPeriod1": "FLResetFreqPeriod1_Days", "fLResetFreqPeriodMult1": "", "fLResetFreqEach1": "FLResetFreqEach1_THU", "bussCentresFLResetFreqGrp": [ ], "fLResetBussDayConv1": "FLResetBussDayConv1_None", "fLRateResetRelativeTo1": "FLRateResetRelativeTo1_Calculation_Start_Date", "fLResetDateOffstPrd1": "FLResetDateOffstPrd1_Days", "fLResetDateOffstPrdMult1": "", "fLRateCompoundingType1": "FLRateCompoundingType1_None", "fLRateAveragingMethod1": "FLRateAveragingMethod1_Weighted", "fLDayCountConv1": "FLDayCountConv130_360", "fLRateStubLength1": "FLRateStubLength1_Short", "fLInitialNotionalExchange1": "FLInitialNotionalExchange1_False", "fLIntermediateNotionalExch1": "FLIntermediateNotionalExch1_False", "fLFinalNotionalExchange1": "FLFinalNotionalExchange1_False", "fLPaymentDiscounting": "FLPaymentDiscounting_False", "fL1stPayDate1": "0", "fLAdjusted1stPayDate1": "0", "fL1stRegPrdStartDate1": "0", "fLAdj1stRegPrdStartDate1": "0", "fLLastRegPrdEndDate1": "0", "fLAdjLastRegPrdEndDate1": "0", "fLLastRegPayDate1": "0", "fLAdjLastRegPayDate1": "0", "fLInterpolationMethod1": "FLInterpolationMethod1_None", "fLIndexSource1": "FLIndexSource1_N_A", "fLSwapstream2": "", "fLIndexCurrency2": "", "fLIndexMarket2": "FLIndexMarket2_GBP_LIBOR_BBA", "fLIndexTenorPeriod2": "FLIndexTenorPeriod2_Months", "fLIndexTenorPeriodMutiplier2": "3", "fLNotionalAmountCurrency2": "", "fLCalcFreqPeriod2": "FLCalcFreqPeriod2_Days", "fLCalcFreqPeriodMultiplier2": "", "bussCentresFLCalcFreq2Grp": [ ], "fLCalcBussDayConv2": "FLCalcBussDayConv2_None", "fLPayFreqPeriod2": "FLPayFreqPeriod2_Days", "fLPayFreqPeriodMult2": "", "bussCentresFLPayFreq2Grp": [ ], "fLPayBussDayConv2": "FLPayBussDayConv2_None", "fLPayRelativeTo2": "FLPayRelativeTo2_Calculation_Start_Date", "fLPayDateOffsetPeriod2": "FLPayDateOffsetPeriod2_Days", "fLPayDateOffsetPeriodMult2": "", "fLPayDaysOffsetDayType2": "FLPayDaysOffsetDayType2_Calendar_Days", "fLCutOffDateOffstPrd2": "FLCutOffDateOffstPrd2_Days", "fLCutOffDateOffstPrdMult2": "", "bussCentresFLCutOffDays2Grp": [ ], "fLCutOffDateRelativeTo2": "FLCutOffDateRelativeTo2_Payment_Date", "fLFxngDaysPeriod2": "FLFxngDaysPeriod2_Days", "fLFxngDaysPeriodMult2": "", "bussCentresFLFxngDayGrp": [ ], "fLFxngDayDayType2": "FLFxngDayDayType2_Calendar_Days", "fLFxngDayBussDayConv2": "FLFxngDayBussDayConv2_None", "fLFxngDayRelativeTo2": "FLFxngDayRelativeTo2_Reset_Date", "fLResetFreqPeriod2": "FLResetFreqPeriod2_Days", "fLResetFreqPeriodMult2": "", "fLResetFreqEach2": "FLResetFreqEach2_THU", "bussCentresFLResetFreq2Grp": [ ], "fLResetBussDayConv2": "FLResetBussDayConv2_None", "fLRateResetRelativeTo2": "FLRateResetRelativeTo2_Calculation_Start_Date", "fLResetDateOffstPrd2": "FLResetDateOffstPrd2_Days", "fLResetDateOffstPrdMult2": "", "fLRateCompoundingType2": "FLRateCompoundingType2_None", "fLRateAveragingMethod2": "FLRateAveragingMethod2_Weighted", "fLDayCountConv2": "FLDayCountConv230_360", "fLRateStubLength2": "FLRateStubLength2_Short", "fLInitialNotionalExchange2": "FLInitialNotionalExchange2_False", "fLIntermediateNotionalExch2": "FLIntermediateNotionalExch2_False", "fLFinalNotionalExchange2": "FLFinalNotionalExchange2_False", "fL1stPayDate2": "0", "fLAdjusted1stPayDate2": "0", "fL1stRegPrdStartDate2": "0", "fLAdj1stRegPrdStartDate2": "0", "fLLastRegPrdEndDate2": "0", "fLAdjLastRegPrdEndDate2": "0", "fLLastRegPayDate2": "0", "fLAdjLastRegPayDate2": "0", "fDSwapStream": "", "fDNotionalAmountCurrency": "", "fDDayCountConv": "FDDayCountConv_30_360", "fDCalcFreqPeriod": "FDCalcFreqPeriod_Days", "

I need to extract key value pairs for fields like securityAltID, securityAltIDSource etc.

Any help is much appriciated.

Many thanks in advance.

Regards,
Rajnish

Re: Extracting Key Value pair from JSON input

hortonew — Tue, 29 Sep 2020 15:59:31 GMT

Are your indexed_extractions entries in props.conf on the forwarder that is sending the data? Check out this to make sure you're configuring your settings in the right location: https://wiki.splunk.com/Where_do_I_configure_my_Splunk_settings%3F

Re: Extracting Key Value pair from JSON input

rajnish1202 — Tue, 29 Sep 2020 15:59:33 GMT

Hi Hortonew,
Thanks for the response. my indexed_extraction entry is on indexer in $SPLUNK_HOME\etc\system\default\props.conf file.

Re: Extracting Key Value pair from JSON input

hortonew — Fri, 29 Sep 2017 14:20:59 GMT

So first thing: you never want to edit etc/system/default/props.conf. You should only be making changes to etc/system/local/*.conf, or a default/local entry in a custom app.

Second thing: If it's coming from a remote Splunk Universal forwarder, it has already gone through the input queue and will hit the indexers at the parsing queue. This will not use those props.conf settings as INDEXED_EXTRACTIONS only works at the input queue. So you want to go to your Universal Forwarder where the json is ingested, and configure your props.conf there, in either a new app or etc/system/local/props.conf.

Re: Extracting Key Value pair from JSON input

rajnish1202 — Fri, 29 Sep 2017 14:45:26 GMT

Hi Hortonew,
Thanks for the guidance. Yes I understand I should not do it in default/props.conf file and I would never do it on production environment. This is a POC I am working on with indexer and forwarder both installed on my local machine. My local machine also stores the log I am forwarding. I was cleaning the eventdata on indexer and fishbucket on forwarder before trying all the methods mentioned in the question description. I belive my forwarded data will hit both input and parsing queues if I do above before trying anything new.

I just tried configuring props.conf file on forwarder as suggested but unfortunately no luck.

Re: Extracting Key Value pair from JSON input

hortonew — Fri, 29 Sep 2017 15:00:01 GMT

I see your sample json starts with a timestamp outside of the actual json data. Can you try the ingestion without that? I'm not sure if that affects what happens in the indexed_extractions

Edit: It might make more sense to use the data ingestion tool through the GUI of Splunk to see how it's being handled. might be able to play with settings there to get extracted fields

Re: Extracting Key Value pair from JSON input

rajnish1202 — Fri, 29 Sep 2017 15:22:21 GMT

Thanks again Hortonew. You are right it starts with a timestamp because thats how it is generated by the system responsible for the logs. If timestamp is removed, we wont be able to keep track of the time of the events.

it seems to work however after removing the timestamp and using only one event. We want to keep track of the events based on the time of their occurance so need the time of event.

Re: Extracting Key Value pair from JSON input

hortonew — Fri, 29 Sep 2017 15:33:14 GMT

If there's no way for the system generating it to add the timestamp directly to the json, then you might look at doing search time field extractions. I believe you can add timestampping configs and KV_MODE=json to parse the rest of the event as json.

Re: Extracting Key Value pair from JSON input

rajnish1202 — Fri, 29 Sep 2017 15:40:42 GMT

is there any example you can suggest to add timestampping config and KV_MODE=json in search time field extraction? Please excuse my naive approach as I am not a pro on splunk.

Many thanks again.

Re: Extracting Key Value pair from JSON input

hortonew — Fri, 29 Sep 2017 16:37:26 GMT

Seems KV_MODe won't work on data that isn't formatted as json. In this case, your timestamp is causing issues. Is there any way your system can output the timestamp as a k/v pair inside the json braces? for instance, every new event would be:

{"timestamp": <timestamp>, other json data}

Re: Extracting Key Value pair from JSON input

rajnish1202 — Mon, 02 Oct 2017 09:25:41 GMT

Hi Hortonew,
Unfortunately it does not seem to be an option to create logs with timestamp as a key value pair as we cant change the system creating the logs. Do you suggest any alternate way to accomplish this?