Hello I have challenged with a difficult formatting task. I have a very long search
index=testindex "Type" = "services" "ID" = *
| dedup "Name", "Workflow Phase", "Workflow Process Name", "Workflow Step Name"
| convert timeformat="%Y-%m-%d %H:%M:%S.%6N" mktime("Step Date Started") AS starttime mktime("Step Date Completed") AS endtime mktime("Step Due Date") AS stepDueDate
| eval dueDateRange=mvrange(stepDueDate,now(),86400)
| convert ctime(dueDateRange) timeformat="%+" | eval daysPastDue=mvcount(dueDateRange)
| fillnull value="" "Step Date Started" "Step Date Completed"
| eval stepStatus=case('Step Date Started' == "" AND 'Step Date Completed' == "", "Not Started", 'Step Date Started' != "" AND 'Step Date Completed' == "", "Started", 'Step Date Started' != "" AND 'Step Date Completed' != "", "Completed" )
| eval DaysPastDue=if(stepStatus == "Completed", "0", daysPastDue)
| eval isWin = if(DaysPastDue > 'SLA', "Miss","Win")
| eval MissedSLA=if(isWin == "Miss", "Miss", "")
| eval previousMonthepoch=relative_time(now(), "-1mon@mon")
| eval previousMonth=strftime(previousMonthepoch,"%b")
| eval nowMonth=strftime(now(),"%b")
| eval eventCompMonth=strftime(strptime('Step Date Completed',"%Y-%m-%d %H:%M:%S.%6N"),"%b")
| eval compMonth=case( nowMonth == eventCompMonth, "Current Month", previousMonth == eventCompMonth, "Previous Month", previousMonth != eventCompMonth, "Previous")
| eval datecompleted_epoch = strptime('Step Date Started', "%Y-%m-%d %H:%M:%S.%6N")
| eval slaSeconds='SLA' * 86400
| eval newDate= datecompleted_epoch + slaSeconds
| eval updatedProjCompDate=strftime('newDate', "%Y-%m-%d %H:%M:%S.%6N")
| eval "Projected Completion Date"=case(stepStatus == "Not Started", 'Step Due Date', stepStatus == "Started", updatedProjCompDate, stepStatus == "Completed", 'Step Date Completed')
|join "ID" [`init(services)`
|rename id as "ID" info_name as RiskRank
| 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)
|fields RV RiskRank "ID"]
| join RV [`init(assessments)` |rename id as RV |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)
|fields RV AssessmentName]
|fillnull value=NA
| table AssessmentName RiskRank "Workflow Phase" "Workflow Process Name" "Workflow Step Name" stepStatus compMonth SLA "Projected Completion Date"
For reasons I have to join due to same fieldnames using different values based on the value in the init macro.
This results in a table such as :
AssessmentName RiskRank Workflow Phase Workflow Process Name Workflow Step Name stepStatus compMonth SLA Projected Completion Date
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Approved Not Started NA 3 2018-08-29 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Add Vendor Locations Not Started NA 5 2018-08-24 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Locations Not Started NA 5 2018-08-17 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Full Legal Name Not Started NA 5 2018-08-10 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Select Engagement Scope Not Started NA 5 2018-08-03 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Contract Not Started NA 2 2018-07-27 16:02:46
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Reviewed Started NA 3 2018-07-21 19:02:54
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Submitted by Business Completed Current Month 2 2018-07-18 19:02:49
I'm trying to re-create the attached table.link text
I have the necessary data calculated by adding this at the end but it doesnt give the format I'm trying to create:
| eventstats count("Workflow Step Name") as CompltetedMonth by AssessmentName RiskRank "Workflow Phase" "Workflow Process Name" compMonth
| eventstats count(eval((stepStatus != "Completed"))) as Pending, count(eval((stepStatus = "Completed"))) as CompletedTotals
Which returns a table like
AssessmentName RiskRank Workflow Phase Workflow Process Name Workflow Step Name stepStatus compMonth SLA Projected Completion Date CompletedTotals CompltetedMonth Pending
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Approved Not Started NA 3 2018-08-29 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Add Vendor Locations Not Started NA 5 2018-08-24 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Locations Not Started NA 5 2018-08-17 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Full Legal Name Not Started NA 5 2018-08-10 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Select Engagement Scope Not Started NA 5 2018-08-03 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Link Contract Not Started NA 2 2018-07-27 16:02:46 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Reviewed Started NA 3 2018-07-21 19:02:54 23 15 31
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Submitted by Business Completed Current Month 2 2018-07-18 19:02:49 23 1 31
Any ideas how I could alter my table/eventstats to more closely duplicate the attached table/chart?
Thanks for the help!
... View more