Splunk Search

Transforming Commands

jbuddy24
Explorer

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. 

Error: 

jbuddy24_1-1636423774613.png

 

Goal:

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

gcusello
SplunkTrust
SplunkTrust

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+)"),
     start_weekNo=strftime(strptime(start_DateNo,"%Y-%m-%d"),"%V"),
     start_yearNo=strftime(strptime(start_DateNo,"%Y-%m-%d"),"%y")
| 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

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Splunk App for Anomaly Detection End of Life Announcement

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...

Transforming Financial Data into Fraud Intelligence

Every day, banks and financial companies handle millions of transactions, logins, and customer interactions ...

How to send events & findings from AWS to Splunk using Amazon EventBridge

Amazon EventBridge is a serverless service that uses events to connect application components together, making ...