I am attempting to create sub tables from a main table, progressively removing columns and grouping rows.
I have created the following sub table, but would now like to remove "Process" and group by "Phase" while summing "Process duration" to get "Phase duration":
index=fp_dev_tsv "BO Type" = "assessments" "BO ID" = *
| convert timeformat="%Y-%m-%d %H:%M:%S.%6N" mktime("Step Date Started") AS starttime mktime("Step Date Completed") AS endtime
|eval dateRange=mvrange(starttime,endtime+86400,86400)
|convert ctime(dateRange) timeformat="%+" | eval daysPastDue=mvcount(mvfilter(NOT match(dateRange,"(Sun|Sat).*")))
|eval isWin = if (daysPastDue < SLA, "Win","Loss")
| rename "BO Subtype" as "BOSUBTYPE"
|dedup "BOSUBTYPE", "BO Name", "Workflow Step Name", "Workflow Name"
| table "Responsible Parties _roles" "Workflow Name" isWin "Workflow Step Name" "BOSUBTYPE" "BO Name" "Workflow Phase" "Step Date Started" "Step Date Completed" daysPastDue starttime "Workflow Step Sort Order" SLA "BO ID" id "Workflow Process Name"
| sort "Workflow Step Sort Order"
| rex mode=sed field="Responsible Parties _roles" "s/[][]//g"
| rex mode=sed field="Responsible Parties _roles" "s/'//g"
|join "BO ID" [search index=fp_dev_tsv md_type="assessments" info_owner_orgID="Noble America" |rename id as "BO ID" |rename info_name as AssessmentName
| rex mode=sed field="related_vendors" "s/[][]//g"
| rex mode=sed field="related_vendors" "s/'//g"
| makemv delim="," related_vendors
| eval RV = mvindex(related_vendors,0)]
|join RV [ search index=fp_dev_tsv md_type=vendors info_owner_orgID="Noble America" |rename id as RV | rename info_name as VendorName info_owner_deptTechnical as "LOB"]
| fillnull value=None "Responsible Parties _roles"
|stats sum(eval(if(match(isWin,"Win"),1,0))) as Wins
sum(eval(if(match(isWin,"Loss"),1,0))) as Losses, count AS Total ,dc("BO ID") as ttlAssessments sum(SLA) as processDuration sum(daysPastDue) AS avgDuration
by "BOSUBTYPE" "Workflow Name" "Workflow Phase" "Workflow Process Name" "Responsible Parties _roles"
| eval winRate = (Wins/Total)
| eval winRate = round(winRate,1)*100
| eval winRate = winRate + " %"
| eval winRate = round(winRate,1)*100
|eval processDuration = processDuration/ttlAssessments
|eval avgDuration = avgDuration/ttlAssessments
| eval avgDuration = round(avgDuration,2)
|table "Responsible Parties _roles" "BOSUBTYPE" "Workflow Name" "Workflow Phase" "Workflow Process Name" processDuration avgDuration Wins Losses Total winRate ttlAssessments
|rename "BOSUBTYPE" AS "Assessment Type" "Workflow Phase" AS "Phase Name" "Workflow Process Name" AS "Process Name" "Workflow Step Name" AS "Step Name" processDuration AS "Process Duration" "Responsible Parties _roles" AS "Responsible Party" avgDuration AS "Avg Process Duration" Total AS Opportunities
What the table currently looks like:
How would I go about doing this?
