Only show rows from a certain year based on datestamp?

[rkassabov]

I have the following query that looks at data from all-time (according to Splunk date window). My understanding is that the Splunk time frame selector goes off of when the data was added to the system, not the datestamps I am using below.

How can I only show rows from 2018 based on my "Step Date Completed" field?

index=xxx "BO Type" = "xxx" "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" [`init("xxx")`  |rename id as "BO ID" |rename info_name as xxx 
| 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 [ `init("vendors")`  |rename id as RV | rename info_name as VendorName info_owner_deptTechnical as "LOB" | dedup VendorName]

|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 , avg(daysPastDue) AS avgDuration
by "BOSUBTYPE" "Workflow Name" "Workflow Phase" "Workflow Process Name" "Workflow Step Name" "Responsible Parties _roles" SLA  "Workflow Step Sort Order" "Step Date Completed"
| eval winRate = (Wins/Total)
| eval winRate = round(winRate,1)*100
| eval winRate = winRate + " %"
| eval avgDuration = round(avgDuration,1)

| eval month=strftime(strptime('Step Date Completed',"%Y-%m-%d %H:%M:%S.%6N"),"%b")

| eval "January Ops"=if(month="Jan",Total,null())
| eval "February Ops"=if(month="Feb",Total,null())
| eval "March Ops"=if(month="Mar",Total,null())
| eval "April Ops"=if(month="Apr",Total,null())
| eval "May Ops"=if(month="May",Total,null())
| eval "June Ops"=if(month="Jun",Total,null())
| eval "July Ops"=if(month="Jul",Total,null())
| eval "August Ops"=if(month="Aug",Total,null())
| eval "September Ops"=if(month="Sept",Total,null())
| eval "October Ops"=if(month="Oct",Total,null())
| eval "November Ops"=if(month="Nov",Total,null())
| eval "December Ops"=if(month="Dec",Total,null())

| eval "January Wins"=if(month="Jan",Wins,null())
| eval "February Wins"=if(month="Feb",Wins,null())
| eval "March Wins"=if(month="Mar",Wins,null())
| eval "April Wins"=if(month="Apr",Wins,null())
| eval "May Wins"=if(month="May",Wins,null())
| eval "June Wins"=if(month="Jun",Wins,null())
| eval "July Wins"=if(month="Jul",Wins,null())
| eval "August Wins"=if(month="Aug",Wins,null())
| eval "September Wins"=if(month="Sept",Wins,null())
| eval "October Wins"=if(month="Oct",Wins,null())
| eval "November Wins"=if(month="Nov",Wins,null())
| eval "December Wins"=if(month="Dec",Wins,null())

| eval "January WR"=if(month="Jan",winRate,null())
| eval "February WR"=if(month="Feb",winRate,null())
| eval "March WR"=if(month="Mar",winRate,null())
| eval "April WR"=if(month="Apr",winRate,null())
| eval "May WR"=if(month="May",winRate,null())
| eval "June WR"=if(month="Jun",winRate,null())
| eval "July WR"=if(month="Jul",winRate,null())
| eval "August WR"=if(month="Aug",winRate,null())
| eval "September WR"=if(month="Sept",winRate,null())
| eval "October WR"=if(month="Oct",winRate,null())
| eval "November WR"=if(month="Nov",winRate,null())
| eval "December WR"=if(month="Dec",winRate,null())

|table "Responsible Parties _roles" "BOSUBTYPE" "Workflow Name" "Workflow Phase" "Workflow Process Name" "Workflow Step Name" SLA avgDuration "Workflow Step Sort Order" "January Ops" "January Wins" "January WR" "February Ops" "February Wins" "February WR" "March Ops" "March Wins" "March WR" "April Ops" "April Wins" "April WR" "May Ops" "May Wins" "May WR" "June Ops" "June Wins" "June WR" "July Ops" "July Wins" "July Ops" "July WR" "August Ops" "August Wins" "August WR" "September Ops" "September Wins" "September WR" "October Ops" "October Wins" "October WR" "November Ops" "November Wins" "November WR" "December Ops" "December Wins" "December WR"
|rename "BOSUBTYPE" AS "Assessment Type" "Workflow Phase" AS "Phase Name" "Workflow Process Name" AS "Process Name" "Workflow Step Name" AS "Step Name" SLA AS "Step Duration" "Responsible Parties _roles" AS "Responsible Party" avgDuration AS "Avg Step Duration"  "Workflow Step Sort Order" as "Step Order"
| sort "Step Order"
| sort "Process Name"

[brad_thomas · Accepted solution]

Here's one way to do it.

Strip out the year by adding this after the | eval month=... line:

| eval year=strftime(strptime('Step Date Completed',"%Y-%m-%d %H:%M:%S.%6N"),"%Y")

Then add this before the |table command:

| search year = "some year"

[rkassabov]

Great solution, thanks!