topic Extract distinct values from an response array that has partial text using RegEx in Splunk Search

Extract distinct values from an response array that has partial text using RegEx

balamurugandha7 — Mon, 08 Feb 2021 10:34:54 GMT

I have an array that would be presented in an API response which is being logged in Splunk and the array format is like this:

"Indicators": [

"HAS_ACOUNT",

"NON_ZERO_BALANCE_ACCOUNT",

"JOINT_ACCOUNT",

"NOT_EXPIRED",

"REGISTERED"

]

The number of values within the array will not always have same pattern. i.e. There may be responses where there may be 10 values within 'Indicators' array.

Now, I want to extract distinct values within "Indicators" array (with the value that has the text "ACCOUNT") logged in Splunk for last 30 days from that specific API response. Could someone help me how to get that?

I wrote like this, but is didn't quite capture all possible values..

index="index_name" Env=test "........./API" | rex field=_raw "\"Indicators\"\:\[(?<planInd>[^\,]*)\]" max_match=0 | where like (planInd,"%PCP%") | dedup planInd | table planInd

Re: Extract distinct values from an response array that has partial text using RegEx

to4kawa — Mon, 08 Feb 2021 10:44:33 GMT

index=_internal | head 1 | fields _raw | eval _raw="\"Indicators\": [ \"HAS_ACOUNT\", \"NON_ZERO_BALANCE_ACCOUNT\", \"JOINT_ACCOUNT\", \"NOT_EXPIRED\", \"REGISTERED\" ]" | rex mode=sed "s/(?ms)(.*)/{\1}/" | spath

I think you will make it JSON better.

Re: Extract distinct values from an response array that has partial text using RegEx

balamurugandha7 — Mon, 08 Feb 2021 11:04:10 GMT

Please advise how am I suppose to dynamically pass values to "elav _raw...." as I am trying to apply this rex to all events logged for last 30 days.

Thanks in advance!

Re: Extract distinct values from an response array that has partial text using RegEx

to4kawa — Mon, 08 Feb 2021 11:11:11 GMT

The rex is performed for each event, so I am not sure what the problem is.

And it doesn't say how the logs for the past 30 days will appear.

Re: Extract distinct values from an response array that has partial text using RegEx

balamurugandha7 — Mon, 08 Feb 2021 11:22:34 GMT

When I ran this I got the same array that is assigned to "_raw".

What I was expecting as an output is,
"HAS_ACOUNT",
"NON_ZERO_BALANCE_ACCOUNT",
"JOINT_ACCOUNT"

as only these three values within the Indicators array has the partial text "ACCOUNT". Also I couldn't figure out in your query where exactly you are trying to extract the events that has the partial text "ACCOUNT".

For example, if there is another event that has the "Indicators" array value as follows, then I should only get the value "HAS_ACCOUNT" for this event as this is the only value within that array that has the partial text "ACCOUNT"..

"Indicators": [
"HAS_ACOUNT",
"NOT_EXPIRED",
]

Re: Extract distinct values from an response array that has partial text using RegEx

to4kawa — Tue, 09 Feb 2021 12:18:01 GMT

index=_internal | head 1 | fields _raw | eval _raw="\"Indicators\": [ \"HAS_ACCOUNT\", \"NON_ZERO_BALANCE_ACCOUNT\", \"JOINT_ACCOUNT\", \"NOT_EXPIRED\", \"REGISTERED\" ]" | rex mode=sed "s/(?ms)(.*)/{\1}/" | spath | rename *{} as * | rex field=Indicators max_match=0 "(?<indicators>.*ACCOUNT)" | table indicators