topic Re: extract key-value pairs that match or contain value, where keys vary across indices in Splunk Search

extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Thu, 15 Apr 2021 19:49:39 GMT

What's a scalable to extract key-value pairs where the value matches via exact or substring match but the field is not known ahead of time, and could be in _raw only?

Eg, search for the string "alan", which may be associated to fields as follows:

index=indexA user=alan

index=indexB username=alan

index=indexC loginId=corp\alan

index=indexD _raw=<unstructured text, alan : user>

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Fri, 16 Apr 2021 11:25:52 GMT

Obviously, you could just add the keyword "alan" to your search to get the results you may want....but those may contain results you're not interested in.

i don't know if you would consider it scalable, but typically the approach i think is to normalize the data as you onboard it. That could be with the Common Information Model app or just on your own.

For example, in the scenario above, for each of the sourcetypes, create a new field called username - either alias existing fields to username or extract/eval/etc username in the case that it's just in the raw data. Once you have a username field across all sourcetypes, then just add username="alan" to your search.

If you find yourself onboarding a new sourcetype that contains a user that you want to search in a similar way, ensure a username field exists or is created.

Re: extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Fri, 16 Apr 2021 13:19:43 GMT

My fault for not being specific enough.:

First, We cannot normalize the data before onboarding as it reflects hundreds of sources including 3rd party vendors and other constraints.

Second, I'm only looking to extract the fields (keys) that match, no other fields (unless added optionally) - ideally avoiding rex as the index volumes are large and this query needs to be repeated 10k-100k times.

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Fri, 16 Apr 2021 14:40:19 GMT

Ah ok. I'm not sure how expensive it is, but i'm wondering if fieldsummary might be helpful here? Something like this maybe? I'm sure the match would need to be a bit more complicated....

index IN (indexA,indexB,indexC) alan | fieldsummary | where match(values,"(?i)alan")

Re: extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Fri, 16 Apr 2021 14:55:10 GMT

Thanks, that is close to what is needed, but the fields for the various indexes seem mixed together.

Is it possible to add a column to show the index (and other fields eg sourcetype) next to the field?

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Fri, 16 Apr 2021 15:14:14 GMT

maybe someone else out here will have a better answer, but i can see maybe getting the index by using the map command....but if you don't know the sources/sourcetypes/etc, then that could get trickier. But here is maybe the next step for your path.

This will create an event for each index and then use the map function to the same search over each, adding a field for index to the results. I'm sure there is a way with tstats or eventcount to build the indexes/sourcetype you want and just expanding this. Although, not sure how expensive this becomes either.

Re: extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Fri, 16 Apr 2021 15:48:28 GMT

I'm not getting the same results w/ this version of the query. Although the index does show up, now the counts are way higher and many or most values seem not to match the user named before "| stats count"

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Fri, 16 Apr 2021 15:55:02 GMT

well i did forget to the put the "alan" keyword back in the search - did you put it back in there? If not, try adding it in the search in the map function. And also i guess the where clause as well. all of that needs to back into that search.....

Re: extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Fri, 16 Apr 2021 16:18:58 GMT

i did add the user value at the beginning, but can't get it to work when reinserting the where clause. I tried putting it after the fieldsummary and after the last eval, - getting no results.

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Fri, 16 Apr 2021 17:27:09 GMT

friday firedrills over here.....can you just toss the where after the map command? or it might helpful if you can share what you tried that failed.

Re: extract key-value pairs that match or contain value, where keys vary across indices

maciep — Sat, 17 Apr 2021 11:35:35 GMT

ok...so I guess you didn't get that working then? Here is an example of how maybe you could use tstats to get the index, source and sourcetypes.....just not sure if it will be too much for the map function. But also including the where clause that you need - same as before, just have to escape the quotes. But I'm still not sure what you tried that didn't work.....

Not sure how many combinations of index/source/sourcetype you have, so you may need to bump up the maxsearches as well. And if it is too much, I think you should know how to go back just using the index.

| tstats count where index IN (indexA,indexB,indexC) by index source, sourcetype | map maxsearches=100 search="search index=$index$ source=$source$ sourcetype=$sourcetype$ alan | fieldsummary | where match(values,\"(?i)alan\") | eval index=\"$index$\",source=\"$source$\",sourcetype=\"$sourcetype$\""

Re: extract key-value pairs that match or contain value, where keys vary across indices

alancalvitti — Mon, 19 Apr 2021 20:57:29 GMT

Thanks, - this tstats query seems to return results that contain the desired fields and values, but it also includes "false positive" values that don't include the query account.

For example, (and I'm having to copy/paste made up values here...) one statistic might say:

field count distinct_count index is_exact max mean min numeric_count sourcetype stdev values

event.Account_Name

11805

ad_index

ADindex

[{"value":"alan","count":8874},{"value":"bob","count":2931},]

There are typically other values (eg bob) other than the input query argument. For example, for the field avent.Account_Name , the "count" of 11805 is the sum of the individual "value" counts (alan 8874, bob 2931) in the "values" column. How to interpret these results? Is it that alan matches 2931 events in which it shows up in a different field than event.Account_Name?

Also, what if the match is on the _raw event but not extracted in a kv field - would _raw be among the fields returned?