Can someone help with the search to format this table?

tkwaller_2 — Fri, 27 Jul 2018 15:42:01 GMT

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!

Re: Can someone help with the search to format this table?

somesoni2 — Fri, 27 Jul 2018 19:10:48 GMT

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)?

Re: Can someone help with the search to format this table?

tkwaller_2 — Fri, 27 Jul 2018 20:29:20 GMT

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

Re: Can someone help with the search to format this table?

somesoni2 — Fri, 27 Jul 2018 20:44:01 GMT

So, in the final output there will only single row? What value should each "week ending date" should show?

Re: Can someone help with the search to format this table?

tkwaller_2 — Mon, 30 Jul 2018 14:16:13 GMT

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

Re: Can someone help with the search to format this table?

tkwaller_2 — Mon, 30 Jul 2018 14:55:54 GMT

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

topic Re: Can someone help with the search to format this table? in Splunk Search

Can someone help with the search to format this table?

Re: Can someone help with the search to format this table?

Re: Can someone help with the search to format this table?

Re: Can someone help with the search to format this table?

Re: Can someone help with the search to format this table?

Re: Can someone help with the search to format this table?