Splunk Search

Can someone help with the search to format this table?

tkwaller_2
Communicator

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

tkwaller_2
Communicator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

tkwaller_2
Communicator

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

0 Karma

tkwaller_2
Communicator

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
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...