Splunk Search

Transforming Commands


I'm having issue with a search of mine. I've been trying to organize the matrix so that it will be ready for my pivot and then eventually a dashboard visual, but there are three columns that seem to be troublesome. 

It seem as though my eval command is only working with one of the start_DateNo and returning results for only one instance (see pictorial below). Is there a order of operations that I'm missing with my formula, or is there a better command to get the data to what I want? In addition it seems like my "slaName" isn't being reflected accurately as well. 

Below I have a snip-it of the error, and then a row/column matrix goal to what I'm ultimately trying to get the data to. 





keyteam_namestart_DateNostart_weekNostart_yearNoslaName  UNIQUE_SLA_Count
ADVANA-104ADVANA2020-6-112420DSDE Pending Approval SLAADVANA-104 / 24 / 20 / DSDE Pending Approval SLA
ADVANA-104ADVANA2020-6-112420DSDE Ready to Start SLAADVANA-104 / 24 / 20 / DSDE Ready to Start SLA
ADVANA-104ADVANA2021-5-141921DSDE In Progress SLAADVANA-104 / 19 / 21 / DSDE In Progress SLA


Any help would be much appreciated, I've been going back a forth for a few hours now trying to get this to where I need it. 


For editing purposes, here is the SPL from the picture above:

index=jira sourcetype="jira:sla:json" OR sourcetype="jira:issues:json"
| rex field=startDate "(?P<start_DateNo>\d+-\d+-\d+)"
| rex field=startDate "(?P<start_TimeNo>\d+:\d+:\d+)"
| eval start_weekNo=strftime(strptime(start_DateNo,"%Y-%m-%d"),"%V")
| eval start_yearNo=strftime(strptime(start_DateNo,"%Y-%m-%d"),"%y")
| eval key=coalesce(key,issueKey)
| stats values(team_name) as team_name values(start_DateNo) as start_DateNo values(start_weekNo) as start_weekNo values(start_yearNo) as start_yearNo values(slaName) as slaName values(fields.status.name) as fields.status.name by key
| mvexpand slaName
| mvexpand start_DateNo
| mvexpand start_weekNo
| mvexpand start_yearNo
| where team_name="ADVANA" | where key="ADVANA-104"
| strcat key " / " start_weekNo " / " start_yearNo " / " slaName UNIQUE_SLA_Count | search UNIQUE_SLA_Count="ADVANA-104 / 19 / 20 / DSDE Pending Approval SLA "


Thank you!


Labels (2)
0 Karma

Esteemed Legend

Hi @jbuddy24,

you complicated a search that could be simple (if I correctly understood your need!), please try something like this:

index=jira sourcetype="jira:sla:json" OR sourcetype="jira:issues:json" team_name="ADVANA" (key="ADVANA-104" OR issueKey="ADVANA-104")
| eval
     start_DateNo=strftime(strptime(startDate,"\d+-\d+-\d+ \d+:\d+:\d+),"\d+-\d+-\d+"),
     start_TimeNo=strftime(strptime(startDate,"\d+-\d+-\d+ \d+:\d+:\d+),"\d+:\d+:\d+)"),
| stats 
     values(team_name) as team_name 
     values(key) as key 
     values(start_yearNo) as start_yearNo 
     values(start_weekNo) AS start_weekNo 
     values(fields.status.name) as fields.status.name 
     by slaName start_DateNo 
| eval UNIQUE_SLA_Count=key." / ".start_weekNo." / ".start_yearNo." / ".slaName.UNIQUE_SLA_Count 
| search UNIQUE_SLA_Count="ADVANA-104 / 19 / 20 / DSDE Pending Approval SLA "
| table key team_name start_DateNo start_weekNo start_yearNo slaName fields.status.name



0 Karma
Get Updates on the Splunk Community!

Unify Your SecOps with Splunk Mission Control

In today’s post, I'm excited to share some recent Splunk Mission Control innovations. With Splunk Mission ...

Data Preparation Made Easy: SPL2 for Edge Processor

By now, you may have heard the exciting news that Edge Processor, the easy-to-use Splunk data preparation tool ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...