Splunk Search

Best way to create a new column/s for data matrix ( #subsearch #appendcols )

jbuddy24
Explorer

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 Namesourcetype
keyjira:issues:json
team_namejira:issues:json
created_weekNojira:issues:json
created_yearNojira:issues:json
creationDatejira:issues:json
slaNamejira: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!

Labels (1)
Tags (1)
0 Karma
1 Solution

jbuddy24
Explorer

@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_0-1634852066836.png

 

View solution in original post

0 Karma

jbuddy24
Explorer

@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! 

0 Karma

somesoni2
Revered Legend

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
Tags (1)

jbuddy24
Explorer

@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:

jbuddy24_0-1634850712777.png

Trying to explore looking into the mvexpand to see if that might help?

 

Tags (1)
0 Karma

somesoni2
Revered Legend

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

0 Karma

jbuddy24
Explorer

@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_0-1634852066836.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

jbuddy24
Explorer

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":

 keyteam_name created_weekNo created_yearNo creationDate slaName
ADVANA-100ADVANA24202020-06-08 

 

By "issueKey":

 issueKeyteam_name created_weekNo created_yearNo creationDate slaName
ADVANA-100    
DSDE In Progress SLA
 
DSDE Pending Approval SLA
 
DSDE Ready to Start SLA

 

Intended:

 issueKeyteam_name created_weekNo created_yearNo creationDate slaName
ADVANA-100ADVANA24202020-06-08DSDE In Progress SLA
ADVANA-100ADVANA24202020-06-08DSDE Pending Approval SLA
ADVANA-100ADVANA24202020-06-08DSDE Ready to Start SLA

 

Reference Table:

SourcetypeColumn Reference
jira:issues:jsonkey
jira:sla:jsonissueKey

@ITWhisperer

0 Karma

jbuddy24
Explorer

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

0 Karma