Splunk - Extracting from search results using regex and aggregates

I have the following search:

my search | eval LINE=(MESSAGE) | where MESSAGE LIKE "Process : Hp:%" 
| stats values(DATETIME) as Date values(LINE) as Status

That search gets me the following result:

Date                                Status
2023-10-31          Process : Hp: N1 - NOEAST WorkId: 9 Numbers: 209 Cases: 224 Lines: 465         
2023-10-31          Process : Hp: N1 - NOEAST WorkId: 9 Numbers: 499 Cases: 577 Lines: 954         
2023-11-02          Process : Hp: N2 - NOEAST WorkId: 13 Numbers: 178 Cases: 230 Lines: 376         
2023-11-03          Process : Hp: N2 - NOEAST WorkId: 13 Numbers: 466 Cases: 497 Lines: 725         
2023-11-03          Process : Hp: O1 - SOWEST WorkId: 11 Numbers: 182 Cases: 275 Lines: 619

I need to go further in my extraction in order to get the following result:

HP                  Total Numbers     Total Cases     Total Lines
N1 - NOEAST 9          708             801              1419
N2 - NOEAST 13         644             727              1101
O1 - SOWEST 11         182             275               619

Is it possible to get my desired result using a subsearch and regex to get the index of the parts of the string that I have to add together? If so could someone give me an example to get started? Or is there a simpler way to do this all together?

Solution

You can extract the necessary fields by using the rex command with named capturing groups in your regex. Afterward, you can utilize the stats command to sum up the numbers, cases, and lines, grouping them by the HP field, which represents a combination of the location and the WorkId.

my search | where MESSAGE LIKE "Process : Hp:%"
| rex field=MESSAGE "Process : Hp: (?<HP>\w+\s-\s\w+)\sWorkId:\s(?<WorkId>\d+)\sNumbers:\s(?<Numbers>\d+)\sCases:\s(?<Cases>\d+)\sLines:\s(?<Lines>\d+)"
| stats sum(Numbers) as "Total Numbers", sum(Cases) as "Total Cases", sum(Lines) as "Total Lines" by HP, WorkId
| eval HP=HP." ".WorkId
| fields - WorkId