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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...