$ sudo -u postgres psql postgres
postgres=# CREATE USER confluence;
CREATE ROLE
postgres=# ALTER USER confluence with PASSWORD 'confluencepwd';
ALTER ROLE
postgres=# CREATE DATABASE confluencedb WITH OWNER = confluence ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
CREATE DATABASE
postgres=# \q

choose the "production install" to make sure you're offered the DB configuration option

Confluence 5.5 confluence.cfg.xml :

confluence5.5$ cat confluence.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<confluence-configuration>

<setupStep>finishsetup</setupStep>

<setupType>custom</setupType>

<property name="attachments.dir">${confluenceHome}/attachments</property>

<property name="confluence.license.hash">zdDzjZdbADawoyURqdGzoPaZBAgZKUHUYsHVjDZIEvvPyszv2XGziugEuwkSfQ&tg;Gdt6xeE2Xd9zIm1EpkgsJaGpbCyrw</property>

<property name="confluence.license.message">zbAbzjWIpGHwCEPxwadtQDNsBirASzeAvdenNYxIEfgRidiyujEcxrnDWSbWuGVKbgehfZHRPfcxbDFasXfbofNaPgcAwQJuzycjPPOPFvpjyUgxKytaugGgBKevkPAgDRJwDOcKFdTNguqgEqzjtXoOHFbACGavHChWJweOuokZevvxXiTBydzgzbrcXhFWQIHDrqeEHaPXCgzCBqjVBdTyNYCrADHZKIDhcpxTNCjJjewXaicAOvssJvkRQcBqeyoeKcuXyrtvtyDXefYaeffWFbiQYcTBPAdBPGtenCKxZqpubCHYPdaXuqvSARJcuQBzBFvkuovnVJNNDBPpvSDAZpaOaJDOTkqvCXjoIxeDCsqkqdwwsfwnvXWsyzyITiQRPNShDmUESMzT5Imuvab8oNWjYvUj9Xe24N4jL6Wt8GLkwcyaWLBYZgSPeqPgRLozPuPlPjMObtRzOHaqj4m3e4o5KssixIGh&yg;1vT6qpRg0INFzR6OmnAkxOstG9OwNOCev&tg;a7tX9rxXs6u8i1jDyhH3ldYs8QUaqXeMrknEN5cxbIXbp9QAEjM7Z7AMHlaH4mcUgCc1B61j&yg;k&yg;7Ek&tg;fux6A9pr&yg;&yg;Ht&tg;hmHH2q5TFZd5EcZOqJQrp0nFuX9Gi1skZYGOxW&tg;BygPOwDXLkVZpe8WQPAqYCSedTpKGR2nDkWVETxEILEU&tg;kOeNoTlfbviJq4a5egI6eLA6Me3jlfBQL7f9pw&yg;fwS5zmtQJOOz7L&tg;qph1b&yg;DK8pTZNrkCzi4i38f</property>

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>

<property name="hibernate.connection.password">confluencepwd</property>

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluencedb</property>

<property name="hibernate.connection.username">confluence</property>

<property name="hibernate.database.lower_non_ascii_supported">false</property>

<property name="hibernate.dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>

<property name="lucene.index.dir">${localHome}/index</property>

<property name="webwork.multipart.saveDir">${localHome}/temp</property>

</properties>

</confluence-configuration>

Confluence 6.13.19 confluence.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<confluence-configuration>

<setupStep>complete</setupStep>

<setupType>custom</setupType>

<property name="access.mode">READ_WRITE</property>

<property name="admin.ui.allow.daily.backup.custom.location">false</property>

<property name="admin.ui.allow.manual.backup.download">false</property>

<property name="admin.ui.allow.site.support.email">false</property>

<property name="atlassian.license.message">NNNOoN0BQNbCrAc9xI1CtmNHuh/7X0v80EtzME/dxvMBjZxPL8d+fhlzxbBefeX1OrKs27UAe0FGK iFpAa36ChpxmYxkbAmNJX8hoaroopBWk4Mg2Ev9DwHSVIaBPr5L1dI11Jkv5BEp0HDA6EeVF5Rk2 xucHHW2H1WqpSKWEcXiHMYmgXUifEXVRtJtHFTFSMKtHtIx975cLECoXTNWpNar+4nW6ghuorWCz OqFyi1Ul/nC3SB5VdUm5gmqE4H7w9uf4toOOIgxbGtsvPyIg3CEJm30emrwdvpzizdKP3+23GeQi e1z1co1yzEWxZMkOMml5V9/1rzTksbWEWEBcYMRmRZ3IyDbRPFyzLEwBg8ytr/T3gNZpXqwgIcnc X7Vm04xavyaxddq8hxkbQTWxFBt7i6JIkZCwUT1tKbdGuFT3dCw94Xwy+ZLorFPGNGo1NDgA80X0 PTA0kuRPrWf2GH8oonbLruehk/uO4RfhHbjYNVHN70z74+a+XYnayGBhydYW82HVG4PSRna3Jf1k 1m9QN7Jp7sdpRJhgb1yK02uc</property>

<property name="attachments.dir">${confluenceHome}/attachments</property>

<property name="confluence.database.choice">postgresql</property>

<property name="confluence.database.connection.type">database-type-standard</property>

<property name="confluence.setup.server.id">SERVER-ID</property>

<property name="hibernate.c3p0.preferredTestQuery">select 1</property>

<property name="hibernate.c3p0.validate">false</property>

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>

<property name="hibernate.connection.password">confluencepwd</property>

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluencedb</property>

<property name="hibernate.connection.username">confluencedbuser</property>

<property name="hibernate.dialect">com.atlassian.confluence.impl.hibernate.dialect.PostgreSQLDialect</property>

<property name="jwt.private.key">ZVVT/tVONQNAOtxduxvT9j0ONDRSNNFPOhtjttoxNtRNNbVOtDPDXUoNQgeeET8v/rvaFZCT1XjVJIAl2RhnB1xq5MkWkNoelY+BI3HYicksyT36sySKYJTcsyfG1sPDejf/RniqrH0aU72CL5bOmY/kHlC2TjUF9sokDG+52Y0trfRHKAyehAHqcfiI53LT1fOoV8Q9tSScy7iVdU44qG2XfOMI2wI0Kx2ttwxUVEC4R5Y7kPuQBxH8eQ1kg8RiqZBGhZXUhZ/U7J5hiJ3IweoRZsvRg1W2DgXwNDn0zbcwstUwl/vb+H0JX55f3ZN1eKwqeWG1q8RN/qThvSH/oSiydoGP9XYJhxnV1YxaLiiFp0mnB0Z9Zro3D6hE5OizH/B2vFpXc354xArlalDPL/kG48j9K1xCNaQ6DEXaBD6J39FD/FNPvco9RxH9bRt6rQTtbzOW44HbK/zGIUWofJ39JjL9dSrWm53SrnBt8lB3LcKrGrXwV2suCkjbn3tKEpg7zXHhQsE4muwhDyLHaItZ4HonxW8kfgcxa7fODj3YTz9W+8xPNjRNNDXPNLOenofE3/++mK8O069T9wQdAtm5X5KPCBtLD6OwMaYAuNXxYGPBUkkKL++tFQvdYCGhjPUR6FSm5wHMzIDU6zYx4m1MdLmIPdU2AIaCghAHiHercrpnKRCsqy91yNUW6zHJahVf9qgekfgdmExTm909Ou+wGviqy6504hn4Y/k0r+YLxziR7HZDmAGHD8TOIhrcZNUz82/IP9LzEuc0A3n7Xwza/bVXtMfIEDScegV+RIdtD6biFgE0CoWs78C4hRTkIyKUhY4zMK7ZN/abQzRJGgoGVQrydsXuTqpAnHPkxXBf4qUwGKLJJpjfqBScCC6CRjhD+6rvGsZNm1TFSTMU2W6RK9bPWlJkVWeuoDbsIkpjXALg4pCbQEU+YHoeRqQ1BiV6o0G+85pfBnQhoWYZUS82lQUJR+8czk9tvfXhcWrAkeujokc0M0D7lefG3H1rcmIf0XmnMdjxMub1q3lc4k1RiGKNjrOqW7pC5OkY0u5Wx153pQ4BcEE34kTtbNRPtpRNjhTBOK5SxPfcYtn/P0Esoze7L05FKwP8PH7tQpleO9GSzSY9B2Dqva7d3jmLavlfwscCJLU/LRCF8nENSOvJLXFohFdxfXBiRB6C5gCydXNGZALdG/SOefLvjqRJ1TBut2t+OSVHYxTZuhyKlYOu3igCTc8dD/5FuwFKQm9pDKx0fN8VPxniI3KiYbynS3x2xCciOHaPeJEyTAfApjR6kWTEdTM+bjOX97J/alBmkpxh5UmpkDibIn/59X8uVzf5NbUONY1rtAL4wzT1Sr55RU16E3DPrRGE/8yWgjB43c17IOATIzQh14w/M8SuPVrkvLmO4polItOpLb70L2WwpImW2yE1kTLb+pdqb3CkXdqzxx0oyjMiJXbRiO3jTo09JsKK69nxHyRz3CN0eKSsrIpWV1maHO+dUs9m9GkjPiIRi34bhcA4na7iU2U5QZW6dyMA9WOs5RzxODbj+fqY4pbKc/weiLsYE8EfrjAp665nKKhXW1yqKmCZyWemY96RSJ7SRDXOjDPuZiKWZKCISi6CWrSgYaqW3WOblzMmckuQRf1rKZr0FWaEfNCe1cct7l7HcpztiD9JhMOqI5lbDwVN0351bDwlx/nQdsExpTQyCcNyiJ0tzWvUZi/49UHPKe5gI8wfxvHAw3XAUS+5P1g0IEiM05lOI5gAFN8fNiv8YyNghcyGEmQ/2Z8ejBOISvMfqtmuxqOQPNEOrLit5HFP1A9OT+yPb666s12Z8mpCj3Ga7O5BVzK5qupsajCyeEFmNkVQnURPtpNpLwQCnkF0Dz8lTxISrKTPFhu58Qi1xX30lq3pUhZtYUr/+nlzcEzWT6RdeLdsMJDD7YiSCOBgg6J51bixF4RSQp3ACwRXyVjhHnFPPAn+5soPPFuk+1zl5j0Ry1PUw8PFbUvFU1NLIAm2IRtpdbe5KgMpzXC8nDLS9NyNo/u/0Im9j5h6jA6xHn7+62Uvb7nKrGs/on0igA2kk4oyDckmd+akx5+QXagMdeQZcyQPgcqyEdPOMOJJRNAWk+EdtCRPtpRNxnXoH3SMKxTNTIjqjYt9EvwmPs+RXwzd1zSGlOKNl2PHJL6EzDYzu4py0ys3iUBL0pDUvDCr14AYnR4xItE/ypystGQXNCy7QNzfCJ3xQkrHGnCZAH3EfgnVJ8NjhbPTjCwSxwD9INq/eWlOBmMm9S4i0/H3ds9tAbBl+R7aKv4Dm76O/L95uwfrQ6L5W1DvyxgLQsArCRuaDbG0sKv+TaGVig6JwAg2OIOpLW1ZfS9iviwuoHeY184oDlmhgrSD123123</property>

<property name="jwt.public.key">ZVVObwNAOtxduxvT9j0ONDRSNNBPNL8NZVVOvtXPNLRNxPu2jN7n60EiVi3bc0wQkgFfPSyGpguYzwgMUrJpFpDT68v/wyq1P76pK5Eg+a5EIl1udK5oR9KjxX8YCkTe3KyAWk+9w2BnNpl/8IZw9ufO0iK28HR/hqv9VUeOSSmMn7wIUnoY1rq2OgoNJlCN/LOEnMr7lXu+BUH9veNJIqb1qS5AbVV5OlRG+OBF+8DbDmcSCXj9posOY3GQx7wPu7wCk+1hoe1g1L62kQU4uYqFqxYFbjRTgWdXL34O48i4dCyASvhroAmNAn143nlH9KsONC3EebuIC2ko5nz0jiFv1ecTvAF5W2Y70aAZ2wgQCGUz90BexrDo5yCmgbxaPdq+rWQKfc8xNzC8H+CZCI9MQjWj+xRFcmxByg/HxC0tNbdJ/EWSCnOVBatkbXWtFrBSXS/5x1ElJ7Sg/IfTCnuKvp+qkKzwbCZwg2XI3x3vblAa4G8pXTg4S0KYr5vyYt30rZ4L7xWJSW1LQBST2cPsZoYnMW+7NHZAlkciFsiWNtZONNR=</property>

<property name="lucene.index.dir">${localHome}/index</property>

<property name="webwork.multipart.saveDir">${localHome}/temp</property>

</properties>

</confluence-configuration>

Atlassian Jira

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql
psql postgres
CREATE ROLE otrs WITH LOGIN PASSWORD 'otrs'; 
ALTER ROLE otrs CREATEDB;
\q
 
#create user and db
psql postgres -U otrs
CREATE DATABASE otrs;
GRANT ALL PRIVILEGES ON DATABASE otrs TO otrs;
 
#show databases
\list 
 
#import dump
psql -h localhost -d otrs -U otrs -f /Users/tokamak/Desktop/IN/MISSIONS/TOTAL/OTRS/Files/psql_otrs_dump.sql
 
#show all db sizes
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;
 
 
 
#Create jiradb and user
CREATE USER jiradbuser WITH PASSWORD 'jiraSQL';
CREATE DATABASE jiradb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE jiradb TO jiradbuser;

https://www.codementor.io/engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb

jeudi 21 janvier 2021

Opsgenie webinar / ressources

opsgenie is a tool allowing filtering and routing of monitoring-triggered alerts (nagios, AWS SNS, datadog, ...) to specific channels (SMS, phone-call, Slack, Jira, ...).

Main features on top of this :

time-table (who's on-call)
alerts / incident resolution centralization
third party integrations with 100+ tools

Opsgenie Learning Center : https://docs.opsgenie.com/

[video] Opsgenie : "What do we do?" https://www.youtube.com/watch?v=yphtZ9z2TtA&feature=youtu.be

[video] Opsgenie: "First Look" https://www.youtube.com/watch?v=pyM2dROKn6g

Opsgenie Pricing : https://www.atlassian.com/software/opsgenie/pricing

Implement nagios to opsgenie Heartbeats :

mercredi 25 novembre 2020

OpsGenie : AWS SNS message to Jira Description Wiki markup (+ links to S3 logs and SSM output)

If you're using the AWS SNS opsgenie integration and want to publish to JIRA, you can for example use the following code to present the data in a slightly better way :

I this use-case I'm using the SNS channel to publish outputs from a system manager (AWS SSM) command that also publishes it's outputs to an S3, so we're using this extraction to provide the direct links to the s3 logs and the SSM run command history.

And in the end, we copy the message we received from the SNS channel "raw"..

In Opsgenie, in the specific Amazon SNS integration (Incoming Amazon SNS), in the Alert Fields, you can for example modify the "Description" so that it transforms the Message received like this :

h3. Details
|| AWS region | {{ TopicArn.extract(/arn:aws:sns:([^:]*):.*/) }} |
|| Status | {{ Message.extract(/.*"status":"([^"]*)".*/) }} |
|| Instance ID | {{ Message.extract(/.*"instanceId":"([^"]*)".*/) }} [(aws link)|https://{{ TopicArn.extract(/arn:aws:sns:([^:]*):.*/) }}.console.aws.amazon.com/ec2/v2/home?region={{ TopicArn.extract(/arn:aws:sns:([^:]*):.*/) }}#InstanceDetails:instanceId={{ Message.extract(/.*"instanceId":"([^"]*)".*/) }}]|
|| Command ID | {{ Message.extract(/.*"commandId":"([^"]*)".*/) }} [(aws cmd)|https://console.aws.amazon.com/systems-manager/run-command/{{ Message.extract(/.*"commandId":"([^"]*)".*/) }}] [(s3 logs)|https://console.aws.amazon.com/s3/buckets/ssm-output/ssm-log/{{ Message.extract(/.*"commandId":"([^"]*)".*/) }}/{{ Message.extract(/.*"instanceId":"([^"]*)".*/) }}/?region={{ TopicArn.extract(/arn:aws:sns:([^:]*):.*/) }}&showversions=false ]
|
|| documentName | {{ Message.extract(/.*"documentName":"([^"]*)".*/) }} |
|| requestedDateTime | {{ Message.extract(/.*"requestedDateTime":"([^"]*)".*/) }} |
|| eventTime | {{ Message.extract(/.*"eventTime":"([^"]*)".*/) }} |
h3. Opsgenie info
|| EventType | {{eventType}} |
|| Timestamp (opsgenie) | {{Timestamp}}|
|| Tags | {{tags}} |
|| TopicArn | {{TopicArn}} |
|| Actions | {{actions}} |
h3. Original Message (raw):
{code}
{{Message}}
{code}

Nb: this might only be available in certain OpsGenie subscriptions unfortunately :-(

lundi 31 août 2020

Rsync and exclusions

an old post found back on disqr about rsync (original date : circa 2016)

You might want to use the rsync exclude options directly in your script either by specifying a file with all the exclusions to perform, or by specifying them in the command line directly :

--exclude-from <file-name with 1 pattern by line>
--exclude <file or="" dir="">

For example :

rsync -r -a -v -e "ssh -l Username" \

--exclude 'dbconfig.xml' --exclude 'WEB-INF/classes/' --delete \

/local/Directory remote.server:/remoteDirectory

One important thing to keep in mind when excluding a directory is that rsync will always consider the path to be relative to the source directory.

This can be used for example when you want to push your production from a Production JIRA instance to a Staging JIRA instance, but your dbconfig.xml is different (different DB auth parameters for example), and hence want to avoid some files.

vendredi 10 janvier 2020

JIRA + Script Runner : template / default Description value

using Adaptavist Script Runner plugin > Behaviour

def desc = getFieldById ("description")

def defaultValue = """ *Some text in bold*
since we have a multiline string, we can put some more
but keep it short, this is a template...
What / Why / How ...""".replaceAll(/ . /, '')

if (! underlyingIssue.descrption) {
desc.setFormValue(defaultValue)
}

mardi 1 octobre 2019

JIRA - find a plugin usage in a Workflow (groovy Script Runner + SQL version)

Know where/if a plugin is used. Both have to be adapted to match a specific plugin.

First version gives more data but requires ScriptRunner.

Second version only uses SQL, but is less extensive.

You need to know what you're looking for : com.innovalog.jmwe.jira-misc-workflow-extensions

Script Groovy

Adapted from : https://answers.atlassian.com/questions/205094/how-to-find-all-workflows-which-are-using-postfunctions-of-a-plugin

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.workflow.JiraWorkflow
import com.atlassian.jira.workflow.WorkflowManager
import java.util.regex.Matcher
  
String searchText = 'com.innovalog.jmwe.jira-misc-workflow-extensions'
  
WorkflowManager workflowManager = ComponentAccessor.getWorkflowManager()
Collection<JiraWorkflow> workflows = workflowManager.getWorkflows()
  
String result = "Workflow;Function;Type;Action;Step\r\n<br/>"
  
workflows.each{workflow ->
    def workflowXml = workflow.descriptor.asXML()
    Matcher m = workflowXml =~ /${searchText}/
    if (m.find()) {
        String workflow_name = "${workflow.name}${if(!workflow.isActive()){";(inactive)"} else ';active'}${if(workflow.isDraftWorkflow()){"(draft)"} else ''}"
        def wf = new XmlParser().parseText(workflowXml)
        List<Node> wf_flat = wf.depthFirst()
        wf_flat.each{
            if (it.text() ==~ /.*${searchText}.*/){
                result += "$workflow_name;${getNodeInfo(it,searchText)}\n\n<br/>"
            }
        }
    }
}
result
  
String getNodeInfo(Node n, String search) {
    String nodetext = ''
    nodetext += "${n.text() - search}"
    def p = n.parent()
    while (p) {
        switch (p.name()) {
            case '"post-functions"':
                nodetext += ";post-function "; break
            case 'validators':
                nodetext += ";validator "; break
            case 'conditions':
                nodetext += ";condition "; break
            case 'action':
                nodetext += ";${p.attribute('name')} (${p.attribute('id')})"; break
            case 'step':
                nodetext += ";${p.attribute('name')} (${p.attribute('id')})"; break
            case 'global-actions':
                nodetext += ";GLOBAL "; break
            case 'initial-actions':
                nodetext += ";INITIAL "; break
  
        }
        p = p.parent()
    }
    return nodetext
}

SQL Query

From : https://answers.atlassian.com/questions/93165/find-which-workflows-that-uses-a-specific-validation-plugin

SELECT
  workflowscheme.name,
  workflowschemeentity.scheme,
  issuetype.pname,
  workflowschemeentity.issuetype
FROM
  issuetype,
  workflowschemeentity,
  workflowscheme
WHERE
issuetype.id = workflowschemeentity.issuetype and
workflowscheme.id = workflowschemeentity.scheme and
  workflowschemeentity.workflow in
    (SELECT
        jiraworkflows.workflowname
    FROM
        jiraworkflows
    WHERE
        jiraworkflows.descriptor like '%com.company.plugin%');

mercredi 21 août 2019

JIRA Python script : iterate to get the JQL result

Python 2.7 script to :

query the result of a JIRA search query in JQL
iterate so that we do not get all the data at once but only by small batches
print the result list

Pre-requesites :

python
https://pypi.org/project/atlassian-python-api/
https://atlassian-python-api.readthedocs.io/en/latest/index.html
some JIRA Server or JIRA Cloud + credentials

Note that the auth for JIRA Cloud might change a bit

from atlassian import Jira
import json

#auth for JIRA Server
jira = Jira(
url='https://support.valiantys.com',
username='username',
password='password')
# Get the SERVER issues that are Running
JQL = 'project = DEMO AND resolution = Unresolved AND issuetype = "Story" AND status = "In Progress" ORDER BY cf[16032] DESC, cf[15857] DESC'

data_tmp = {} # data we progressively get each interation
issues_all = [] # full list of issues
issues_tmp = {} # list of issues during this iteration

next_start = 0
size = 50

while size > 0 :
print("-------- trying to get issues:%s..%s" % (next_start, next_start+size))
data_tmp = jira.jql(JQL, start=next_start, limit=size)
next_start += size
issues_tmp = data_tmp['issues']
size = len(data_tmp['issues'])
issues_all.extend(issues_tmp)

print ("(final) #issues=%s" % len(issues_all))

# now we can do things with the data
for x in issues_all:
print("%s %s %s %s" % (x['key'], x['fields']['customfield_16032'], x['fields']['customfield_15862'], x['fields']['customfield_15857']))

vendredi 7 juin 2019

JIRA Script Runner log debug

snippet of code to use the output logs in ScriptRunner Adaptavist groovy scripts

ScriptRunner log.debug

tool	ScriptRunner for JIRA
version	8.1.1
use case	log.debug SR script

/***********/
import org.apache.log4j.Logger
import org.apache.log4j.Level
 
def log = Logger.getLogger("com.scriptname")
log.setLevel(Level.DEBUG)
/***********/
 
//and then, for example to print the variable trem
log.debug "trem=${trem}"

example :

mardi 7 novembre 2017

Bamboo concepts for continuous integration : plan / stage / jobs / tasks

Example :

mercredi 7 juin 2017

JIRA Xporter plugin - tips and tricks

Tips for the plugin Xporter

plugin : https://marketplace.atlassian.com/apps/891368/xporter-export-issues-from-jira?hosting=server&tab=overview

Print the current date
Count number of issues in a JQL
Sum a CF for all issues on a JQL
Number of links
Number of issues in a JQL (number format)
Format a number with locale's decimal separator
SUM Excel correct values in wrong locale format (workaround)
Iteration on issue & subtasks

Print the current date

tool	Xporter
version	5.0.0
use case	Print the current date

Document generated at
%{(new Date()).getDate() + "/" + ((new Date()).getMonth()+1) +
"/" + (new Date()).getFullYear()}

Count number of issues in a JQL

tool	Xporter, excel
version	5.0.0
use case	Count number of issues in a JQL

Excel

JQL	<filter>
nfiches1	${jqlcount:
nfiches2	}

usage

=CONCATENATE(nfiches1;<JQL>;nfiches2)

example

${jqlcount:project = PJAB AND issuetype = Pénal AND created >= -90d AND status not in ("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"}

Sum a CF for all issues on a JQL

tool	Xporter, excel
version	5.0.0
use case	Sum a CF for all issues on a JQL

Excel

JQL	<filter>
CF	<cf name>
nsomme1	${set(count,0)} #{for n=JQLIssuesCount\|clause=
nsomme2	} #{if (%{'${JQLIssues[n].
nsomme3	}' .length > 0})} ${set(count,%{${count} + ${JQLIssues[n].
nsomme4	}})} #{end} #{end} %{Number(${count}).toFixed(2)}

usage

in a excel cell :

=CONCATENATE(nsomme1;<JQL>;nsomme2;<CFname>;nsomme3;<CFname>;nsomme4)

example

${set(count,0)} #{for n=JQLIssuesCount|clause=project = PJAB AND 
issuetype = Pénal AND created >= -90d AND status not in 
("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"}
 #{if (%{'${JQLIssues[n].Créance comptable}' .length > 0})}
 ${set(count,%{${count} + ${JQLIssues[n].Créance comptable}})}
 #{end} 
 #{end}
 %{Number(${count}).toFixed(2)}

Number of links

tool	Xporter, excel
version	5.0.1
use case	Number of links matching a criteria (including creation date)

Count the links :
This requires to set a counter, and iterate over the links returned by "LinksCount".
Filter on a date :
This requires to create different Date objects, either initiated with the date returned from the issue, or the calculated date. In the end we are indeed comparing milliseconds since 1-1-1970, so the > is enough.

/* test if the creation date is within the last 90 days */
(new Date('${dateformat("yyyy-MM-dd HH:mm:ss"):Links[n].Created}') > (new Date(new Date().setDate(new Date().getDate()-90))))

example

/* Set the variable countD4 to the number of links to the current issue "A" where :
* link with the current is :  "A" -(est modifié par)-> other issue 
* linked issue was creted less than 90 days ago
* linked issue type is "Créa-Modif Document".
*/
${set(countD4,0)}
#{for n=LinksCount|filter=%{('${Links[n].LinkType}'.equals('est modifié par')) && (new Date('${dateformat("yyyy-MM-dd HH:mm:ss"):Links[n].Created}') > (new Date(new Date().setDate(new Date().getDate()-90)))) && '${Links[n].IssueTypeName}'.equals('Créa-Modif Document')}}
${set(countD4,%{${countD4}+1})}
    * ${Links[n].Key} ${Links[n].Summary} ${Links[n].IssueTypeName}  ${Links[n].Status}
#{end}
${countD4}

Number of issues in a JQL (number format)

tool	Xporter, excel
version	5.0.1
use case	Count the number of issues, and print it in excel JQL format

${jqlcount:<JQL>} : counts the number of issues returned by the JQL
%{Number(<...>)} : make sure it's rendered as a number in excel, for example to use it with =SUM() excel function

example

%{Number(${jqlcount:project = PJAB AND issuetype = Pénal AND created >= -92d AND status not in ("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"})}

Format a number with locale's decimal separator

tool	Xporter
version	?
use case	Format a number with specific locale's decimal separator

use the ${numberformat("fr","#,##0.00#") function

example

${numberformat("fr","#,##0.00#"):JQLIssues[j].Montant de l'opération (en €)}

SUM Excel correct values in wrong locale format (workaround)

tool	Excel, Xporter
version	5.1.1
use case	Sum values that Xporter printed in the wrong locale.

Sometime Xporter has some trouble printing numbers that are actually recognized as numbers by excel. An example is by

example

let's say you need to sum values in a locale where the decimal separator is ",", but xporter outputs a decimal separator as "."; hence the =SUM() function will not work.

=SUM(VALUE(CLEAN(SUBSTITUTE(C11;".";",")));VALUE(CLEAN(SUBSTITUTE(C12;".";",")));VALUE(CLEAN(SUBSTITUTE(C13;".";","))))

Iteration on issue & subtasks

tool	Excel, Xporter, ScriptRunner
version	5.1.1
use case	Bulk export, export the issues & insert their subtasks

One way of doing so is to use the ScriptRunner JQL functions "subtasksOf(<key>)" and iterate on it.

example

#{for j=JQLIssuesCount|clause=project = PRF and issueFunction in subtasksOf("key = ${Key}") and issuetype NOT IN ("Débit", "Crédit") order by updated }

lundi 13 février 2017

JIRA server tips and tricks : WF properties on Status

(note : this works on JIRA Server / Datacenter at the time of the writing of this, Feb 2017)

Sometimes, you want to set some permissions directly depending on the JIRA Issue Workflow status (i.e. not depending on the whole Permissions scheme applied at the whole project level).

This hard to find trick consist in using Workflow properties on the WF steps.

Use-case : lock the "edit" button on 1 WF step, to 3 user groups

ref :

In order to allow the Edit right only to a specific user group, you need to add the following property to the workflow step impacted so that only JIRA administrators can edit an issue

jira.permission.edit.group=jira-administrators

To add multiple groups, use the syntax :

jira.permission.edit.group.1=jira-administrators 
jira.permission.edit.group.2=jira-fr
jira.permission.edit.group.3=jira-pmo

This of course needs to be applied for each step/status of the workflow impacted, making sure that this WF is not shared with other projects or other IT.

JIRA : Status Permissions

from : https://jira.atlassian.com/browse/JRA-32230?focusedCommentId=958985&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-958985

Please note that the expected format is not <property> = denied This format actually grants access to the user named denied.
The proper format is is:
<property>.denied = whatever

The complete format is:

<permission key> = jira.permission[.subtasks].<system project permission>.<grant type>[.<suffix>]
 
<system project permission> =   assign
                                | assignable
                                | attach
                                | attachdeleteall
                                | attachdeleteown
                                | browse
                                | close
                                | comment
                                | commentdeleteall
                                | commentdeleteown
                                | commenteditall
                                | commenteditown
                                | create
                                | delete
                                | edit
                                | link
                                | managewatcherlist
                                | modifyreporter
                                | move
                                | project
                                | resolve
                                | scheduleissue
                                | setsecurity
                                | transition
                                | viewversioncontrol
                                | viewvotersandwatchers
                                | viewworkflowreadonly
                                | work
                                | worklogdeleteall
                                | worklogdeleteown
                                | worklogeditall
                                | worklogeditown
                                        
<grant type> =  denied
                | groupCF
                | assignee
                | assigneeassignable
                | reporter
                | reportercreate
                | userCF
                | applicationRole
                | group  
                | lead 
                | projectrole  
                | user 
 
<suffix> = any text that makes the permission key unique among all keys of permissions in the same workflow step.

vendredi 6 janvier 2017

JIRA Server - redirect to full issue view after creation

(note : this works with most version of JIRA available as of now, Janvier 2017)

Instead of staying in the current view, you can use the following tricks to make your JIRA SERVER / DATACENTER redirect directly to the recently created issue.

JavaScript (in banner)

The trick here is to disable the "popup" with the create screen, hence going to the "full create screen" which opens the page afterward.

<script type="text/javascript">
AJS.$("#create_link").removeClass("create-issue");
$("#announcement-banner").hide()</script>

cf. : https://confluence.atlassian.com/jirakb/how-to-disable-create-issue-popup-300813780.html

Plugin "Issue Quick Start" (and JS source)

The following plugin aims at the same :

Marketplace : https://marketplace.atlassian.com/plugins/uk.org.sappho.jira.plugin.jira-plugin-goto-new-issue/versions
GitHub (License : MIT) : https://github.com/sappho/jira-issue-quick-start

Excerpt from the source :

AJS.$(document).on('DOMNodeInserted', function(event) {
    if (event.target.id == 'aui-flag-container') {
        console.log('issue-quick-start: Got post-it note!');
        AJS.$(event.target).on('DOMNodeInserted', function(event) {
            console.log('issue-quick-start: Post-it HTML: ' + event.target.innerHTML);
            var postItLink = AJS.$(event.target.innerHTML).find('a');
            var postItPath = postItLink.attr('href');
            if (postItPath && postItLink.attr('data-issue-key')) {
                console.log('issue-quick-start: Going to new issue path ' + postItPath);
                window.location = postItPath;
            }
        })
    }
});

[TODO] Script Runner : redirect with script fragment (??)

Is there some cleaner way to do it with SR "Script Fragments" to perform the redirect ?

cf. https://scriptrunner.adaptavist.com/4.3.7/jira/fragments/WebItem.html#_redirects

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.label.LabelManager
import com.atlassian.sal.api.ApplicationProperties
import com.onresolve.scriptrunner.runner.ScriptRunnerImpl
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.transform.BaseScript
 
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
 
@BaseScript CustomEndpointDelegate delegate
 
def labelManager = ComponentAccessor.getComponent(LabelManager)
def applicationProperties = ScriptRunnerImpl.getOsgiService(ApplicationProperties)
def issueManager = ComponentAccessor.getIssueManager()
 
labelIssue(httpMethod: "GET") { MultivaluedMap queryParams ->
 
    def issueId = queryParams.getFirst("issueId") as Long
    def issue = issueManager.getIssueObject(issueId)
 
/**    def label = labelManager.getLabels(issueId)
    if (! label) {
        labelManager.addLabel(null, issueId, "approved", false)
    } 
**/
    Response.temporaryRedirect(URI.create("${applicationProperties.baseUrl}/browse/${issue.key}")).build()
}