About jbuddy24

jbuddy24 · ‎11-08-2021

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!

jbuddy24 · ‎10-21-2021

@somesoni2 & @ITWhisperer Closing the book on this one, the last query got the results I was looking for on this topic. Thank you both for your quick and knowledgeable help, much appreciated!

jbuddy24 · ‎10-21-2021

@somesoni2 I think I figured it out, it looks like mvexpand did the trick getting the slaName data on different rows (I included in the picture below- highlighted): index=jira sourcetype="jira:sla:json" OR sourcetype="jira:issues:json" | rex field=fields.created "(?P<creationDate>\d+-\d+-\d+)" | rex field=fields.created "(?P<creationTime>\d+:\d+:\d+)" | eval created_weekNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%V") | eval created_yearNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%y") | eval key=coalesce(key,issueKey) | stats values(team_name) as team_name values(created_weekNo) as created_weekNo values(created_yearNo) as created_yearNo values(creationDate) as creationDate values(slaName) as slaName values(fields.status.name) as fields.status.name by key | mvexpand slaName | sort key

jbuddy24 · ‎10-21-2021

@somesoni2 That is funny, I just found this similar solution with SPL: index=jira sourcetype="jira:sla:json" OR sourcetype="jira:issues:json" | rex field=fields.created "(?P<creationDate>\d+-\d+-\d+)" | rex field=fields.created "(?P<creationTime>\d+:\d+:\d+)" | eval created_weekNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%V") | eval created_yearNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%y") | eval key_edited = coalesce(key, issueKey) | stats values(team_name) as team_name values(created_weekNo) as created_weekNo values(created_yearNo) as created_yearNo values(creationDate) as creationDate values(slaName) as slaName by key_edited The only problem now is that the slaName needs to be on a difference row. It's combined to one cell looking like this: Trying to explore looking into the mvexpand to see if that might help?

jbuddy24 · ‎10-21-2021

I'm also looking into this article as well that I found, I wonder if this could help with renaming a field into the same one, I just hope that this key field that I have an extraction on isn't affected: https://community.splunk.com/t5/Splunk-Search/convert-two-values-same-name/m-p/24246

jbuddy24 · ‎10-21-2021

Thank you for the response, . That's a good reminder the stats can be used for just data gathering as well, I'll definitely use that more moving forward with more organized data. On that note, it looks as though between the two sources, the field "key" is not the same (shown below in table), although I still mention that the values rendered are the unique id's. Ultimately, I need Splunk's version of a vlookup between these two source columns using the values in the following columns as the Unique Identifiers. PS I tried seeing if I could do a field extraction making the columns the same name, but I'm already using the string to generate the team_name. Below is query I entered, the return results, and a reference table that indicates the "key" to sourcetype: index=jira sourcetype="jira:sla:json" OR sourcetype="jira:issues:json"| rex field=fields.created "(?P<creationDate>\d+-\d+-\d+)" | rex field=fields.created "(?P<creationTime>\d+:\d+:\d+)" | eval created_weekNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%V") | eval created_yearNo=strftime(strptime(creationDate,"%Y-%m-%d"),"%y")| stats values(team_name) as team_name values(created_weekNo) as created_weekNo values(created_yearNo) as created_yearNo values(creationDate) as creationDate values(slaName) as slaName by key By "key": key team_name created_weekNo created_yearNo creationDate slaName ADVANA-100 ADVANA 24 20 2020-06-08 By "issueKey": issueKey team_name created_weekNo created_yearNo creationDate slaName ADVANA-100 DSDE In Progress SLA DSDE Pending Approval SLA DSDE Ready to Start SLA Intended: issueKey team_name created_weekNo created_yearNo creationDate slaName ADVANA-100 ADVANA 24 20 2020-06-08 DSDE In Progress SLA ADVANA-100 ADVANA 24 20 2020-06-08 DSDE Pending Approval SLA ADVANA-100 ADVANA 24 20 2020-06-08 DSDE Ready to Start SLA Reference Table: Sourcetype Column Reference jira:issues:json key jira:sla:json issueKey @ITWhisperer

jbuddy24 · ‎10-20-2021

Hi All, I'm trying to get data tied together into one matrix from Jira (API fed) that utilizes two source types (shown below). What I need is each issue(key) to have the following attributes represented as a column: Column Name sourcetype key jira:issues:json team_name jira:issues:json created_weekNo jira:issues:json created_yearNo jira:issues:json creationDate jira:issues:json slaName jira:sla:json Problem: The "key" is the unique identifier in this case that can marry the data sets, but I'm having trouble getting the "slaName" from my "jira:sla:json" to combine as a column with my "jira:issues:json". Side note the "key" will have multiple entries that need to be reflected as separate rows, but the column information obtained from the "jira:issues:json" will be static over the lifetime of the ticket (as this is just the created date). Ask: If anyone has any best practices that could help me out it would be greatly appreciated. Using the subsearch and appendcols in getting confusing as I'm not looking for any stats functions right now, just getting the table together is the main goal to eventually turn this into a visualization. Thanks for your help!

Posts	7
Solutions	1
Karma Given	2
Karma Received	0
Member Since	‎10-20-2021

Online Status	Offline
Date Last Visited	‎11-08-2021 11:54 PM

Transforming Commands

Best way to create a new column/s for data matrix ...

Transforming Commands

Re: Best way to create a new column/s for data mat...

Re: Best way to create a new column/s for data mat...

Re: Best way to create a new column/s for data mat...

Re: Best way to create a new column/s for data mat...

Re: Best way to create a new column/s for data mat...

Best way to create a new column/s for data matrix ...