JQL – How have we not talked about this already?

Hello everyone! How has your week been going? This week we are going to talk about JQL – and honestly, I’m not entirely sure how this hasn’t come up already. I know I’ve provided a few queries here and there to help you in other topics, but today I’ll be talking about some of my favorite tips and tricks to get the most out of your queries. So without any more delays, let’s get into it.

Always about the Docs

So it’s been a while since I’ve included this section – but I feel this subject could be helped with some further reading. So for this, I am referencing two documents:

I should note that the function reference is for JIRA Cloud – so it does have some new functions that have not made it to server. However, the options I’m covering here will work for JIRA Cloud, JIRA Server, and JIRA Data Center.

Currentuser()

So, this first trick is one of my favorites, as it allows you to customize a query to be specific for whoever is viewing it. In your query, you can set a user field equal to currentuser(), and the function will return whoever is logged in and viewing the query. For example, let’s take the query:

assignee = currentuser()

And let us take a look at what that looks like when running from the Admin account, and my account:

Each one returns whatever is assigned to the specific user. What’s more, this would carry over to any dashboard that uses this query – allowing you to customize a single dashboard to whoever views it.

This also works with any user field: reporter, assignee, watcher – and even custom fields. This is a handy trick to have if you don’t like to spend your day making queries and dashboards…I mean, who would enjoy that, am I right…

Working with time

So, when I’m giving some training for JQL, the most common problem people have is working with time. “How do I search for stuff in the past?” “How do I work with time?”…it seems to be a challenge.

Here’s a trick that’s always helped me. Most databases store time in the Unix Epoch time format – which is simply counting the seconds between midnight of 1 Jan 1970 and today. This means that any time in the past will be less than the current time, and any time greater than now will be in the future. Same for JQL. The past is “less than” the timestamp you are looking at, and the future is “greater than”.

However, JIRA also gives us some handy functions so we are not having to spell out particular date. First off is now(), which simply returns the current timestamp whenever it’s run. Handy when just trying to find out what’s past due, such as in this example (courtesy Atlassian):

duedate < now() and status not in (closed, resolved)

In the above query, duedate < now() returns everything that has a duedate in the past, and status not in (closed, resolved) returns everything that is currently open – giving you an idea what is past-due.

DayWeekMonthYear
startingstartOfDay()startofWeek()startaOfMonth()startOfYear()
endingendOfDay()endOfWeek()endOfMonth()endOfYear()

These are great for establishing a time frame. The really powerful thing about the functions on this table is they take parameters, meaning you can adjust them forward and backward – to say find all the issues resolved last month:

resolutiondate > startOfMonth(-1)  AND resolutiondate < endOfMonth(-1) 

In this query, we shift both startOfMonth() and endOfMonth() by -1. The minus (-) tells us it’s a previous time, and the one (1) tells us to shift one whole month, meaning both of these times return values for the previous calendar month. This gives us a handy time-box for our queries – which is great for a report.

Sprints and Versions

So, this section is for my JIRA Software teams out there.

When I’m a part of Agile teams, I like to have a dashboard we can all view that tells us what’s going on in the current sprint. For this, we can use the opensprints() function. I like to pair this with a project key query so that I only get the open sprint for my project, such as:

Project = "Project Name" and Sprint in openSprints()
Pardon the blur…I actually don’t have any open Sprints in my test instance, so I had to use my work instance.

A note on openSprints() is that it only supports the operators IN and NOT IN. Using an equal sign will cause the query to return an error.

There is also the operators futureSprints() and closedSprints(). However, these return ALL future and closed sprints, respectively, so further filtering will be needed to focus on any particular sprint (such as Sprint = “Name”)

So, let us say you are in a situation where you care more about what has and hasn’t been released than what is in any particular sprint. Then you’ll be interested in the functions latestReleasedVersions() and earliestUnreleasedVersions(). These operate on the fields AffectedVersion, FixVersion, as well as any custom fields with type Version. Let’s say you want to know what bug-fixes have gone out the door:

issuetype = Bug and fixVersion = latestReleasedVersion()

The neat trick about this query is that it will automatically update if your team releases a new version, meaning if you use it in a dashboard, it will always show the bugs fixed in the last released version.

Filtering out sub-tasks

So this one comes up often. A manager wants a dashboard, but they don’t want to get bogged down in the minutia of the sub-tasks. This a handy trick I use to filter out all sub-tasks from a query. I simply and the existing query with this clause:

issuetype not in subtaskIssueTypes()

It’s a simple thing to add but is an easy way to filter out all sub-tasks. Because of how it works, if you add any new sub-task types to the project in the future, the filter will still work without needing to be updated.

Cleaning up watched issues

So, JIRA in its current form sends out A LOT of emails.

Just a *few* emails…

Atlassian has heard our cries, and they are working on it, but in the meantime – this is where we are. And as an Admin, you will be touching a lot of issues – which will put you on their watchers list automatically. So I find it helpful to run this JQL and clean things up every few months.

watcher = currentuser() and project not in ("Home Project A", "Home Project B", ...)

This gives me a list of all issues I’m watching on an instance that is outside of what I consider to be my “Home Projects” I use to organize my work. From there you can run a bulk change on the list, and on Step 2, choose the “Stop Watching Issues” operation. I don’t know if this honestly counts as a JQL trick, but it has helped keep me sane many a time.

And that’s all for this time!

Did you learn anything new? What are some of your favorite JQL tricks that I didn’t cover? If I get enough I’ll do a part 2 to this!

So it’s been a busy time for me. I decided to take advantage of the current time I have to study for my next ACP exam. It’s scheduled for May 8th, which doesn’t give me too much study time left. But hopefully, I’ll be sending out good news the following week!

My Wife and I also have been working on 3D printing face-shields for first responders – you can read about our efforts (okay, mostly her efforts) on her blog here: https://cadstories.com/2020/04/17/engineering-and-covid-19/

I also have a twitter account for the blog now! If that’s your preferred source of Social Media, give me a follow! I’ll be posting blog posts there, as well as anything else Atlassian related I find interesting! https://twitter.com/theJIRAguy You can also sign up here to get an email every time I publish a new blog post! Simply use the sign-up form at the bottom of this post!

Until next time though, this is Rodney, asking “Have you updated your JIRA Issues today?”

Seven JIRA Gotchas

So, let me start with the fact it’s good to be back on a normal schedule. I love writing for you guys, but last week reminded me why I only do so once a week. But…worth it. I heard from so many of you about how much you appreciated the recap. Also – this happened.

Yeah – we got noticed!

So between your enthusiasm, likes and comments on LinkedIn and Twitter, Atlassian’s tweet, and word of mouth spreading, we have overtwo-thirds many views in the first week of April than we had in the entire month of March.

Today’s topic was actually recommended by a team-mate and colleague at Coyote Creek, Olena McMurtrey. The thought is, what are the gotchas you need to look out for when doing various tasks. I’ve outlined seven that even after all my years, I’ll still occasionally trip over. Where possible, I’ll explain how to prevent falling into the traps, and how to fix it if you’ve already fell for them.

1. Internal Directory User Required

So, your IT organization is making some changes to Active Directory, and this will require you to adjust some settings. You go into JIRA with your System Administrator account, navigate to User Directories, and find….

There are no options to edit the AD Directory (or LDAP in my case…still). This is because you have just fallen for our first Gotcha. JIRA does not allow you to modify a directory if the account you are logged in with is a part of that directory. This is to prevent a situation where you modify a directory and manage to lock yourself and your entire company out of JIRA.

For this reason it is often considered best practice to maintain at least one system administrator level account within the JIRA Internal Directory – and to use that when making these changes.

So lets say you didn’t do that. Well, it’s an easy enough fix. Go to User management > Users, then click “Create User” in the upper-right hand corner.

From here, create yourself a user within the JIRA internal Directory, then add it to the jira-administrators group to give it permissions. Log into this account and use that to change the settings.

2. Did you update the field context?

So, you have a team request a new field. Looking into JIRA, you see that field already exists on another project in your instance. Sweet! Less work for you, right? You just slap that bad boy onto the requesting team’s screens and call it a day…

That is until you get an email saying that field is still not showing up for them. You have just fallen for our second gotcha. All fields have a Field Context that you can use to limit what projects that field is allowed to be used on.

To test if this was a case, go to the project, then click “Create” (YOU HAVE TO GO TO THE PROJECT FIRST). Once your create screen is up, click “Configure Fields” then “Where is my field?”

From here, type in your field name and click on it from the drop-down, and you’ll get a quick diagnostic on what’s preventing it from showing up.

As you can see, the bottom one is the offending item – which is your field context. However, this screen will even give you the link to where you need to go to adjust it. To fix this, click that link, then on the next page click “Edit Configuration”

From here, make sure your issue types and project context are appropriately set, then click Modify. You will need to reindex after this, but the field should now be accessible!

3. Updating your URL

So, for whatever reason you need to adjust your URL to JIRA. You do all the work on cut-over day. You have updated the base URL, DNS entries, and even setup Apache to redirect from your old URL to your new one. You get done, run one last test load of JIRA when you see….

Yep. That’s our third gotcha. Any time you change JIRA’s URL, you will need to change the proxy settings in conf/server.xml and restart JIRA. Specifically, the setting “proxyName” listed here:

Additionally, if you are changing the directory you access it from (lets stay from /jira to just /), you will need to update this path setting too.

Do that, restart JIRA and that error message should disappear!

4. App Compatibility

So, picture this. You’ve just completed a massive JIRA Upgrade, and everything appears to be running smoothly. However, come the next work day, you get reports that some features appear to be outright missing. Investigating, you look at your apps to find the following messages.

Image courtesy Atlassian.

Congratulations, you just fell for another gotcha. Your apps are not guaranteed to work version to version, so you need to check each time you plan an upgrade. If you are fortunate, the vendor has already released an updated version that works with your new version of JIRA, and you can just update it straight away.

However, sometimes (like in the case of the “Copy Space Plugin” here), there is no updated version available. You have few choices here.

  • First, you can try to request an update. Not every app is still supported though, so you may not get your update.
  • Second, you can try to find another app in the Marketplace that replaces the functionality. Again, this may also be a dead end.
  • Third, just decide that functionality was a nice to have, but not important, and go on with your life. Your users may disagree though – so be warned.

I suppose there is another option here if you know how to program, but I don’t expect all JIRA Admins to become Java Developers to, so this probably won’t be everyone.

5. Role based Permissions

So, you get a ticket in saying a user can’t work inside a project. No problem, you add them to the Developers role and call it done. Then the user reopens the ticket saying that they still can’t do everything they need to do.

Congratulations, you’ve just fallen for a classic gotcha. Depending on the permission scheme, the Project Roles may map out differently than in an unmodified JIRA. That’s why it’s always important to know what kind of permissions your users need and how they map to the project roles.

Seriously, I’ve used this exact scenario in interviews for JIRA Admins before. You’d be surprised how many fall for it.

6. Did you set a resolution?

So, you get another ticket from one of your Service Desk teams. No matter what they do, they can’t get a ticket to clear out of their queue. You look, and all the tickets appear closed, but there they are, still in the queue.

You’ll find this in teams that have customized their workflow. They forgot to do one of two things: Either auto-set a resolution or put a screen up to allow the user to select a resolution. JIRA Does not consider any issue truly done until that field is set. And that’s for both Software and Service Desk. But you will see this more from Service Desk teams, as it impacts them to a greater degree.

The main takeaway here is to always double check you are giving a user a way to set the resolution in every workflow you make for them.

7. Auto-assignment Roulette

So our last gotcha may not be your fault, but your users. Lets imagine this scenario:

You get an email from a project lead. They are actively using components in their project, with the components having their default assignee be their component lead.

However, they like to add multiple components per story, and no matter what they do they can’t seem to make the components auto-assign in a consistent way.

As you may have guessed, they have just fallen for the for the last gotcha. When using multiple components on a story, JIRA Will use the default assignment of the first component added to the list. So, if your teams are adding them in a different order every time, they will get a seemingly random default assignee every time. This one is easy enough to fix – just instruct your users of this fact and tell them to get a consistent result, order matters.

And those are some Gotchas to watch out for!

Is one of these surprise you? Do you have a gotcha that I didn’t cover? Leave it in a comment!

This topic was recommended by a colleague, but I’m always looking for reader requests for posts. If you have a topic you’d like me to cover, let me know!

Job Seeker Profile

So, as you know, I really started posting to this blog regularly when I myself was in the job market. So every now and again I like to feature those JIRA Admins who find themselves in a similar situation. And I have another one for you to look at.

His name is Akuthota Venkatesh, and he is based out of Hyderabad, Telangana, India. He has been a JIRA Admin in one form or another since June 2011. However, he has recently lost his job due to the world-wide slowdown brought on by the Corona Virus.

Some of the projects he’s worked on include integrating JIRA to Rally and Salesforce, migrating data to and from JIRA, as well as the normal spread of Application upgrades and dealing with stakeholders.

If you are looking for a JIRA Admin and think you might be able to help him, please download his CV and reach out to him!

And in other news

So, I was caught a bit off guard last week. As part of Atlassian posting about the blog, they asked if I had a twitter handle they could @mention. Well, I do, but it was mostly used for sweepstakes entries – not the impression I wanted to make. So since then, I have fixed that. You can find The JIRA Guy on Twitter here: https://twitter.com/theJIRAguy

I intend to post new articles and posts to twitter about our Atlassian powered lives that I find interesting, as well as new posts from the blog! So be sure to give it a follow. You can also subscribe directly to the blog to get new posts directly to your inbox! To do so, just use the form at the bottom of the post.

So, I’ve got some interesting posts planned over the next few weeks – even some that are not JIRA related! *gasp* So I hope you will check back in to see what we’ve got. But until then, my name is Rodney, asking “Have you updated your JIRA issues today?”

Its all about them schemes

Last week we took a look at cleaning up your fields to help improve your system performance. So now we move on to our next Atlassian TAM suggested topic: How to standardize workflows and screens. But before we dive in…

Last week was amazing. I had a lot of engagement on this topic, and even had two more people come forward with requests for more topics to write about. I’m always up to do a reader suggested topic, so if you have something you want to see covered, let me know! It may be a few weeks before I can write on your topic (For once, it’s nice to have a backlog!), but I’ll definitely get to it. You can use the contact form on the blog’s website, or email me directly at contact@thejiraguy.com.

Anyways, lets get into it.

So, let me ask: Why would you want to standardize?

Fair question. This is going to be a lot of work, negotiation, and may come down to you telling them this is how it’s going to be. Why bother?

What’s the benefit that will make all this worthwhile? Well, the first benefit it helps teams within your organization work better together. If a person has to move from Team A to Team 1, they don’t have to relearn how to use JIRA. They’ll see the same fields and the same workflows. They’ll know how to use the tool as they’ve always have and be able to focus on getting to work that much quicker.

Secondly, It will help with reporting. With all the teams using the same workflows and fields, we have a consistent set of data across all teams with which to run reports and measure performance. While you should NEVER compare teams’ velocities to each other (as each team will value story points differently, therefore you will never get an oranges-to-oranges comparison), you can measure most other things consistently across the teams if they are all working from the same field set.

And the third, and I’d argue the most important reason to consider standardizing your schemes is your sanity. Look, there is only so much JIRA Admin to go around. If you are lucky, your organization has two or three of them to help out – but that’s usually for the largest organizations. Most small and medium businesses, it’s on you. Having standardize schemes stops you from having to play “What impact will this change have?”. You’ll know, and be able to get on with it that much faster.

But what about the exceptions?

Look. There will always be exceptions. A process or team that doesn’t adhere to normal business models. My Approvals workflow is such an example. But it’s your job to make it just that: An Exception. If you standardize your workflow, any change that is proposed by a team using the standard schemes should be measured on “How will this benefit the entire group?”. If it’s a benefit, it should be applied to the entire scheme. No breaking off a scheme to apply to a specific team. It’s all or none.

This, in and off itself, can help limit JIRA bloat. If a team has to defend why their change will benefit everyone, not just themselves, they will sometimes decide it’s not worth it.

So, where do I get started?

Honestly, I’d start with the screen schemes. And the first step is to talk with each and every one of your teams. Figure out what fields each one is using, and how they are using them. This will be time consuming – just a fair warning. But to do this successfully requires you to put in the work and really get to know your teams and how they work within JIRA.

Once you have that, sit down and compare your notes from all the teams. (You did take notes, right?) Find similarities, and try to get down to the fields you can get away with to allow as many teams to work as they already do as possible. Try to avoid fields that are used by only one team – opting instead to put that information in another field if possible, or the description. Take this time to use your test instance to make the changes. This will be both your testing and give you a way to demonstrate the change in a real way in our next step.

Now that you have your proposed standardized fields, this next step is probably the most important. Go back to each team, and present the new scheme proposal to them.

No, Seriously. Review what changes will impact their team specifically, and explain why you are making each change. Get feedback. Listen. Negotiate. Being willing to work with each team on this process is how you build buy-in, and it’s how you will make this process a success. Not everyone will be a 100% happy, and you may have to add some fields back in, but you can get them to a point where they can at least agree to the change. Again, I know this will take time, but having gone through this process myself, it is absolutely required.

So, you have con-census and buy in from your teams, and you have the changes ready to go in your test instance. You are now ready to push this to production..which in vanilla JIRA isn’t going to be fun. Without any plugins, you will need to manually recreate the new schemes in your production JIRA Instance, then assign all the appropriate projects to it. Or – you can use a tool like Botron’s Configuration Manager to copy over the new config as it exists from your test to production. Either way, I usually schedule a full change window for this during a relative down period for the instance (which invariably ends up being Saturday). No need having people try to work around you as you make these changes.

And that’s your Screen schemes standardized! When you are meeting with the teams, they may not understand why you are doing this. Stick with it – they will see the benefits eventually, and wonder why it wasn’t always like that.

But…uh…what about workflows?

Are you Forgetting something? Maybe you are,
Maybe you aren't - Are you Forgetting something? Maybe you are,
Maybe you aren't  Scumbag Brain

Yeah…workflows. I hadn’t forgotten about it – I was strategically putting it off. I promise!

My experience has been standardizing workflows is a bigger fight than screen schemes, which is why I chose to lead with that. People are willing to be flexible on fields – most people don’t use most custom fields anyways. But when you start messing with their workflows, you are messing with how they work. And yeah – that is part your job – but it’s going to be a fight. So start out with the screen schemes and build up some good will. That will make this easier.

With the Workflow schemes at least, you have the information at hand for each team. Teams are somewhat free-form in how they use fields, so you have to interview them first. But a workflow can only be used as designed. So start by looking at all the workflows for the projects you wish to standardize. Figure out the common points, and design a workflow (in your test instance!) that matches as many of them as possible.

Now that you have something designed, take it to those same teams. Explain to them what you hope to achieve, and demonstrate how the standard workflow you are proposing differs from the one that particular team is currently using. Go through the same thing with each team you did with the fields. Explain the benefits of everyone working on the same scheme. The object here is to build con-census from the ground up.

Look, I’m not going to sugarcoat it. People are going to disagree with you. Some are even going to do so passionately. Don’t make enemies of these people. If they care about how JIRA is being used THAT much, they are a person to have on your side. Figure out what their objections are, and figure out a way to address or otherwise work-around those objections. But show you are willing to work with their concerns, and they will be more willing to work with you.

Once you have all the concerns addressed and con-census built, schedule a change window and put it in place. You can do it manually, or by a plugin. Honestly, I keep harping on the plugin as an option as it eliminates some possibilities of you making a mistake. But if you are going to do it manually, at least export the workflow and re-import it into production, so you don’t have to worry about a mistake there.

And that’s it!

Look, this won’t be the easiest thing to do as a JIRA admin. People can be very passionate about how they work, and if you are seen as messing with that, they can get upset. But remember that they are upset because they do care, so if you can show that you are willing to listen and work with them, it should help.

So I know everything else in the world has been going a little crazy right now. I really hope you all survived the transition and ramping up your teams to work remotely. I’ve been stuck at home anyways while I recover from the surgery, but even before that I’ve been a remote employee for some time, so it still business as normal for me. I can write a post on tips and tricks from working from home, but it’s all likely advice you’ve heard elsewhere already, so unless requested I don’t have a plan to write on that topic. But if you are interested, let me know!

Don’t forget you can subscribe to receive new posts from this blog via email! To subscribe, just put your email in the form below this post! We also have an officially Unofficial Atlassian Discord chat. Swing in, ask questions, and see what’s going on! https://discord.gg/mXuRsVu

But until next time, this is Rodney, asking “Have you updated your JIRA issues today?”

Spring Cleaning for Custom Fields

Well, I’m back…or at least upright. Thank you all for the well wishes I’ve received. As much as I hate not posting an actual article, I really did need the week off to recover. But now it’s time to get back to it. As I’ve stated, this week’s article was going to be a reader selected topic. It seems there was some demand for each, but the clear winner was…

I’ll be tackling the other articles in the coming weeks, but lets look at Getting those custom fields under control this week.

A bit of a confession here.

First, let me say that as Admins, I think each one of us has various strengths and weaknesses. Me, for example. My obvious strength is Systems. I can tune almost any system to run smoothly. I can learn new back-end technologies with almost no lead-time and still successfully deploy.

However, my weakness as a JIRA Admin is I’ve never been able to execute a successful Cleanup. I’ve known the problem, and I’ve worked to fix it. But I’d rather be a con-census builder than a dictator when it comes to JIRA, and people LOVE their custom fields.

To manage this, I’ve always put safeguards to keep things getting worse. I re-use fields rather than create new ones, and really analyze whether new fields are needed. But on systems I’ve taken over, it’s rare I get a group to give up a field that was already existing.

That being said, if you want to learn from a real master of this topic, I’d highly recommend Rachel Wright’s JIRA Strategy Admin Workbook.

However, I’m going to give you tools to analyze the problem, see what fields aren’t being used, and give you the data to say to people “This…this is a problem we can fix.” Then I’ll show you how to go about cleaning things up. So, lets learn together!

Why does this even matter?

So, you might be asking yourself “JIRA is all about the custom fields, why should I worry about how many there are?” That, honestly, is a fair question. But the reason you should care is performance. It’s just a fact: The more custom fields your system has to parse through, the slower things like creating an issue, searching, etc will be.

Average response times per action (in milliseconds): custom fields test. Courtesy Atlassian.

Take a look at this chart above. This is from some testing Atlassian did. If we look at JQL Searching alone, at 1400 custom fields, the performance is abysmal at almost 3 seconds. However, doubling the custom field count to 2800 triples that number to nearly 9 seconds.

And yes, this is an extreme case. But users will notice a delay of half a second. You will get comments saying JIRA is slow even at that number, trust me. And it doesn’t take too terribly many custom fields to get searching to be that slow. That is why your custom field count matters.

Analysis

It is said the first step to fixing a problem is admitting you have a problem. Check. The second step it would seem is to figure out how big the problem is. Unfortunately, for this part we are going to have to go directly to the database.

For all my databases, I like to keep an administrative read-only account that will allow me to go in and query from time to time. This helps with making sure things are running properly, or diagnosing problems every now and again. Just make sure it’s read only so you don’t mess with something that aught not to be messed with.

Anyways, Atlassian has some queries that will help you figure out what fields are being used in your system. You can find all of them available here:

All the queries below I have confirmed are still working for MySQL 5.7 and JIRA 8.5.3.

Unused custom fields

select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfieldvalue.stringvalue is null 
and customfieldvalue.numbervalue is null 
and customfieldvalue.textvalue is null
and customfieldvalue.datevalue is null
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.servicedesk%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.pyxis.greenhopper%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.bonfire%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.jpo%'
group by customfield.id, customfield.cfname, customfield.description;

So, the first logical question is to ask is what fields are just not being used – period. These can probably be thrown out easily enough. Just a word of caution with this query: It can return fields that are being used by Apps using their own datastore. I’ve modified it to at least throw out results from JIRA Service Desk, JIRA Software, Capture, and Portfolio So make sure you are vetting this list for such false postitives. But if it’s a field you or your team has created, and it’s on this list, it might be time to have a discussion on whether it needs to be around.

Custom fields that have not been updated after date (YYYY-MM-DD)

select field.id, field.cfname from customfield field where field.cfname not in (
select item.field from changeitem item
JOIN changegroup cgroup ON item.groupid=cgroup.id
where item.fieldtype='custom' and cgroup.created > 'YYYY-MM-DD'
) and customfieldtypekey not like '%com.pyxis.greenhopper%'
and customfieldtypekey not like '%com.atlassian.servicedesk%'
and customfieldtypekey not like '%com.atlassian.bonfire%'
and customfieldtypekey not like '%com.atlassian.jpo%'

So this one returns fields that haven’t been used after a date. You’ll have to modify the query by replaying “YYYY-MM-DD” with a date you are interested in, but this one is handy for an end of year review, as it can answer “What fields have we not used this year”. If your teams haven’t touched the field in that long, you have a strong case for it’s need to go away.

Custom fields with low usage

select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.servicedesk%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.pyxis.greenhopper%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.bonfire%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.jpo%'
and customfieldvalue.ID is not NULL
group by customfield.id having count(*) < 5
order by count(*) desc

Unlike the first query, this one will return fields that do not meet a certain threshold of usage. As written, this threshold is 5 issues, but you can adjust it by changing the number in “count(*) < 5”. As a rule, I like to keep this to one-tenth of one percent of the total number of issues in your instance.. So if you have 400,000 issues in your instance, the threshold would be 400. But that is a personal preference, you should set this wherever it makes sense for you.

So, what now?

You know have your information, and you know what fields are not being used. You can just go and delete them, right?

Well…

Not really. You should do some more digging. Who asked for that field to be added? What was their business case? Was that field just recently added? For those with a small usage, are they only being used in a specific project? If so, what is the usage within that project?

Also, use Botron Power Admin to see where all the field is being used (or not used). This might tell you who you need to talk to as well.

This should help you narrow down your candidates further. Once you do that, go back to whoever requested those fields. Take the data from your queries, and let them know that the fields aren’t being used, and are on a list to be removed. You can also include documents on how custom fields impact performance for everyone. If you are lucky, they’ll agree with the data and you can remove it. If not, start negotiating. See why they think they need a field they aren’t using. See if there is a way you can give them their ask without the field.

Custom Field optimizer (Data Center)

For those of you on Data Center, JIRA has a built-in Custom field optimizer you can use. It will go through and find fields that are not used often, or otherwise “misconfigured” (As defined by Atlassian).

As you can see, it’s alerting on a field in my instance that has a global context. This was one I specifically created to test the optimizer, so it’s Ideally, you should limit your custom fields to only be available to the projects that need it. So you can use this tool to find those with global contexts and if necessary, fix them.

Merging Fields (App Required)

So, lets say you have a duplicate field. How do you copy the information from one to another so you can delete one without losing data?

Well, as you can guess, this isn’t something that is able to be done in vanilla JIRA – so we have to turn to the Atlassian Marketplace – and specifically Adaptavist’s Scriptrunner plugin.

Now my groovy – it needs some work. But Scriptrunner has a number of built in scripts, including one to “Copy custom field values”. This does what it says on the tin. It takes the value in a source field, and places it into a destination field, overwriting what was there. For most cases, this won’t be a problem for reasons I’ll talk about later, but do keep this in mind.

To use this, you will first need to save a filter that will limit what issues it will copy the fields on. I use something along the lines of:

"Custom Field A" is not EMPTY

This will return all issues that has the custom field set. You can add another clause to weed out those that have the destination field already set, and handle combining those manually. In most cases, each of the duplicate fields would have only been used on a project or two a piece, and have no overlap, so this shouldn’t be too much of a concern, but as always do your research first and make sure that is not the case in your situation.

Now, I shouldn’t have to say this, but always test any changes you want to make before doing it in production. That’s very true here. This built-in script isn’t perfect, and you may have to pick it apart and modify it a bit to get to your use case. The only way to know if this is required is to do your testing.

That being said, I asked my colleagues at Coyote Creek about merging fields, and Neil Taylor gave me this as a solutions he’s used before. From testing it, it’s the easiest to implement and execute that I’ve found, and should handle most of your use cases.

So time to get cleaning!

So you know how to find fields that are good candidates to removal, and have an action plan to migrate the data should they have anything worth keeping in them. What are you waiting for? Get out there, start conversations, and get yourself on a better path to better JIRA Performance.

Don’t forget to subscribe to receive new posts by email. It’s the easiest way to be notified as soon as the posts go live. You can sign up with the form at the bottom of the post. And don’t forget to check out our Atlassian Discord Chat!It’s always interesting to see who has popped up there and what’s being discussed. https://discord.gg/mXuRsVu

But until next time, this is Rodney, asking “Have you updated your JIRA issues today?”

A JIRA Guy’s New Years Resolutions

HAPPY NEW YEARS! I hope you had a good 2019 and look forward to a great 2020!

As you know, 2019 was at times….rocky, but all in all it wasn’t terrible. This blog has been one of the bright spots, actually! Hearing from readers out there about how much they are getting from the blog really just makes my day every time!

Where I live, it is a tradition that we resolve to improve at least one aspect about ourselves over the new year. So, that got me thinking, as JIRA Admins, what can we resolve to improve about how we work and how we run our instances. So without further adieu, here we go!

I resolve to not make any config changes without notifying all users first.

This one is easier said than done. But this one will save you so much headache in avoiding unexpected impacts from a change.

The challenge here is there are a lot of changes that you think will have no impact on user experience, and it’s tempting to just rush in and change it. Especially when explaining what you are changing and why feels like writing a light novel. I know, I’ve been guilty here too!

But giving yourself a chance to pause to explain what you are doing and why you are doing it will give both you and your users some perspective. If you are having trouble stating why you are making a change in a way that normal users can understand, do you really understand the change you are about to make? And if that’s the case, why are you making a change you don’t fully understand?

Now another thing here is this requires some lead time. If you send out a rushed email than immediately make a change, you haven’t really notified anyone. It’s the giving it time for users to digest the information and send in the feedback that will show the true power of this resolution.

I resolve to clean up Custom Fields from JIRA.

I see this one all too often. JIRA went through a “wild west” period where either a) all user requests for new fields were automatically stamped “Approved”, or b) everyone had admin rights and just added fields without a care in the world. It’s nothing to be ashamed off, but it is something you should resolve to fix, as an over-abundance of custom fields can cause system performance problems.

Now I’m not going to lie, this is a political quagmire if ever there were one. Users LOVE their custom fields, especially Project Managers. So if it were me, I’d start with the low hanging fruit. In this case, that would be duplicate fields. This one is easy for users to see why it can be a problem. Try doing a JQL search on a field that shares it’s name with another. Which field is which? Which has the data you need? Who knows!?

This does lead to the problem of what to do with the information contained in whichever copy you are getting rid of. Not going to lie, that is not an easy question to answer in Vanilla JIRA, and rightfully deserves a post all it’s own. However, the ever helpful folks at Atlassian Community has a few pointers to get you started.

If you manage to clean up those fields, then comes the next easiest category: Those that are “close” to each other in purpose or meaning. For example, lets say you have the following list of fields:

  • Dev Customer
  • Customer
  • Customer (Accounting)
  • UX Customer
  • Customer Number
  • Customer ID

You now have six fields, which all contain essentially the same kind of information. In an ideal case, any custom field you create should be “generic” – that is to say it should be general enough you can drop it in any project where it’s needed and it makes sense. In this case, I’d work with five of these groups and migrate them all to use the “Customer” field. It’s generic enough that the meaning can be used for all of these applications, and gets rid of five fields in one go.

I resolve to gain and/or regain control of my JIRA instance.

So this one isn’t as universally applicable, but for those of you dealing with it, it can be a hassle. You do the work, get things cleaned up in JIRA and the system working smoothly, just to have some guy – who lets be honest should NOT have JIRA Admin rights – come in, create 20 new projects with 40 new fields.

It’s a story that does happen. That is why you should strive to regain control over your instance, and be the gate-keeper for any such changes. Do you really need 20 projects, or because they are all short lived, could you do with one and utilize Versions/Releases to organize that work?

Now, I’m not going to say this will be easy. Anybody with JIRA Admin rights that aren’t you might have some clout – or at least the ability to complain very loudly to the right people. You will need to defend your decision to take away their rights by showing how the changes they are making are not following best practices, and are actually having a detrimental affect on the instance. But remember, YOU are the JIRA Admin. The health of the instance is ultimately your job, so state that clearly. And remind them that you are open to requests, but you will guide the discussion to something that is better for both the users and the platform.

I resolve to make myself as much of a priority as my systems.

I get it. Your teams and users depend on you to keep the system running and updated. You know what makes it easier to depend on you? Not burning out.

I’ll see it on reddit from time to time where a Sysadmin has passed away from a heart attack, or otherwise burned out and is switching careers. They are always the type who hardly ever took vacation, never took personal days, and felt like the weight of the company is on their shoulders. I’m telling you now – don’t be that guy. I mean I play around with these systems for fun in my spare time, and I still have to unplug every once and a while!

So if this sounds like you, just relax. And if you are at a company that would fire you for taking a few days off every now and again, you’re at the wrong company! Every company should be concerned about burn out, and if they aren’t that isn’t a healthy environment, and you should start looking now for a better year ahead.

Again, Happy New Years!

So, what are your JIRA Admin Resolutions? Are you doing one of the ones I listed here, or are you doing something different! Please let me know. Next week I’ll be reviewing a app (or plugin for us curmudgeons) that literally blew my mind when I saw what it could do. But until then, I hope you have a great start to 2020! I’m Rodney, asking, “Have you updated your JIRA Issues today tomorrow?” Happy New Years Everyone!

So, you are now a JIRA Admin, now what?

So, you’ve just been handed a box and told “This is yours now.” Or maybe you just landed your first job as a JIRA Admin. And maybe you start to feel that panic when you realize you don’t even know where to begin…

First, take a deep breath, everything will be alright. You got this. In the words of Douglas Adams:

This week we are going to focus on what you should do leading up to and during your first week as a JIRA Admin, with an eye on setting yourself up for future success.

The Lead up

Okay, so I know some of you don’t get this luxury. As I stated earlier, you didn’t know you were about to be a JIRA Admin. You just got handed a box and told “Here you go” with no further preamble or instructions. If this is you, you can go ahead to the next section. However, if you are fortunate enough to have interviewed for the position, this is for you.

I typically start during the actual interview for the job. While a job interview is still about convincing the hiring manager that you are right for a job, it is also as much about seeing if the job is right for you. As standard operating procedure, I always have a list of questions printed out to ask. For a JIRA Admin role, they often look like:

  • Is your instance cloud, server, or data center?
  • If Server: Is your instance bare metal or on a VM?
  • How many teams and users does it support?
  • How many projects and issues are on it?
  • What is your governing policies around JIRA?
  • What is the state of your end-user documentation on how to use JIRA?
  • What Apps (plugins) do you run?
  • Am I replacing another JIRA Admin?
  • Will I be the only JIRA Admin on staff?
  • What is your biggest pain point with your current JIRA usage?

As you can see, these are questions designed to get to the heart of what your first tasks should be coming into an organization, by gauging where an organization is with it’s JIRA instance, and what problem spots there could be.

Don’t sit on this information though. If you get the position, the answers to these questions can help you do some research before you start that will give you a head start on day one. If there biggest pain point is JIRA is slow, you can research on causes of lag in JIRA and have a plan ready to diagnose and resolve them. Or if their biggest pain point is no one has visibility into anything anyone is doing, you can have some end-user docs on JQL searching and Dashboards prepared to copy/paste into their Knowledge Base (cough cough Confluence cough). The goal is if you have some warning, take the time to become prepared.

You’re First week

Day One

So, you’re sitting in front of your computer. You’ve finished all the HR stuff, tours, introductions, and general pageantry that always goes with a first day on the job. Now what?

I’m going to be honest, some of this is going to assume a few things. First, that you are the only JIRA Admin that is managing the instances you are working with. Second, that things have not been managed otherwise. Sometimes you get lucky and these are not the case. More often than not though, this is what you will be walking into.

First thing you should do is take stock. Check that you have admin rights to every application you are managing. Make sure you can get into every node of every system under your management. That includes back-end systems like databases and file shares. If those systems are managed by another department, make sure you get introduced to an actual person who can help you with those systems. And for the love of all that is good, if there are no documents for all of this, take your time and write some for every system.

Second thing I do day one is start to figure out who is actually using the instance. Start with the following JQL search:

ORDER BY updated DESC

This will give you the issues with the most recent modifications within your instance. Take note of what projects these issues belong to, and who is making these modifications. This will give you an idea of who is using the instance actively. Depending on how the information is flowing, you might be able to see evidence of bulk edits going on. Take note of who is doing this – as anyone knowledgeable enough to do a bulk edit is likely a power user, and someone you want on your side.

If you have time, find out what teams are using the active projects and make plans to meet with those teams this week. If they are active within JIRA, they may have insight into pain points that Management might not be aware of. This will also show that you are an admin that cares about who they are and how well they can do their work – and you cannot pay for that kind of goodwill.

Day Two

Day two is about taking control. Figure out who else has admin rights on your instance. Do they require admin rights? If no, how politically sensitive is it going to be to take those rights away? In an ideal world, only people on your team would have admin rights. You shouldn’t depend on being the only Admin (unless you absolutely love being called while on Vacation), but everyone shouldn’t be an admin on the same time. I find giving the team you are working on Admin access is usually a good balance there.

However, we don’t live in an ideal world. Unless you happen to be a VP, it’s going to be politically difficult to tell another VP that you are taking away their admin rights. However, here is a trick. Figure out what exactly they were doing that required the elevated access. Is this something reasonable? Is it something that has a permission that you don’t mind being outside your control? If so, create a special group with access to that specific permission plus normal user access, and give it to the VP’s. That way you can be seen as enabling work they feel the need to do while not giving away the keys to the kingdom.

If it is something that you should feel you need to be able to manage, stay calm. Explain your concerns over what this access could mean long term. The classic example is the ability to create projects. JIRA, by default, will create new permission schemes, workflows, etc whenever a new project is created. However, this will lead to system bloat and eventually slow down the system. Explain that the VP will be able to request new projects from you, but you need to be the one creating them in order to set them to a default set of schemes and remove the bloat. Most reasonable people will be fine with this so long as they still have an avenue to get it done.

Day Three

Now that you know that you won’t have someone coming up behind you to undo changes you are putting in place, start looking at the instance in further detail. How many permission schemes are there? How many workflows, custom fields, etc? How many of those are going unused? Start making a plan for what to clean up.

I usually start with the low hanging fruit of unused workflows. These still count as far as JIRA is concerned, so cleaning them up will help JIRA perform better. And because they are unused, most users will not notice that they are gone.

My next target is usually permission schemes. Unlike unused workflows, you will not be able to clean these up today, but you can at least start strategizing and getting a conversation going. Typically, you can break down permission schemes into four general categories:

  • Everyone can see, modify, work on, and create issues
  • Everyone can see and create issues, but only the team can modify and work on issues
  • Everyone can see the issues, but only the team can create, modify, and work on issues
  • Only the team can see, create, modify, and work on the issues.

Exactly which permission nodes map onto these for broad generalized schemes will vary from org to org, but in practice I find that these four are enough to cover most use cases in JIRA. So my plan is to usually set these four up to use Project Roles so I can reuse them on as many projects as I can. I’ll then start working down the project list to adjust them as needed. You will need buy-in from the Project Manager for each one, but if you write a document explaining in detail exactly what the permission schemes are, and how this would empower them to manage their own teams access by using project roles. Once you explain all the benefits, buy-in will almost always happen.

Day Four

On Day Four, You will usually have a good bit of dialogue going at this point. You’ll probably spend this day working through incomplete tasks from the previous days and continuing on with team meetings. However, as this is going, you should be taking notes as to what projects, if any, are being unused. Revisit the JQL from Day One, and instead of looking at the active projects, start weeding out the projects that haven’t been updated in at least a month.

However, don’t assume just because they aren’t used that they are safe for archival. Track down whoever is/was responsible for that project. Ask them why the project is going unused? Did it not meet their needs? If so, how? Was that project completed? Or did a re-org make it obsolete? Figure out why each one isn’t being used, and come up with an appropriate answer for each. A lot are likely to be archive-able, but some just may need some user education and tweaking by an actual JIRA Admin to get it back into use.

Sometimes while doing this, you’ll run into the “JIRA Sucks” people; that is people who hate the platform for one reason or another. It is amazing how many people draw this conclusion due to a misconfigured JIRA instance.

I deal with these people by agreeing with them that JIRA can suck. No really, nothing wrong foots a person and brings down there defenses like the JIRA Admin – a guy who’s career is based of managing JIRA – agreeing with them that JIRA can suck when configured wrong. But this can get the conversation started about exactly what they didn’t like about it. You can then explain that when managed by someone who knows what they are doing, most – if not all – of those problems can be solved, and then explain how you plan to fix it. Show these people that you have a plan and are willing to put in work to make their experience better, and they will likely give you a chance.

Day Five

So, Here you are, five days in. You are meeting key players, getting to know your teams, and coming up with a plan to meet your user’s needs. You will still likely to be having a lot of meeting and introductions to people, but take some time today to prepare for eventualities.

By this, I am saying you should have a run-book available to your team. A run-book is a document that can tell people who are not 100% familiar with a system what to do in the event of downtime. It should contain the following:

  • What checks to perform to be sure JIRA is down rather than a network problem
  • Information on all services that support JIRA (i.e. Database, File Share, Proxy, etc), including how to check that those are working correctly and are reachable from the node.
  • Basic troubleshooting steps to try if JIRA really is down before calling you.
  • Backup Procedures and Location of backups
  • Logs to capture if a problem is detected
  • Contact information should things need to be escalated to you.

A run-book won’t prevent every call to you on your off-time, but it’s at least something to help whoever is on-call do something to attempt to resolve a problem before you are called in.

And You’ve survived!

Now, you won’t have every item on the list above completed, but you will have gotten the ball rolling. After all, “A journey of 1000 miles begins with but one step.” You’ll have set yourself up for a good tenure within your org, while establishing yourself as the point of contact for JIRA problems. So take a break, enjoy your weekend, and rest up while you can. Now that people know that you are willing and able to help them with JIRA, your work is only just starting.

And with that, I’ll catch you next week! Until then, this is Rodney, asking “Have you updated your JIRA issues today?”

Hitchhiker’s Guide to Other Team’s JIRA Projects

Introduction

So you’ve got your team project configured to a fare-the-well, and your team is running like a well oiled machine, and you are able to see it.  But then, the worst thing in the world happens.  A task comes up, and you are wholly dependent on an outside team to complete it before you can proceed.  How do you track this? How do you account for this?

This truly nightmarish scenario is what we’ll deal with today.  The fact is, this situation is actually fairly common.  No one team can do everything on their own, which means to some degree, we are all interdependent on one another.  Understanding how to coordinate work when this happens can mean the difference between successful completion and needless delays and cost.

What you can’t do

Lets start with what you can’t do.  The reason you can’t do these is that the associated things are limited to a project, therefore do not work for issues outside your project. In other words, JIRA itself will not let you do this.  You can ask, but I cannot change these things any more than you can as these are built into the code.

The first is put it in a release within your project.  Because a release is a project level config, you cannot put a foreign issue into your release.  You can get around this for querying if the other project admin is willing to create a release in their project with the same name, but this solution will not let you use the project release tools to track that foreign release.

The second thing you cannot do is transfer a sub-task to the foreign project.  As a rule, a sub-task and it’s parent MUST be in the same project.  This is hard-baked into JIRA, so there is no way around this…directly.  What you can do is use your subtask as a tracker in your project, and link it to the foreign issue.  It’s not as direct, as stated, but it is the recommended method.

What you should never do

That being said, there are a few things you should NEVER do.  In fact, they are considered two of the Seven Deadly JIRA Sins:

JIRA Sin II causes a problem because most teams here use some form of either scrum or kanban board to track their team’s work.  If you transfer an Epic to their project as a request for work to be done, these Boards treat those differently by putting them into the sidebar on the board’s backlog screen.  Here, they will be missed, which means your work won’t be done.

JIRA Sin IV is more a courtesy, but it is still sin worthy.  Most people will at the very least move an issue some sort of closed status when completed, and may even include comments to explain details or ask questions.  Skipping this will not only waste your time, but theirs as well.  Even If you don’t see any activity on your issue, try putting in a comment and waiting a few hours before you march to their desks demanding an update.  Maybe they had a higher priority item come in, or maybe they just haven’t gotten to it yet.  Maybe they thought it was a lower priority than it actually is.  However, stopping whatever they are doing right away so they can answer your questions that could have been handled by comments is not going to win you friends.

Well, what the heck can I do then!?

  1. Releases with the same name
    1. Pros – This is great for teams that use dashboards – as a query on only the release name will return results of all projects that share that release name.
    2. Cons – You lose all ability to track from a single project release view.
  2. Duplicate issue and Link
    1. Pros – This is actually the Atlassian recommended approach to handling this problem.  It gives you visibility without directly interfering with another team’s processes.
    2. Cons – You actually have to watch the issue and followup on it.  You may also have an issue updating the issue, depending on the other team’s permissions and setup. I Should state that this is generally not a problem, only in a few very specific circumstances, and we actively try to avoid this situation – but it does happen.
  3. Transfer story
    1. Pros – You get to craft the story how you see fit, and have it fully prepared before firing off to the other team.
    2. Cons – This can literally break another team’s workflow if you transfer it as the wrong issue type or wrong status.  For this reason, this is not really recommended.

Watching issues, your ace in the hole

So, you’ve created a story/task/what-have-you for the other team to work on.  Now what?  How do you know when it’s done? The answer is simple, yet this is the biggest problem people seem to have.  However, if you watch an issue, you can get JIRA to email you every time anyone transitions, comments, edits or even looks at an issue the wrong way.

This is your ace in the hole.  You’ll know when an issue has been worked on, and conversely when it has not.  This will then let you reach out and maybe post a comment asking “What’s up?”  As a rule of thumb, you should always watch issues you are sending to another team.

Heads up!

If there is one thing that will help get eyes on your issue and get an early ETA on it, it’s a simple “Heads Up”.  Reaching out to the project owner on chat saying “I just submitted XYZ-1234” with a link can help make sure it’s seen, and make sure the other team has all the info they need to proceed.  While a simple step, most people neglect this in favor of just dumping it into a black hole of a queue.  Don’t wait for a problem or an issue to be ignored, just reach out! It will save both your team and their team time in the long run.

What it really comes down to

The great myth of JIRA is that it will do the work for you and make your life easier. While it can make life easier, it merely provides an platform for you to organize your work, share it with others, and submit work requests to others.  It is still on you to do the communication, the followup, and the work, and this is no truer than when you have to work with another team.  JIRA will give you place to submit the work, but it is on you to do the legwork required in following up and making sure it’s done.  But use the tools it gives you to help! Write comments! Watch issues! Give the other team a heads up in chat when you submit an issue!  All this will make your next collaboration project a success.