Grouping field names together from a pivot table with a partial literal regex name

[ryhluc01]

Hi There,

There are multiple field titles that start with the same exact word (example: Candy).

After the word Candy, there may or may not be another word that may or may not be followed by a number that will vary but will be formatted as \d.\d\d

I need to grab & be able to reference every field title with the word Candy in it (along with whatever wording etc that follows it).

The different values within the various Candy* fields need to coalesce under one title while still remaining separate.

Everything is coming from a pivot table.

Thank you!

[ryhluc01 · Accepted solution]

This is the answer to my question just in case anyone else is having this isssue.

This was the query & regex I created to solve this issue:
|index=foo | stats dedup_splitvals=t latest(Field_Name_One) AS version By host, Field_Name_Two
| regex Field_Name_Two_Here="Candy(\s|™)"

Explanation of process & solution:
I had to remove the Pivot Table and work directly with the data from the index. The PT severely limited what I could do with the data because I could not add anything before the table. By exploring the data I realized that I had to completely remove it from the query. I then created a regular expression to define what I was looking for. Splunks regex's are based on perl. I found this document to be incredibly helpful.
https://perldoc.perl.org/perlre.pdf

To customize my regex I had to know the different variations the word would have so that I could only grab what I needed.
I performed a DC on Field_Name_Two to achieve this purpose. It looked like this:
|index=foo | stats dedup_splitvals=t latest(Field_Name_One) AS version By host, Field_Name_Two_Here | stats dc(Field_Name_Two_Here) as Distinct_Name_Count by Field_Name_Two