Can we access the Query Profile(r) via Java?

[Janek Schumann]

Dear Community,


Does anyone know if and how to access the JQL Query Profile shown to an admin via Java?

What we are doing:
- find any place in Jira, where JQL is used. For example, filters/search requests, custom fields, workflows, Groovy scripts, ...
- check if the JQL is executable at all -> report broken JQL to the owner of wherever we found the JQL

Since we have this already, now we would like to use the profiler as well to identify performance issues

any ideas?

Cheers,
Janek

[Radek Dostál · Accepted solution]

That's a scriptrunner feature isn't it? I don't understand what you're after. Either you don't know what it is and you want to know where to find it, or you want to know how a non-admin can find it, all the while using confusing words.

For the first part, if I'm right you need the ScriptRunner plugin installed.

For the second, you can't, because it's not user-facing nor should a user be expected to understand any of it.

[Janek Schumann]

mhh... I think you are right about it being a ScriptRunner feature. I haven't noticed that. 😥

(I use ScriptRunner extensively. I thought this is a Jira feature like the "Where is my field"-admin helper)

[Janek Schumann]

what I am looking for is :

QueryProfile queryProfile = some.package.to.QueryProfiler.analyze( jql );

It has nothing to do with end-users being able to see this. It has to do with efficient administration, maintenance, and operations of Jira, which Jira is lacking for 20+ years, as we all know. 🤪

However, since you are right that this is a ScriptRunner feature, I will look at it's plugin source code to figure out the appropriate steps. 

[Radek Dostál]

Alrighty, that makes sense. I haven't looked into the jar myself but there's a good chance you could actually use @WithPlugin to use the class once you find it without needing to copy pasta it (which I reckon could be relatively complex-ish). Just an idea, dunno if it's exposed to OSGi.

From what I can see it also uses REST

/rest/scriptrunner-jira/latest/jqlfunctions/profile?jql=<URL ENCODED JQL>

So worst case scenario maybe you could use an HttpClient if not java.

[Janek Schumann]

Trying to post a similar answer, it won't let me for some weird reason. ¯\_(ツ)_/¯

I came to the same conclusion as you:

• looked at the JAR and found the JavaScript with the endpoint
• tested it, not a nice return structure, but usable enough. Some String parsing required. Calling a REST API from within ScriptRunner or a custom plugin is ... not nice. (secure token handling etc...) 🙄

Better than nothing and sufficient for what we are trying to do.

Now we can implement something like "show me all JQL queries anywhere in Jira's configuration, which take longer than 100 ms" for performance optimization.


What I understand from the code:
They call a custom logger for every ScriptRunner function executed. This is how this "profile" is being generated.
Internally, ScriptRunner has a nice enough structure ("ProfilingTimerBean"). It is serialized to a String for the REST API though and all the structure is lost. 🙄😤

Well, anyways. It will work. It's a hack job. But isn't that the story of our lives working with Jira to begin with? So nothing new. 🤣

[Janek Schumann]

Regarding the class:

com.onresolve.scriptrunner.runner.JqlFunctionsManagerImpl

 method:

QueryProfileInfo profileQuery(ApplicationUser user, String jqlQuery)

however, the JqlFunctionsManager's constructor needs a list of parameters.

---

OR:

just issue a standard Jira JQL query through the SearchService and look at the result in 

com.onresolve.scriptrunner.concurrent.util.UtilTimerStack

---

As you suggested, dependency injection in a custom plugin would most likely take care of the first case automatically. The JqlFunctionsManager is being exported by the plugin.

Anyways. Doable. I will just use the REST API for now and do it right later. (= never 🤪)

[Janek Schumann]

In case someone wants to do the same:

1. ScriptRunner for Jira is required, as Dostal pointed out
2. Undocumented REST API from ScriptRunner:

HTTP URI:
/rest/scriptrunner-jira/latest/jqlfunctions/profile

HTTP METHOD:
GET

HTTP Query parameters
jql: the JQL query to get the profile for

Return MIME type:
JSON

Example:
{
    "profile": "[388ms] - JQL query profile, thread: https-openssl-nio-443-exec-1\n  [71ms] - {issueFunction in linkedIssuesOf(issuetype = Project, derives)}\n    [22ms] - Subquery: issuetype = Project",
    "queryTree": "issue_id:(1 2 3 4 5)",
    "opTimers": [

        "OpSnapshot{name='issue.index.reads', invocationCount=1, elapsedTotal=387909400, elapsedMin=387909400, elapsedMax=387909400, resultSetSize=0, cpuTotal=0, cpuMin=0, cpuMax=0}",
        "OpSnapshot{name='db.conns', invocationCount=1, elapsedTotal=387914800, elapsedMin=387914800, elapsedMax=387914800, resultSetSize=0, cpuTotal=0, cpuMin=0, cpuMax=0}",
        "OpSnapshot{name='db.reads', invocationCount=1, elapsedTotal=387916500, elapsedMin=387916500, elapsedMax=387916500, resultSetSize=0, cpuTotal=0, cpuMin=0, cpuMax=0}"
    ]
}

Not a "nice" return structure, but I'll take it. Better than nothing.
Now we can implement something like "show me all JQL queries anywhere in Jira's configuration, which take longer than 100 ms" for performance optimization.