Regex or transform to split different formats for a field

[rfds]

Hi,

I've got a bunch of TSV files that I'd like to pull into Splunk that lists when certain aircraft components are due for maintenance. I've created a tsv sourcetype for the purpose.

These aircraft components become due for maintenance based on either:

1. a date
2. the number of hours the aircraft has flown
3. the number of landings the aircraft has completed
4. a combination of the above

One of the columns in the TSV (which is now mapped to a field - 'Next Due') contains all of the above. Here is an example:

Task_Check PN_SN Description Next Due
SURVIVAL-WATER PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 30/08/2015
SURVIVAL-RATION PACK PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 10/08/2015
SURVIVAL-LIP BALM PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 1/03/2015
SURVIVAL-EPIRP MT410G PN: MT410G SN: 80454243 CARRY OUT EPIRP MT410G INSPECTION IAW MANUFACTURER'S INSTRUCTIONS 19/06/2015
PIL-SB-71-008 REV 0 N/A PIL-SB-71-008: REPLACEMENT OF PLENUM SCREEN ASSEMBLY 23/07/2015
PIL-SB-52-008 REV 0 N/A SB 52-008: INTRODUCTION OF A NEW CARGO DOOR HANDLE SWITCH 29/01/2015
PHASE ENGINE INSP N/A CARRY OUT ENGINE - 100 HOUR & MINOR ITEMS IAW PWC M/M TABLE 601 4844.70 Hours
MT410G REPLACEMENT PN: MT410G SN: 80454243 EPIRP LIFE LIMIT 30/05/2015
MAINTENANCE RELEASE INSP N/A MAINTENANCE RELEASE INSPECTION 2015-04-22, 4844.70 Hours
AD/PT6A/28 req 2 (7 DAY) PN: PT6A-67P SN: PCE-RY0238 AD/PT6A/28 Req 2 (Compressor & Compressor Turbine Desalination wash -coast) 3/12/2014
AD/GEN/37R9 N/A AD/GEN/37 AMDT 9 - Emergency Exits. 12/03/2015
AD/ENG/5R9 PN: PT6A-67P SN: PCE-RY0238 AD/ENG/5 AMDT 9 - Turbine Engine Continuing Airworthiness Requirements. 5000.00 Hours
99-5r-1869-eo1 NAV LIGHTS N/A 5R EO 5R-1869-EO1 NAV LIGHTS ALT POWER SOURCE CONT AIRWORTHINESS INSP 2015-02-18, 4887.50 Hours
99-16576.001/1-200 N/A SMOKE GOGGLES 200 HR CONT AIRWORTHINESS REQUIREMENTS INSPECTION 5043.60 Hours
99-16375.001/2 N/A CARRY OUT VISUAL INSPECTION OF MOTOROLA XTL5000 WHIP ANTENNA 4844.70 Hours
99-14907.216/1 N/A EO14907.216/1 Issue 4 Mansell NeoNatal Unit Inst. CONT AIRWORTHINESS REQ 24/05/2015
99-14907.206/1 N/A PROPAQ INSTALLATION CONTINUED AIRWORTHINESS INSPECTION REQUIREMENTS 1/05/2015
99-14907.049/1 N/A 14907.049/1 issue 3: SA Baggage Bay Annual Inspection 2/05/2015
99-14907.033/1 N/A CONTINUED AIRWORTHINESS- AFT FACING SEAT PROTECTION PLATE 4844.70 Hours
99-14907.032/1 N/A PERFORM SACRIFICIAL SEAT TRACK CONT AIRWORTHINESS INSP 4944.70 Hours
99-14907.029/1 N/A Perform EO14907.029/1 Issue 6. MEDICAL EQUIPMENT SUPPORTS ANNUAL INSP 1/05/2015
99-14907.028/1 N/A EO14907.028/1 Issue 5 Medical Equip Rails annual inspection 1/05/2015
99-14907.023/1 N/A Perform EO14907.023/1 Issue 4. AEA Stretcher Loader Installation Inspect 2/05/2015
99-14907.022/1 N/A EO14907.022/1 Issue 2. Ferno 2650 Stretcher System installation Insp 2/05/2015
99-14907.015/1 N/A EO14907.015/1 Issue 3. MEDICAL CABINET INSTALL CONT AIRWORTHINESS 1/05/2015
99-14907.014/1 N/A Perform EO14907.014/1 Issue 2. FUEL DRAIN CUP INSPECTION 4844.70 Hours
99-14907.008/1 N/A Perform EO14907.008/1 Issue 2. LIFE JACKET INSTALL CONT A/WORTHINESS REQ 29/04/2015
99-14907.002/1 N/A Oxygen Mask Stowage - Installation inspection 4844.70 Hours
7920-012B PN: PT6A-67P SN: PCE-RY0238 Replace Oil Filter Element. 4887.50 Hours
79-20/43 PN: 968.32.14.102 SN: 1134 Overhaul Oil Cooler. 5000.00 Hours
7530-100 (O) PN: 3059835-01 SN: AHX016123 Overhaul Compressor Bleed Valve. 5000.00 Hours
7410-100 (O) PN: 10-381550-4 SN: NNA10080009 Overhaul Ignition Exciter. 5000.00 Hours
7310-300 (O) PN: 26130-2 SN: 9959525078 Overhaul Flow Divider. 4909.70 Hours
7310-200 (O-02) PN: 825601-5 SN: 010282 Overhaul Engine Driven (HP) Fuel Pump. 5000.00 Hours
7310-100 (O) PN: 10585K SN: WA39256 Overhaul Oil-to-Fuel Heater. 5000.00 Hours
7310-022 PN: PT6A-67P SN: PCE-RY0238 Replace Engine P3 Filter. 4887.50 Hours
7250-250 (D) PN: 3056693-01 SN: HWDD2125-HWDH0765 Discard Power Turbine 2nd Stage Blades. 5000.00 Hours
7250-250 (D) PN: 3037313 SN: YUAA005A562 Discard Power Turbine 2nd Stage Blades. 5000.00 Hours
7250-000 (O) PN: 3072956 SN: PS-RY0238 Overhaul Power Section Module. 5000.00 Hours
7230-000 (O) PN: 3072959 SN: PCE-RY0238 Overhaul Gas Generator Module. 5000.00 Hours
72-06 PN: 3037313 SN: YUAA005A562 Inspection Engine Power Turbine Blades. 4844.70 Hours
72-05 PN: PT6A-67P SN: PCE-RY0238 EXAMINE , FIRST STAGE COMPRESSOR 4844.70 Hours
7200-000 (O) PN: PT6A-67P SN: PCE-RY0238 Overhaul Engine. 5000.00 Hours
71-00/38 PN: 944.81.21.409 SN: 5432 Replace Engine Shock Mount. 5000.00 Hours
71-00/38 PN: 944.81.21.409 SN: 5162 Replace Engine Shock Mount. 5000.00 Hours
71-00/38 PN: 944.81.21.409 SN: 5147 Replace Engine Shock Mount. 5000.00 Hours
71-00/38 PN: 944.81.21.409 SN: 5146 Replace Engine Shock Mount. 5000.00 Hours
71-00/38 PN: 944.81.21.409 SN: 5135 Replace Engine Shock Mount. 5000.00 Hours
6120-300 (O) PN: 3244882-2 SN: C51108 Overhaul Over Torque Limiter. 5000.00 Hours
6120-200 (O) PN: 210949 SN: 16890761 Overhaul Overspeed Governor. 5000.00 Hours
6120-100 (O) PN: 8210-137E SN: 16910258 Overhaul Propeller Governor. 5000.00 Hours
34-08 N/A UPDATE HONEYWELL DATA BASE (PURPLE) 3/12/2014
34-07 N/A UPDATE HONEYWELL DATA BASE (GREEN) 19/07/2014
34-06 N/A UPDATE HONEYWELL DATA BASE (BLUE) 3/12/2014
32-10/315 PN: 532.10.12.054 SN: VLG453 Main landing gear axles and bushes. 2015-09-02, 6460.30 Hours
32-10/315 PN: 532.10.12.053 SN: VLG453 Main landing gear axles and bushes. 2015-09-01, 6460.30 Hours
32-01 PN: 3-1501 SN: 0037 LUBRICATE WHEEL BEARINGS 11/09/2015
28-10/306 N/A Fuel cross vent and outward check valve. 4898.30 Hours
27-50/437 N/A 27-50/437:INBOARD FLAP DRIVE ARMS INSITU INSP/CHECK 2015-11-12, 4889.40 Hours
27-50/30 PN: 978.73.20.309 SN: 3900 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings
27-50/30 PN: 978.73.20.309 SN: 3891 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings
27-50/30 PN: 978.73.20.309 SN: 3874 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings
27-50/30 PN: 978.73.20.309 SN: 3873 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings
27-40/1 N/A Horizontal runaway trim aural warning check 2015-01-23, 6815.20 Hours
25/06/2005 N/A Bucher EMS Wall Panels. 11/09/2015
25/06/2004 N/A Bucher EMS Overhead Track Inspection 11/09/2015
25-06-03B N/A EMS Lighting System. 2/09/2015
25/06/2003 N/A Carry out Bucher EMS Electrical Inspection test 2/09/2015
25-06-02B N/A EMS Suction System. 9/09/2015
25/06/2002 N/A Bucher EMS Oxygen Wall Outlets. 3/09/2015
25/06/2001 N/A Bucher EMS Oxygen System Pipelines. 11/09/2015
2500-302 PN: AV6479-004D0201/2 SN: PC-006 EMS Stretcher Loader. 4839.40 Hours
2431-012C PN: RG-380E/44LS SN: 40649547 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-09-15, 5466.30 Hours
2431-012C PN: RG-380E/44 SN: 40605586 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-07-22, 5322.90 Hours
24-30/311 N/A Generator Control Unit (GCU) 1 and GCU 2. 2015-09-02, 7460.30 Hours
21-40/52 N/A Duct over temperature switch. 2/08/2015
21-40/51 N/A Overtemperature switch. 2/08/2015
21-30/318 N/A Pressurization Dump switch. 2015-05-02, 7085.90 Hours
21-20/317 N/A ECS emergency shut-off system. 2015-09-03, 7460.30 Hours
21-17 PN: 959.90.22.102 SN: 1119 CARRY OUT VCCS SYSTEM ANNUAL INSPECTION 9/09/2015
5-Mar N/A OPS 54 IFR Radio Inspection 10/03/2015
0500-550 PHASE 6 RESET N/A PHASE 6 RESET 2015-09-15, 5660.30 Hours
0500-550 [2] N/A Airframe - 6 Phase Progressive Inspection. 4844.70 Hours
0500-500 N/A Airframe - Progressive Mini Inspection. 4839.40 Hours
00-00/45 (INTERNAL) N/A Airframe - Corrosion Protection Compound Inspection - Internal Areas. 11/09/2015
00-00/45 (EXT) MODERATE N/A Airframe - Corrosion Protection Compound Inspection - External Areas. 13/05/2015

I'd like to split the 'Next Due' field and map it based on the contents of it to one or two new fields - Next_Due_Date, Next_Due_Hours, Next_Due_Landings

I've used evals to split those events that contain two options and a field extraction so far:

search
| eval Next_Due_Date_Only=strftime(strptime('Next Due', "%d/%m/%Y"), "%F")
| eval Next_Due_Split=split('Next Due',",")
| eval Next_Due_1=mvindex(Next_Due_Split,0)
| eval Next_Due_2=mvindex(Next_Due_Split,-1)
| eval Next_Due_1_Date=strftime(strptime(Next_Due_1, "%d/%m/%Y"), "%F")
| table host PN_SN Description "Next Due" Next_Due_Split Next_Due_1 Next_Due_2 Next_Due_Date_Only Next_Due_Date_and_Hours_Hours Next_Due_1_Date

Extraction:
^(?:[^,\n]*,){5}\s+(?P[^ ]+)

Which produces:

Task_Check PN_SN Description Next Due Next_Due_Split Next_Due_1 Next_Due_2 Next_Due_Date_Only Next_Due_Date_and_Hours_Hours Next_Due_1_Date
SURVIVAL-WATER PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 30/08/2015 30/08/2015 30/08/2015 30/08/2015 30/08/2015 30/08/2015
SURVIVAL-RATION PACK PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 10/08/2015 10/08/2015 10/08/2015 10/08/2015 10/08/2015 10/08/2015
SURVIVAL-LIP BALM PN: 25-01 SN: 16 REPLACE DUE SERVICE LIFE 1/03/2015 1/03/2015 1/03/2015 1/03/2015 1/03/2015 1/03/2015
SURVIVAL-EPIRP MT410G PN: MT410G SN: 80454243 CARRY OUT EPIRP MT410G INSPECTION IAW MANUFACTURER'S INSTRUCTIONS 19/06/2015 19/06/2015 19/06/2015 19/06/2015 19/06/2015 19/06/2015
PIL-SB-71-008 REV 0 N/A PIL-SB-71-008: REPLACEMENT OF PLENUM SCREEN ASSEMBLY 23/07/2015 23/07/2015 23/07/2015 23/07/2015 23/07/2015 23/07/2015
PIL-SB-52-008 REV 0 N/A SB 52-008: INTRODUCTION OF A NEW CARGO DOOR HANDLE SWITCH 29/01/2015 29/01/2015 29/01/2015 29/01/2015 29/01/2015 29/01/2015
PHASE ENGINE INSP N/A CARRY OUT ENGINE - 100 HOUR & MINOR ITEMS IAW PWC M/M TABLE 601 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

MT410G REPLACEMENT PN: MT410G SN: 80454243 EPIRP LIFE LIMIT 30/05/2015 30/05/2015 30/05/2015 30/05/2015 30/05/2015 30/05/2015
MAINTENANCE RELEASE INSP N/A MAINTENANCE RELEASE INSPECTION 2015-04-22, 4844.70 Hours "2015-04-22
4844.70 Hours" 22/04/2015 4844.70 Hours 4844.7

AD/PT6A/28 req 2 (7 DAY) PN: PT6A-67P SN: PCE-RY0238 AD/PT6A/28 Req 2 (Compressor & Compressor Turbine Desalination wash -coast) 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014
AD/GEN/37R9 N/A AD/GEN/37 AMDT 9 - Emergency Exits. 12/03/2015 12/03/2015 12/03/2015 12/03/2015 12/03/2015 12/03/2015
AD/ENG/5R9 PN: PT6A-67P SN: PCE-RY0238 AD/ENG/5 AMDT 9 - Turbine Engine Continuing Airworthiness Requirements. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

99-5r-1869-eo1 NAV LIGHTS N/A 5R EO 5R-1869-EO1 NAV LIGHTS ALT POWER SOURCE CONT AIRWORTHINESS INSP 2015-02-18, 4887.50 Hours "2015-02-18
4887.50 Hours" 18/02/2015 4887.50 Hours 4887.5

99-16576.001/1-200 N/A SMOKE GOGGLES 200 HR CONT AIRWORTHINESS REQUIREMENTS INSPECTION 5043.60 Hours 5043.60 Hours 5043.60 Hours 5043.60 Hours

99-16375.001/2 N/A CARRY OUT VISUAL INSPECTION OF MOTOROLA XTL5000 WHIP ANTENNA 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

99-14907.216/1 N/A EO14907.216/1 Issue 4 Mansell NeoNatal Unit Inst. CONT AIRWORTHINESS REQ 24/05/2015 24/05/2015 24/05/2015 24/05/2015 24/05/2015 24/05/2015
99-14907.206/1 N/A PROPAQ INSTALLATION CONTINUED AIRWORTHINESS INSPECTION REQUIREMENTS 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015
99-14907.049/1 N/A 14907.049/1 issue 3: SA Baggage Bay Annual Inspection 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015
99-14907.033/1 N/A CONTINUED AIRWORTHINESS- AFT FACING SEAT PROTECTION PLATE 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

99-14907.032/1 N/A PERFORM SACRIFICIAL SEAT TRACK CONT AIRWORTHINESS INSP 4944.70 Hours 4944.70 Hours 4944.70 Hours 4944.70 Hours

99-14907.029/1 N/A Perform EO14907.029/1 Issue 6. MEDICAL EQUIPMENT SUPPORTS ANNUAL INSP 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015
99-14907.028/1 N/A EO14907.028/1 Issue 5 Medical Equip Rails annual inspection 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015
99-14907.023/1 N/A Perform EO14907.023/1 Issue 4. AEA Stretcher Loader Installation Inspect 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015
99-14907.022/1 N/A EO14907.022/1 Issue 2. Ferno 2650 Stretcher System installation Insp 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015 2/05/2015
99-14907.015/1 N/A EO14907.015/1 Issue 3. MEDICAL CABINET INSTALL CONT AIRWORTHINESS 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015 1/05/2015
99-14907.014/1 N/A Perform EO14907.014/1 Issue 2. FUEL DRAIN CUP INSPECTION 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

99-14907.008/1 N/A Perform EO14907.008/1 Issue 2. LIFE JACKET INSTALL CONT A/WORTHINESS REQ 29/04/2015 29/04/2015 29/04/2015 29/04/2015 29/04/2015 29/04/2015
99-14907.002/1 N/A Oxygen Mask Stowage - Installation inspection 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

7920-012B PN: PT6A-67P SN: PCE-RY0238 Replace Oil Filter Element. 4887.50 Hours 4887.50 Hours 4887.50 Hours 4887.50 Hours

79-20/43 PN: 968.32.14.102 SN: 1134 Overhaul Oil Cooler. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7530-100 (O) PN: 3059835-01 SN: AHX016123 Overhaul Compressor Bleed Valve. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7410-100 (O) PN: 10-381550-4 SN: NNA10080009 Overhaul Ignition Exciter. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7310-300 (O) PN: 26130-2 SN: 9959525078 Overhaul Flow Divider. 4909.70 Hours 4909.70 Hours 4909.70 Hours 4909.70 Hours

7310-200 (O-02) PN: 825601-5 SN: 010282 Overhaul Engine Driven (HP) Fuel Pump. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7310-100 (O) PN: 10585K SN: WA39256 Overhaul Oil-to-Fuel Heater. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7310-022 PN: PT6A-67P SN: PCE-RY0238 Replace Engine P3 Filter. 4887.50 Hours 4887.50 Hours 4887.50 Hours 4887.50 Hours

7250-250 (D) PN: 3056693-01 SN: HWDD2125-HWDH0765 Discard Power Turbine 2nd Stage Blades. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7250-250 (D) PN: 3037313 SN: YUAA005A562 Discard Power Turbine 2nd Stage Blades. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7250-000 (O) PN: 3072956 SN: PS-RY0238 Overhaul Power Section Module. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

7230-000 (O) PN: 3072959 SN: PCE-RY0238 Overhaul Gas Generator Module. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

72-06 PN: 3037313 SN: YUAA005A562 Inspection Engine Power Turbine Blades. 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

72-05 PN: PT6A-67P SN: PCE-RY0238 EXAMINE , FIRST STAGE COMPRESSOR 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

7200-000 (O) PN: PT6A-67P SN: PCE-RY0238 Overhaul Engine. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

71-00/38 PN: 944.81.21.409 SN: 5432 Replace Engine Shock Mount. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

71-00/38 PN: 944.81.21.409 SN: 5162 Replace Engine Shock Mount. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

71-00/38 PN: 944.81.21.409 SN: 5147 Replace Engine Shock Mount. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

71-00/38 PN: 944.81.21.409 SN: 5146 Replace Engine Shock Mount. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

71-00/38 PN: 944.81.21.409 SN: 5135 Replace Engine Shock Mount. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

6120-300 (O) PN: 3244882-2 SN: C51108 Overhaul Over Torque Limiter. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

6120-200 (O) PN: 210949 SN: 16890761 Overhaul Overspeed Governor. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

6120-100 (O) PN: 8210-137E SN: 16910258 Overhaul Propeller Governor. 5000.00 Hours 5000.00 Hours 5000.00 Hours 5000.00 Hours

34-08 N/A UPDATE HONEYWELL DATA BASE (PURPLE) 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014
34-07 N/A UPDATE HONEYWELL DATA BASE (GREEN) 19/07/2014 19/07/2014 19/07/2014 19/07/2014 19/07/2014 19/07/2014
34-06 N/A UPDATE HONEYWELL DATA BASE (BLUE) 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014 3/12/2014
32-10/315 PN: 532.10.12.054 SN: VLG453 Main landing gear axles and bushes. 2015-09-02, 6460.30 Hours "2015-09-02
6460.30 Hours" 2/09/2015 6460.30 Hours 6460.3

32-10/315 PN: 532.10.12.053 SN: VLG453 Main landing gear axles and bushes. 2015-09-01, 6460.30 Hours "2015-09-01
6460.30 Hours" 1/09/2015 6460.30 Hours 6460.3

32-01 PN: 3-1501 SN: 0037 LUBRICATE WHEEL BEARINGS 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015
28-10/306 N/A Fuel cross vent and outward check valve. 4898.30 Hours 4898.30 Hours 4898.30 Hours 4898.30 Hours

27-50/437 N/A 27-50/437:INBOARD FLAP DRIVE ARMS INSITU INSP/CHECK 2015-11-12, 4889.40 Hours "2015-11-12
4889.40 Hours" 12/11/2015 4889.40 Hours 4889.4

27-50/30 PN: 978.73.20.309 SN: 3900 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings "5000.00 Hours
7000.00 Landings" 5000.00 Hours 7000.00 Landings 7000

27-50/30 PN: 978.73.20.309 SN: 3891 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings "5000.00 Hours
7000.00 Landings" 5000.00 Hours 7000.00 Landings 7000

27-50/30 PN: 978.73.20.309 SN: 3874 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings "5000.00 Hours
7000.00 Landings" 5000.00 Hours 7000.00 Landings 7000

27-50/30 PN: 978.73.20.309 SN: 3873 Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings "5000.00 Hours
7000.00 Landings" 5000.00 Hours 7000.00 Landings 7000

27-40/1 N/A Horizontal runaway trim aural warning check 2015-01-23, 6815.20 Hours "2015-01-23
6815.20 Hours" 23/01/2015 6815.20 Hours 6815.2

25/06/2005 N/A Bucher EMS Wall Panels. 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015
25/06/2004 N/A Bucher EMS Overhead Track Inspection 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015
25-06-03B N/A EMS Lighting System. 2/09/2015 2/09/2015 2/09/2015 2/09/2015 2/09/2015 2/09/2015
25/06/2003 N/A Carry out Bucher EMS Electrical Inspection test 2/09/2015 2/09/2015 2/09/2015 2/09/2015 2/09/2015 2/09/2015
25-06-02B N/A EMS Suction System. 9/09/2015 9/09/2015 9/09/2015 9/09/2015 9/09/2015 9/09/2015
25/06/2002 N/A Bucher EMS Oxygen Wall Outlets. 3/09/2015 3/09/2015 3/09/2015 3/09/2015 3/09/2015 3/09/2015
25/06/2001 N/A Bucher EMS Oxygen System Pipelines. 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015
2500-302 PN: AV6479-004D0201/2 SN: PC-006 EMS Stretcher Loader. 4839.40 Hours 4839.40 Hours 4839.40 Hours 4839.40 Hours

2431-012C PN: RG-380E/44LS SN: 40649547 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-09-15, 5466.30 Hours "2015-09-15
5466.30 Hours" 15/09/2015 5466.30 Hours 5466.3

2431-012C PN: RG-380E/44 SN: 40605586 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-07-22, 5322.90 Hours "2015-07-22
5322.90 Hours" 22/07/2015 5322.90 Hours 5322.9

24-30/311 N/A Generator Control Unit (GCU) 1 and GCU 2. 2015-09-02, 7460.30 Hours "2015-09-02
7460.30 Hours" 2/09/2015 7460.30 Hours 7460.3

21-40/52 N/A Duct over temperature switch. 2/08/2015 2/08/2015 2/08/2015 2/08/2015 2/08/2015 2/08/2015
21-40/51 N/A Overtemperature switch. 2/08/2015 2/08/2015 2/08/2015 2/08/2015 2/08/2015 2/08/2015
21-30/318 N/A Pressurization Dump switch. 2015-05-02, 7085.90 Hours "2015-05-02
7085.90 Hours" 2/05/2015 7085.90 Hours 7085.9

21-20/317 N/A ECS emergency shut-off system. 2015-09-03, 7460.30 Hours "2015-09-03
7460.30 Hours" 3/09/2015 7460.30 Hours 7460.3

21-17 PN: 959.90.22.102 SN: 1119 CARRY OUT VCCS SYSTEM ANNUAL INSPECTION 9/09/2015 9/09/2015 9/09/2015 9/09/2015 9/09/2015 9/09/2015
5-Mar N/A OPS 54 IFR Radio Inspection 10/03/2015 10/03/2015 10/03/2015 10/03/2015 10/03/2015 10/03/2015
0500-550 PHASE 6 RESET N/A PHASE 6 RESET 2015-09-15, 5660.30 Hours "2015-09-15
5660.30 Hours" 15/09/2015 5660.30 Hours 5660.3

0500-550 [2] N/A Airframe - 6 Phase Progressive Inspection. 4844.70 Hours 4844.70 Hours 4844.70 Hours 4844.70 Hours

0500-500 N/A Airframe - Progressive Mini Inspection. 4839.40 Hours 4839.40 Hours 4839.40 Hours 4839.40 Hours

00-00/45 (INTERNAL) N/A Airframe - Corrosion Protection Compound Inspection - Internal Areas. 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015 11/09/2015
00-00/45 (EXT) MODERATE N/A Airframe - Corrosion Protection Compound Inspection - External Areas. 13/05/2015 13/05/2015 13/05/2015 13/05/2015 13/05/2015 13/05/2015

This doesn't seem very efficient though - any ideas on how I can process the 'Next Due' field in a better way - maybe a regex - that will:

1. look for a date in the format dd/mm/YYYY and if found put that date in a new field 'Next_Due_Date' with ISO format YYYY-mm-dd
2. look for a decimal number before the word 'Hours' and if exists put just the decimal number in a new field 'Next_Due_Hours'
3. look for a decimal number before the word 'Landings and if exists put just the decimal number in a new field 'Next_Due_Landings'
4. Be able to handle all of these for a single event

Any ideas welcomed and appreciated!

Thanks!

[rfds · Accepted solution]

Ok, I put on my big boy pants and I've figured out a solution that seems to do the job - I'm noting it here for my own future reference but hopefully it might help someone else sometime:

search
| eval "Original Next Due"='Next Due'
| makemv delim="," "Next Due"
| mvexpand "Next Due"
| eval Next_Due_Date_DDMMYYY=strftime(strptime('Next Due', "%d/%m/%Y"), "%F") 
| eval Next_Due_Date_ISO=strftime(strptime('Next Due', "%F"), "%F") 
| eval Next_Due_Date=coalesce(Next_Due_Date_DDMMYYY,Next_Due_Date_ISO)
| rex field="Next Due" "(?\d+\.\d+) Hours"
| rex field="Next Due" "(?\d+\.\d+) Landings"
| sort Next_Due_Hours Next_Due_Landings Next_Due_Date
| table PN_SN Description "Original Next Due" Next_Due_Hours Next_Due_Landings Next_Due_Date

Where:

| eval "Original Next Due"='Next Due' -- Creates a new field with the original 'Next Due' value before I tamper with it

| makemv delim="," "Next Due" -- Converts the "," delimited "Next Due" field into a multi-value field

| mvexpand "Next Due" -- Expands the values of multi-value "Next Due" field into separate events for each value of the "Next Due" field

| eval Next_Due_Date_DDMMYYY=strftime(strptime('Next Due', "%d/%m/%Y"), "%F") -- Creates a temporary field looking for date values in the "Next Due" field with the format "%d/%m/%Y" and then converting this to ISO format "%F" (YYYY-MM-DD)

| eval Next_Due_Date_ISO=strftime(strptime('Next Due', "%F"), "%F") -- Creates a temporary field looking for date values in the "Next Due" field with ISO format "%F" (YYYY-MM-DD) and then converting this back to ISO format "%F" (YYYY-MM-DD)

| eval Next_Due_Date=coalesce(Next_Due_Date_DDMMYYY,Next_Due_Date_ISO) -- creates a new field 'Next_Due_Date' which takes the value of the first non-NULL field out of the two temporary fields "Next_Due_Date_DDMMYYY" and "Next_Due_Date_ISO"

| rex field="Next Due" "(?\d+.\d+) Hours" -- extracts a decimal number preceeding the word 'Hours'

| rex field="Next Due" "(?\d+.\d+) Landings" - extracts a decimal number preceeding the word 'Landings'

This then returns:

PN_SN   Description Original Next Due   Next_Due_Hours  Next_Due_Landings   Next_Due_Date
PN: AV6479-004D0201/2 SN: PC-006    EMS Stretcher Loader.   4839.40 Hours   4839.40      
N/A Airframe - Progressive Mini Inspection. 4839.40 Hours   4839.40      
N/A CARRY OUT ENGINE - 100 HOUR & MINOR ITEMS IAW PWC M/M TABLE 601 4844.70 Hours   4844.70      
N/A MAINTENANCE RELEASE INSPECTION  2015-04-22, 4844.70 Hours   4844.70      
N/A CARRY OUT VISUAL INSPECTION OF MOTOROLA XTL5000 WHIP ANTENNA    4844.70 Hours   4844.70      
N/A CONTINUED AIRWORTHINESS- AFT FACING SEAT PROTECTION PLATE   4844.70 Hours   4844.70      
N/A Perform EO14907.014/1 Issue 2. FUEL DRAIN CUP INSPECTION    4844.70 Hours   4844.70      
N/A Oxygen Mask Stowage - Installation inspection   4844.70 Hours   4844.70      
PN: 3037313 SN: YUAA005A562 Inspection Engine Power Turbine Blades. 4844.70 Hours   4844.70      
PN: PT6A-67P SN: PCE-RY0238 EXAMINE , FIRST STAGE COMPRESSOR    4844.70 Hours   4844.70      
N/A Airframe - 6 Phase Progressive Inspection.  4844.70 Hours   4844.70      
N/A 5R EO 5R-1869-EO1 NAV LIGHTS ALT POWER SOURCE CONT AIRWORTHINESS INSP   2015-02-18, 4887.50 Hours   4887.50      
PN: PT6A-67P SN: PCE-RY0238 Replace Oil Filter Element. 4887.50 Hours   4887.50      
PN: PT6A-67P SN: PCE-RY0238 Replace Engine P3 Filter.   4887.50 Hours   4887.50      
N/A 27-50/437:INBOARD FLAP DRIVE ARMS INSITU INSP/CHECK 2015-11-12, 4889.40 Hours   4889.40      
N/A Fuel cross vent and outward check valve.    4898.30 Hours   4898.30      
PN: 26130-2 SN: 9959525078  Overhaul Flow Divider.  4909.70 Hours   4909.70      
N/A PERFORM SACRIFICIAL SEAT TRACK CONT AIRWORTHINESS INSP  4944.70 Hours   4944.70      
PN: PT6A-67P SN: PCE-RY0238 AD/ENG/5 AMDT 9 - Turbine Engine Continuing Airworthiness Requirements. 5000.00 Hours   5000.00      
PN: 968.32.14.102 SN: 1134  Overhaul Oil Cooler.    5000.00 Hours   5000.00      
PN: 3059835-01 SN: AHX016123    Overhaul Compressor Bleed Valve.    5000.00 Hours   5000.00      
PN: 10-381550-4 SN: NNA10080009 Overhaul Ignition Exciter.  5000.00 Hours   5000.00      
PN: 825601-5 SN: 010282 Overhaul Engine Driven (HP) Fuel Pump.  5000.00 Hours   5000.00      
PN: 10585K SN: WA39256  Overhaul Oil-to-Fuel Heater.    5000.00 Hours   5000.00      
PN: 3056693-01 SN: HWDD2125-HWDH0765    Discard Power Turbine 2nd Stage Blades. 5000.00 Hours   5000.00      
PN: 3037313 SN: YUAA005A562 Discard Power Turbine 2nd Stage Blades. 5000.00 Hours   5000.00      
PN: 3072956 SN: PS-RY0238   Overhaul Power Section Module.  5000.00 Hours   5000.00      
PN: 3072959 SN: PCE-RY0238  Overhaul Gas Generator Module.  5000.00 Hours   5000.00      
PN: PT6A-67P SN: PCE-RY0238 Overhaul Engine.    5000.00 Hours   5000.00      
PN: 944.81.21.409 SN: 5432  Replace Engine Shock Mount. 5000.00 Hours   5000.00      
PN: 944.81.21.409 SN: 5162  Replace Engine Shock Mount. 5000.00 Hours   5000.00      
PN: 944.81.21.409 SN: 5147  Replace Engine Shock Mount. 5000.00 Hours   5000.00      
PN: 944.81.21.409 SN: 5146  Replace Engine Shock Mount. 5000.00 Hours   5000.00      
PN: 944.81.21.409 SN: 5135  Replace Engine Shock Mount. 5000.00 Hours   5000.00      
PN: 3244882-2 SN: C51108    Overhaul Over Torque Limiter.   5000.00 Hours   5000.00      
PN: 210949 SN: 16890761 Overhaul Overspeed Governor.    5000.00 Hours   5000.00      
PN: 8210-137E SN: 16910258  Overhaul Propeller Governor.    5000.00 Hours   5000.00      
PN: 978.73.20.309 SN: 3900  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings 5000.00      
PN: 978.73.20.309 SN: 3891  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings 5000.00      
PN: 978.73.20.309 SN: 3874  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings 5000.00      
PN: 978.73.20.309 SN: 3873  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings 5000.00      
N/A SMOKE GOGGLES 200 HR CONT AIRWORTHINESS REQUIREMENTS INSPECTION 5043.60 Hours   5043.60      
PN: RG-380E/44 SN: 40605586 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-07-22, 5322.90 Hours   5322.90      
PN: RG-380E/44LS SN: 40649547   CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-09-15, 5466.30 Hours   5466.30      
N/A PHASE 6 RESET   2015-09-15, 5660.30 Hours   5660.30      
PN: 532.10.12.054 SN: VLG453    Main landing gear axles and bushes. 2015-09-02, 6460.30 Hours   6460.30      
PN: 532.10.12.053 SN: VLG453    Main landing gear axles and bushes. 2015-09-01, 6460.30 Hours   6460.30      
N/A Horizontal runaway trim aural warning check 2015-01-23, 6815.20 Hours   6815.20      
N/A Pressurization Dump switch. 2015-05-02, 7085.90 Hours   7085.90      
N/A Generator Control Unit (GCU) 1 and GCU 2.   2015-09-02, 7460.30 Hours   7460.30      
N/A ECS emergency shut-off system.  2015-09-03, 7460.30 Hours   7460.30      
PN: 978.73.20.309 SN: 3900  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings     7000.00  
PN: 978.73.20.309 SN: 3891  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings     7000.00  
PN: 978.73.20.309 SN: 3874  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings     7000.00  
PN: 978.73.20.309 SN: 3873  Overhaul Flap Actuator. 5000.00 Hours, 7000.00 Landings     7000.00  
N/A UPDATE HONEYWELL DATA BASE (GREEN)  19/07/2014          2014-07-19
PN: PT6A-67P SN: PCE-RY0238 AD/PT6A/28 Req 2 (Compressor & Compressor Turbine Desalination wash -coast) 3/12/2014           2014-12-03
N/A UPDATE HONEYWELL DATA BASE (PURPLE) 3/12/2014           2014-12-03
N/A UPDATE HONEYWELL DATA BASE (BLUE)   3/12/2014           2014-12-03
N/A Horizontal runaway trim aural warning check 2015-01-23, 6815.20 Hours           2015-01-23
N/A SB 52-008: INTRODUCTION OF A NEW CARGO DOOR HANDLE SWITCH   29/01/2015          2015-01-29
N/A 5R EO 5R-1869-EO1 NAV LIGHTS ALT POWER SOURCE CONT AIRWORTHINESS INSP   2015-02-18, 4887.50 Hours           2015-02-18
PN: 25-01 SN: 16    REPLACE DUE SERVICE LIFE    1/03/2015           2015-03-01
N/A OPS 54 IFR Radio Inspection 10/03/2015          2015-03-10
N/A AD/GEN/37 AMDT 9 - Emergency Exits. 12/03/2015          2015-03-12
N/A MAINTENANCE RELEASE INSPECTION  2015-04-22, 4844.70 Hours           2015-04-22
N/A Perform EO14907.008/1 Issue 2. LIFE JACKET INSTALL CONT A/WORTHINESS REQ    29/04/2015          2015-04-29
N/A PROPAQ INSTALLATION CONTINUED AIRWORTHINESS INSPECTION REQUIREMENTS 1/05/2015           2015-05-01
N/A Perform EO14907.029/1 Issue 6. MEDICAL EQUIPMENT SUPPORTS ANNUAL INSP   1/05/2015           2015-05-01
N/A EO14907.028/1 Issue 5 Medical Equip Rails annual inspection 1/05/2015           2015-05-01
N/A EO14907.015/1 Issue 3. MEDICAL CABINET INSTALL CONT AIRWORTHINESS   1/05/2015           2015-05-01
N/A 14907.049/1 issue 3: SA Baggage Bay Annual Inspection   2/05/2015           2015-05-02
N/A Perform EO14907.023/1 Issue 4. AEA Stretcher Loader Installation Inspect    2/05/2015           2015-05-02
N/A EO14907.022/1 Issue 2. Ferno 2650 Stretcher System installation Insp    2/05/2015           2015-05-02
N/A Pressurization Dump switch. 2015-05-02, 7085.90 Hours           2015-05-02
N/A Airframe - Corrosion Protection Compound Inspection - External Areas.   13/05/2015          2015-05-13
N/A EO14907.216/1 Issue 4 Mansell NeoNatal Unit Inst. CONT AIRWORTHINESS REQ    24/05/2015          2015-05-24
PN: MT410G SN: 80454243 EPIRP LIFE LIMIT    30/05/2015          2015-05-30
PN: MT410G SN: 80454243 CARRY OUT EPIRP MT410G INSPECTION IAW MANUFACTURER'S INSTRUCTIONS   19/06/2015          2015-06-19
PN: RG-380E/44 SN: 40605586 CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-07-22, 5322.90 Hours           2015-07-22
N/A PIL-SB-71-008: REPLACEMENT OF PLENUM SCREEN ASSEMBLY    23/07/2015          2015-07-23
N/A Duct over temperature switch.   2/08/2015           2015-08-02
N/A Overtemperature switch. 2/08/2015           2015-08-02
PN: 25-01 SN: 16    REPLACE DUE SERVICE LIFE    10/08/2015          2015-08-10
PN: 25-01 SN: 16    REPLACE DUE SERVICE LIFE    30/08/2015          2015-08-30
PN: 532.10.12.053 SN: VLG453    Main landing gear axles and bushes. 2015-09-01, 6460.30 Hours           2015-09-01
PN: 532.10.12.054 SN: VLG453    Main landing gear axles and bushes. 2015-09-02, 6460.30 Hours           2015-09-02
N/A EMS Lighting System.    2/09/2015           2015-09-02
N/A Carry out Bucher EMS Electrical Inspection test 2/09/2015           2015-09-02
N/A Generator Control Unit (GCU) 1 and GCU 2.   2015-09-02, 7460.30 Hours           2015-09-02
N/A Bucher EMS Oxygen Wall Outlets. 3/09/2015           2015-09-03
N/A ECS emergency shut-off system.  2015-09-03, 7460.30 Hours           2015-09-03
N/A EMS Suction System. 9/09/2015           2015-09-09
PN: 959.90.22.102 SN: 1119  CARRY OUT VCCS SYSTEM ANNUAL INSPECTION 9/09/2015           2015-09-09
PN: 3-1501 SN: 0037 LUBRICATE WHEEL BEARINGS    11/09/2015          2015-09-11
N/A Bucher EMS Wall Panels. 11/09/2015          2015-09-11
N/A Bucher EMS Overhead Track Inspection    11/09/2015          2015-09-11
N/A Bucher EMS Oxygen System Pipelines. 11/09/2015          2015-09-11
N/A Airframe - Corrosion Protection Compound Inspection - Internal Areas.   11/09/2015          2015-09-11
PN: RG-380E/44LS SN: 40649547   CONCORDE BATTERY CAPACITY TEST (PC12-47E = 24-30/340; PC12-45/47 = 24-30/69 2015-09-15, 5466.30 Hours           2015-09-15
...

If you've got a better idea let me know!