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

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

jbuddy24 — Wed, 20 Oct 2021 23:56:20 GMT

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!

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

ITWhisperer — Thu, 21 Oct 2021 08:00:19 GMT

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

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

jbuddy24 — Thu, 21 Oct 2021 18:59:36 GMT

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

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

jbuddy24 — Thu, 21 Oct 2021 19:07:16 GMT

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

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

somesoni2 — Thu, 21 Oct 2021 20:54:28 GMT

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

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

jbuddy24 — Thu, 21 Oct 2021 21:12:41 GMT

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

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

somesoni2 — Thu, 21 Oct 2021 21:26:07 GMT

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

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

jbuddy24 — Thu, 21 Oct 2021 21:35:38 GMT

@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

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

jbuddy24 — Thu, 21 Oct 2021 22:34:16 GMT

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