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!
What is "prior" column in your pdf relates to in your current output? Also, is CompletedTotals (in last table) same as CompletedNumbers (in last query)?
OK I updated the question for the CompletedTotals/CompletedNumbers issue.
The Prior column in the pdf means prior months. What they are looking for is when was the step completed, this month, last month or prior? So what Im doing is taking the field Step Date Completed, IF it exists and calculating the month it was completed using:
| 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")
If it doesnt exist I fill with NA
So, in the final output there will only single row? What value should each "week ending date" should show?
So what I'm expecting, maybe incorrectly lol, is a single row,the table would be like:
AssessmentName RiskRank "Workflow Phase" "Workflow Process Name" "Workflow Step Name" "Prior" "Previous Month" "Current Month" "7/6" "7/13" "7/20" "7/27" "8/3" "8/10" "8/17" "8/24" "8/31" "TOTAL PENDING" "TOTAL COMPLETE"
With a row like:
Test Co. SIG Full Contract for construction services cntrct#5 Plan Risk Rank Risk Rank Submitted by Business Completed <count(compMonth) by vaues(compMonth)> <count(CompletionDate) by values(CompletionDate)> 1 31
To answer your question:
What value should each "week ending date" should show?
<count(CompletionDate) by values(CompletionDate)>
would be fine
In was hoping this woudl work but it did not:
| stats count(compMonth) as compMonth count("Projected Completion Date") as "Projected Completion Date" by values(compMonth) values("Projected Completion Date") AssessmentName RiskRank "Workflow Phase" "Workflow Process Name" "Workflow Step Name" stepStatus