Transforming Commands

jbuddy24 — Tue, 09 Nov 2021 02:13:08 GMT

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:

Goal:

key	team_name	start_DateNo	start_weekNo	start_yearNo	slaName	UNIQUE_SLA_Count
ADVANA-104	ADVANA	2020-6-11	24	20	DSDE Pending Approval SLA	ADVANA-104 / 24 / 20 / DSDE Pending Approval SLA
ADVANA-104	ADVANA	2020-6-11	24	20	DSDE Ready to Start SLA	ADVANA-104 / 24 / 20 / DSDE Ready to Start SLA
ADVANA-104	ADVANA	2021-5-14	19	21	DSDE In Progress SLA	ADVANA-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!

Re: Transforming Commands

gcusello — Tue, 09 Nov 2021 07:15:53 GMT

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

topic Transforming Commands in Splunk Search

Transforming Commands

Re: Transforming Commands