Getting Data In

Extracting Key Value pair from JSON input

rajnish1202
Explorer

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

0 Karma

hortonew
Builder

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

0 Karma

rajnish1202
Explorer

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

0 Karma

hortonew
Builder

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.

0 Karma

rajnish1202
Explorer

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.

0 Karma

hortonew
Builder

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

0 Karma

rajnish1202
Explorer

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.

0 Karma

hortonew
Builder

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.

0 Karma

rajnish1202
Explorer

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.

0 Karma

hortonew
Builder

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}

0 Karma

rajnish1202
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...