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!
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
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!
Give this a try
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 by key
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?
So, you're getting more than 1 events with different slaName for same key/issueKey. Do you want to just keep the latest one? If yes, then change values(slaName) to latest(slaName).
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
Can you share some sample events from both data sources?
What links events in jira:issues:json to events in jira:sla:json e.g. key exists in both?
Are the fields already extracted?
Assuming some of these answers, you could try something like this (stats isn't just for statistics, it also can be used to "gather" data from different events)
sourcetype="jira:sla:json" OR sourcetype="jira:issues:json"
| stats values(team_name) as team_name values(created_weekNo) as created_weekNo values(ceated_yearNo) as created_yearNo values(creationDate) as creationDate values(slaName) as slaName by key
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 |
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