Piotr Bojko Specialist

Comments

  • You can do this with the help of Smart QL app With smartql create a view with query select FLOOR(resolved TO DAY) resolution, avg(TIMESTAMPDIFF(minute, created, resolved)) td from core.issues where resolved between ? and ? group by FLOOR(resolved TO DAY) Then plan the SmartQL Chart Gadget on a dashboard. Configure as…
    in Resolution time query on jira Comment by Piotr Bojko January 2019
  • With SmartQL - https://marketplace.atlassian.com/apps/1218767/smart-ql-sql-jql-easy-reporting?hosting=server&tab=overview - you can write, more or less following query: select jql.project, w.author, sum(w.timeworked) rawworked, AUX.ASDURATION(sum(w.timeworked))) prettyworked from table(AUX.JQL('id,…
    in How to sum the "timespent" per user Comment by Piotr Bojko September 2018
  • Try SmartQL - https://marketplace.atlassian.com/apps/1218767/smart-ql-sql-jql-easy-reporting?hosting=server&tab=overview You can write there following SQL query select * from changes This view from SmartQL contains issue changes, (affected and fixed versions). Just query issues from your project which changed fixedVersion…
    in Merge release version history? Comment by Piotr Bojko September 2018
  • You can use advanced searching with JQL - those fields will be available there. See Atlassian manual - https://confluence.atlassian.com/jiracore/blog/2015/07/search-jira-like-a-boss-with-jql. You can use also Smart QL to power your jira even more with searching and reporting…
    in Time tracking report based on filter query Comment by Piotr Bojko September 2018
  • With SmartQL - https://marketplace.atlassian.com/apps/1218767/smart-ql-sql-jql-easy-reporting?hosting=server&tab=overview - you can run following query: select * from issues where created between TIMESTAMP '2016-11-01 00:00:00' and TIMESTAMP '2016-12-01 00:00:00' and extract(hour from created) > 8 and extract(hour from…
    in I would like to have a Monthwise list of all Tickets between 7 PM to 12 AM Comment by Piotr Bojko September 2018
  • You can try SmartQL - https://marketplace.atlassian.com/apps/1218767/smart-ql-sql-jql-easy-reporting?hosting=server&tab=overview With SmartQL you can use SQL, SQL and XPATH to extract data from views exposed by the addon. Then you can export data by csv. You can even connect external tools to the addon/JIRA with jdbc…
    in How can I pull history from all issues in Jira Comment by Piotr Bojko August 2018
  • JSD has SLA. Read this - https://confluence.atlassian.com/servicedeskcloud/setting-up-slas-732528967.html . This a best tool to be escalate such tickets automatically, not by hand (which I presume you want to do with a mentioned query). With SLA you can automate a trigering email, escalate priority or simple view the new…
    in View all Jira Service Desk tickets open longer than 2 weeks Comment by Piotr Bojko August 2018
  • Having some thoughts that I gave you incomplete answer. I believe the whole query would like as follows select steps.id issue, steps.oldstring step, steps.newstring nextstep, steps.lagged fromdate, steps.created todate from (select I.ID, CH.OLDSTRING, CH.NEWSTRING, lag(CH.CREATED, 1, I.CREATED) over W lagged, CH.CREATED…
    in Query to found the sprint status of tickets on sepcific day? Comment by Piotr Bojko August 2018
  • Could you provide an example? Smart QL - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview can query historical issue changes in some advanced ways.
    in Jira query to return issue status in a specific date using resolution date Comment by Piotr Bojko August 2018
  • See Smart QL docs - https://codedoers.atlassian.net/wiki/spaces/SUPPORT/pages/186712065/Getting+started+with+Smart+QL
    in Find all tickets of users that are also working on a specific epic Comment by Piotr Bojko August 2018
  • Check the Smart QL - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview It allows you to mix JQL with SQL (and XPATH). Query for your need in Smart QL is as simple as following SELECT i.assignee, AUX.ASISSUEKEY(i.id) FROM TABLE(AUX.JQL('assignee', '"Epic Link" = SSP-24 ')) jql JOIN issues i…
    in Find all tickets of users that are also working on a specific epic Comment by Piotr Bojko August 2018
  • With a little help of Smart QL plugin - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview You can do following queries SELECT AUX.ASISSUEKEY(issue), author, AUX.ASDURATION(timeworked) FROM worklogs WHERE startdate BETWEEN TIMESTAMP '2016-11-23 00:00:00' AND TIMESTAMP '2016-11-24 00:00:00'…
    in Report/Query to track daily hours entered Comment by Piotr Bojko August 2018
  • With Smart QL - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview - you can scratch following queries SELECT * FROM changes WHERE fieldtype = 'jira' AND field = 'Component' LIMIT 10
    in History searches do not support the "....." Comment by Piotr Bojko August 2018
  • With Smart QL you are able query with SQL and do reports based on those queries: https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview * It honors JIRA permission system - you want see data from JIRA your are not meant to see. * It gives you an option to connect to JIRA via JDBC with dedicated…
    in SQL and using jira database for reporting Comment by Piotr Bojko August 2018
  • Following query SELECT * FROM issues WHERE resolved < CREATED + INTERVAL '7' DAY from Smart QL https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview should help.
    in Query for finding issues that were resolved in less that 7 days from the created date Comment by Piotr Bojko August 2018
  • Smart QL https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview can do this: Example (assuming Product Name is a custom field): SELECT * FROM TABLE(AUX.JQL('Key, `Product Name` AS `Product`','project = robustus')) WHERE Product LIKE 'Microsoft%'
    in Search for start of string Comment by Piotr Bojko August 2018
  • Please note that this query gives only data you can access in JIRA in normal way - it won't show you issues you want see in JIRA normally. Smart QL supports JIRA permissions system.
    in Query to found the sprint status of tickets on sepcific day? Comment by Piotr Bojko August 2018
  • Hello, You should able to to this with Smart QL https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview. SELECT MAX(aggr.checkedday) lastchanged, aggr.issuekey, ANY_VALUE(status) -- your desired result FROM (SELECT AUX.ASISSUEKEY(ch.issue) issuekey, FLOOR(ch.CREATED TO DAY) checkedday,…
    in Query to found the sprint status of tickets on sepcific day? Comment by Piotr Bojko August 2018
  • Could you, please, clarify following therms: * length that a project was created? * length that the project was completed?
    in Jira Time Reporting Comment by Piotr Bojko August 2018
  • Projects are independent units when it comes to versioning (and release management). So, if those subapps have theirs own, separate versioning - there are projects for me. Also, when the customer or an user of your software system sees only part of it, one sub-application, maybe there would be better to track this…
    in Any logical reason for a single software development team to have 18 projects for one application? Comment by Piotr Bojko August 2018
  • It is not possible with JQL, but using ie this addon https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview you would have SQL console with builtin permissions for data (just like in Jira) and you can query like this. SELECT * FROM PROJECTVERSIONS
    in SQL Query to get all values of version field Comment by Piotr Bojko July 2018
  • Assuming your "customer" is a reporter in some projects, you can browse all issues with JQL: project in (...mentioned projects here...) Then, using third party addon, like https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview you can do a following query: SELECT JQL.Reporter, COUNT(JQL.Key)…
    in JQL to return open Tickets count for Customer. Comment by Piotr Bojko July 2018
  • Hmm, it seems that this affects only demo sprints from demo boards based on demo projects :) . Normal sprints have their board properly assigned in RAPID_VIEW_ID column.
    in Column RAPID_VIEW_ID FROM AO_60DB71_SPRINT is null Comment by Piotr Bojko July 2018
  • @Olexandr Polinkevych what does this mean "without checking permissions"? You want to get a specific data despite in JIRA you do not have access because of permissions to them?
    in SQL query Comment by Piotr Bojko June 2018
  • Try - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview. You can use SQL then just like JQL: select * from changes limit 10
    in JQL to get latest stories transferred among squads Comment by Piotr Bojko June 2018
  • With this addon - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview - you can query JIRA with SQL straight from the app or through the special JDBC without to worry about permissions to data.
    in SQL Query Comment by Piotr Bojko June 2018
  • Maybe this will help. By using this addon - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview you can write following query SELECT DeltaID, COUNT(KEY) CNT FROM TABLE(JQL('KEY,DeltaID','')) GROUP BY DeltaID With such query you should have following table: DeltaID | CNT Delta ID 51 | 4 Delta…
    in JQL query to find Distinct issues Comment by Piotr Bojko June 2018
  • Hello, With following addon - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview You can write following report: select AUX.ASISSUEKEY(ch.ISSUE), ch.NEWSTRING, ch.CREATED, lead(ch.CREATED, 1) over (partition by ch.issue order by ch.id) until from changes ch where fieldtype = 'jira' and…
    in How can i count time period between two transition, how time it took for that phase?? Comment by Piotr Bojko June 2018
  • with this plugin https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview you can do following query select aux.ASISSUEKEY(IL.SOURCE), count(il.DESTINATION) from ISSUELINKS IL where name = 'Relates' or name = 'jira_subtask_link' group by IL.SOURCE HAVING COUNT(IL.DESTINATION) > 2
    in How can I search the task which has multi 'sub-task' via JQL or SQL? Comment by Piotr Bojko May 2018
  • Using the addon https://marketplace.atlassian.com/1218767 you would use SQL like language to have bacic queries and computations SELECT J.Priority, SUM(J.Key) FROM TABLE(JQL('Priority, Key', 'project = XXXX AND issuetype = Defect AND (status = New OR Assignee = currentUser() AND status not in (resolved, released,…
    in How to subtotal JQL queries Comment by Piotr Bojko April 2018
Avatar