diff options
Diffstat (limited to 'bin/acme/srtool_jira.py')
| -rwxr-xr-x | bin/acme/srtool_jira.py | 838 |
1 files changed, 838 insertions, 0 deletions
diff --git a/bin/acme/srtool_jira.py b/bin/acme/srtool_jira.py new file mode 100755 index 00000000..4fd9d7bb --- /dev/null +++ b/bin/acme/srtool_jira.py @@ -0,0 +1,838 @@ +#!/usr/bin/env python3 +# +# ex:ts=4:sw=4:sts=4:et +# -*- tab-width: 4; c-basic-offset: 4; indent-tabs-mode: nil -*- +# +# Security Response Tool Commandline Tool +# +# Copyright (C) 2018 Wind River Systems +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License version 2 as +# published by the Free Software Foundation. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with this program; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +### Usage Examples (run from top level directory) +# Updating Jira Issues: ./bin/srtool_jira.py -U + + +### +### THIS IS A SAMPLE JIRA INTEGRATION SCRIPT FOR +### MANAGING AN ORGANIZATION'S SRTOOL INTEGRATION +### +### INSTALLATION INSTRUCTIONS AND DOCUMENATION OF THE JIRA PYTHON +### LIBRARIES CAN BE FOUND HERE: +### https://jira.readthedocs.io +### + + +import os +import sys +import re +import csv +import xml.etree.ElementTree as ET +import argparse +import sqlite3 +import subprocess +import json +import urllib +from jira import JIRA +from time import sleep +from datetime import datetime + +# load the srt.sqlite schema indexes +dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__))) +sys.path.insert(0, dir_path) +from common.srt_schema import ORM + +try: + from datetime import datetime, date + from urllib.request import urlopen, URLError + from urllib.parse import urlparse +except ImportError: + from urllib2 import urlopen, URLError + from urlparse import urlparse + +srtDbName = 'srt.sqlite' +srtErrorLog = 'srt_errors.txt' + +### +### THESE ARE SAMPLE JIRA INTEGRATION LINKS: +### The 'production' link is to the main Jira server +### The 'test' link is to a test Jira server, for example to pre-test defect creation +### The 'browse' link is how to format the Jira defect lookup link that is passed to the SRTool users +### + +# Jira constants +JIRA_PRODUCTION_LINK = 'https://jira.acme.com' +JIRA_BROWSE_LINK_FORMAT = 'http://jira.acme.com/browse/%s' +JIRA_TESTSERVER_LINK = 'https://jira.acmetest.com' + +### +### THESE ARE SAMPLE JIRA CUSTOM FIELD LINKS: +### These are example links that you can use to define and populate the respective fields for the SRTool +### These are not defined in the standard Jira schema, and would presumably be custom added +### You can extend and cusomize the Jira integration to SRTool by added additional custom fields using this model +### The two indicated fields below are part of the SRTool design. They can be present as empty strings if they +### do not exist in your schema: +### The 'PUBLISHED' field is used to indicate if and when a Jira defect has been published to the customers +### The 'FIX_VERSION' field is used to indicate which version/release/update/service pack of the product has the fix +### + +# Custom Jira fields +JIRA_PUBLISHED_FIELD = 'customfield_10010' ### REPLACE WITH YOUR CUSTOM FIELD VALUE +JIRA_FIX_VERSION_FIELD = 'customfield_10011' ### REPLACE WITH YOUR CUSTOM FIELD VALUE +# ... + +################################# +# Helper methods +# + +verbose = False + +def debugMsg(msg): + if verbose: + print(msg) + +overrides = {} + +def set_override(key,value=None): + if not value is None: + overrides[key] = value + elif key in os.environ.keys(): + overrides[key] = 'yes' if os.environ[key].startswith('1') else 'no' + else: + overrides[key] = 'no' + if 'yes' == overrides[key]: + print("OVERRIDE: %s = %s" % (key,overrides[key])) + +def get_override(key): + if key in overrides.keys(): + return 'yes' == overrides[key] + return False + +def srt_error_log(msg): + f1=open(srtErrorLog, 'a') + f1.write("|" + msg + "|\n" ) + f1.close() + +def get_tag_key(tag,key): + d = json.loads(tag) + return d[key] + +################################# +# class to hold fields of a Jira issues +# + +class Defect: + project = '' + product_id = '' + + def __init__(self,*args, **kwargs): + self.reset() + + # reset all but persistent project info + def reset(self): + self.id = -1 + self.name = '' + self.summary = '' + self.url = '' + self.priority = -1 + self.status = '' + self.resolution = 'Unresolved' + self.publish = '' + + # RCPL + self.release_version = '' + + self.date_created = '' + self.date_updated = '' + + # extra fields + self.cve_status = '' + self.vi_status = '' + self.vi_outcome = '' + +################################# +# Import Jira states +# +# if too slow, change to check update times and ignore those that need nothing +# can also move parsing JSON so that it doesnt happen if record is up to date + +def do_update_jira(): + try: + jira = JIRA(JIRA_PRODUCTION_LINK, auth=(srt_user, srt_passwd)) + except Exception as e: + print("CONNECTION TO JIRA FAILED") + return + + conn = sqlite3.connect(srtDbName) + c = conn.cursor() + + today = datetime.today() + weeknum = today.strftime("%W") + weekday = today.isoweekday() + + log = open("./update_logs/update_jira_log_%s_%s.txt" % (weeknum, weekday), "a") + + log.write("BEGINNING JIRA UPDATES\n") + pre_update_time = datetime.now() + + products = c.execute('''SELECT * FROM orm_product''').fetchall() + for i,product in enumerate(products): +# print("FOO1:%s,%s" % (product[ORM.PRODUCT_DEFECT_TAGS],get_tag_key(product[ORM.PRODUCT_DEFECT_TAGS],'key'))) + product_defect_prefix = get_tag_key(product[ORM.PRODUCT_DEFECT_TAGS],'key') + + if get_override('SRTDBG_MINIMAL_DB') and (i > 1): + break + + #specify which fields to get in order to speed up request! + #print("\tupdating ... " + product[ORM.PRODUCT_NAME] + " " + product[ORM.PRODUCT_VERSION] + " " + product[ORM.PRODUCT_PROFILE] + "\tloading " + spinner[block_num % 3], end='\r', flush=True) + log.write("\tUPDATING ... " + product[ORM.PRODUCT_NAME] + " " + product[ORM.PRODUCT_VERSION] + " " + product[ORM.PRODUCT_PROFILE] + "\n") + + block_size = 500 + block_num = 0 + spinner = [' ', '. ', '.. ', '...'] + while True: + print("\tloading" + spinner[block_num % 4] + "\t" + product[ORM.PRODUCT_NAME] + " " + product[ORM.PRODUCT_VERSION] + " " + product[ORM.PRODUCT_PROFILE], end='\r', flush=True) +# print("\tloading" + spinner[block_num % 4] + "\t" + product[ORM.PRODUCT_NAME] + " " + product[ORM.PRODUCT_VERSION] + " " + product[ORM.PRODUCT_PROFILE], flush=True) + start_idx = block_num*block_size + #searches current project's bug issues that contain "cve" in their text + issues = jira.search_issues('project=%s AND text ~ "cve*" AND type = Bug' % product_defect_prefix, start_idx, block_size, False, fields='key,summary,priority,status,resolution,project,updated,created,%s,%s' % (JIRA_PUBLISHED_FIELD, JIRA_FIX_VERSION_FIELD)) + if len(issues) == 0: + # Retrieve issues until there are no more to come + break + # Development support + block_num += 1 + update_project_issues(product, issues, conn, log) + conn.commit() #commit to db after each block + print("\tfinished \t" + product[ORM.PRODUCT_NAME] + " " + product[ORM.PRODUCT_VERSION] + " " + product[ORM.PRODUCT_PROFILE], flush=True) + conn.commit() + log.write("began updates: %s\n" % str(pre_update_time)) + log.write("finished updates: %s\n" % str(datetime.now())) + log.write("=============================================================================\n") + log.write("\n") + + # Reset datasource's update_time as today + sql = "UPDATE orm_datasource SET update_time=?,lastModifiedDate=? WHERE data='jira_status'" + date_string = datetime.today().strftime('%Y-%m-%d %H:%M:%S') + c.execute(sql, (date_string,date_string,) ) + conn.commit() + + c.close() + conn.close() + +#############################################################################3 +### Update the defect status from the Jira database +### + +def new_vulnerability_name(cur): + sql = "SELECT * FROM orm_srtsetting WHERE name='current_vulnerability_index'" + cvi = cur.execute(sql).fetchone() + if not cvi: + index = 100 + sql = '''INSERT INTO orm_srtsetting (name, helptext, value) VALUES (?,?,?)''' + cur.execute(sql, ('current_vulnerability_index', '', index)) + else: + index = int(cvi[ORM.SRTSETTING_VALUE]) + 1 + sql = '''UPDATE orm_srtsetting SET value=? WHERE id = ?''' + cur.execute(sql, (index, cvi[ORM.SRTSETTING_ID])) + return "VUL-%05d" % index + +def new_investigation_name(cur): + sql = "SELECT * FROM orm_srtsetting WHERE name='current_investigation_index'" + cvi = cur.execute(sql).fetchone() + if not cvi: + index = 100 + sql = '''INSERT INTO orm_srtsetting (name, helptext, value) VALUES (?,?,?)''' + cur.execute(sql, ('current_investigation_index', '', index)) + else: + index = int(cvi[ORM.SRTSETTING_VALUE]) + 1 + sql = '''UPDATE orm_srtsetting SET value=? WHERE id = ?''' + cur.execute(sql, (index, cvi[ORM.SRTSETTING_ID])) + return "INV-%05d" % index + +# +# Translate Jira values to SRTool values +# + +def translate_priority(j,p): + NONE = 0 + MINOR = 1 + LOW = 2 + MEDIUM = 3 + HIGH = 4 + Priority = ( + (NONE, 'None'), + (MINOR, 'P4'), + (LOW, 'P3'), + (MEDIUM, 'P2'), + (HIGH, 'P1'), + ) + + for i in range(len(Priority)): + if p == Priority[i][1]: + return str(Priority[i][0]) + print("ERROR: unknown priority string '%s=%s'" % (j,p)) + log.write("ERROR: unknown priority string '%s=%s'" % (j,p)) + return '0' + +def translate_status(j,s): + OPEN = 0 + IN_PROGRESS = 1 + ON_HOLD = 2 + CHECKED_IN = 3 + RESOLVED = 4 + CLOSED = 5 + Status = ( + (OPEN, 'Open'), + (IN_PROGRESS, 'In progress'), + (ON_HOLD, 'On Hold'), + (CHECKED_IN, 'Checked In'), + (RESOLVED, 'Resolved'), + (CLOSED, 'Closed'), + ) + for i in range(len(Status)): + if s == Status[i][1]: + return str(Status[i][0]) + print("ERROR: unknown status string '%s=%s'" % (j,s)) + log.write("ERROR: unknown status string '%s=%s'" % (j,s)) + return '0' + +def translate_resolution(j,r): + Resolution = ( + (ORM.DEFECT_UNRESOLVED, 'Unresolved', ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_OPEN), + (ORM.DEFECT_RESOLVED, 'Resolved', ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_FIXED), + (ORM.DEFECT_FIXED, 'Fixed', ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_FIXED), + (ORM.DEFECT_WILL_NOT_FIX, 'Won\'t Fix', ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_NOT_FIX), + (ORM.DEFECT_WITHDRAWN, 'Withdrawn', ORM.STATUS_NOT_VULNERABLE,ORM.STATUS_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_REJECTED, 'Rejected', ORM.STATUS_NOT_VULNERABLE,ORM.STATUS_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_DUPLICATE, 'Duplicate', ORM.STATUS_NOT_VULNERABLE,ORM.STATUS_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_NOT_APPLICABLE, 'Not Applicable', ORM.STATUS_NOT_VULNERABLE,ORM.STATUS_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_REPLACED_BY_REQUIREMENT, 'Replaced By Requirement',ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_CANNOT_REPRODUCE, 'Cannot Reproduce', ORM.STATUS_NOT_VULNERABLE,ORM.STATUS_VULNERABLE,ORM.OUTCOME_CLOSED), + (ORM.DEFECT_DONE, 'Done', ORM.STATUS_VULNERABLE,ORM.STATUS_NOT_VULNERABLE,ORM.OUTCOME_CLOSED), + ) + for i in range(len(Resolution)): + if r == Resolution[i][1]: + return Resolution[i][0],Resolution[i][2],Resolution[i][3],Resolution[i][4], + print("ERROR: unknown resolution string '%s=%s'" % (j,r)) + log.write("ERROR: unknown resolution string '%s=%s'" % (j,r)) + return 0,0,0,0 + +# +# Update the defect status from the Jira database +# + +#handles updating a list of issues for a single product/project +#DOES NOT CALL COMMIT (should change this?) +def update_project_issues(project, issues, conn, log): + global force_update + + #CREATE TABLE "orm_defect" ( + #0 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT + #1 "name" varchar(50) NOT NULL + #2 "summary" text NOT NULL + #3 "url" text NOT NULL + #4 "priority" integer NOT NULL + #5 "status" integer NOT NULL + #6 "resolution" integer NOT NULL + #7 "publish" text NOT NULL + #8 "release_version" varchar(50) NOT NULL + #9 "product_id" integer NULL REFERENCES "orm_product" ("id") + #10 "date_created" varchar(50) NOT NULL + #11 "date_updated" varchar(50) NOT NULL); + + USER_SRTOOL_ID = "SRTool" + + d = Defect() + d.project = project[ORM.PRODUCT_NAME] + " " + project[ORM.PRODUCT_VERSION] + " " + project[ORM.PRODUCT_PROFILE] + d.product_id = project[ORM.PRODUCT_ID] + + cve_regex = re.compile("CVE-\d+-\d+") + srtool_today = datetime.today().strftime('%Y-%m-%d') + + c = conn.cursor() + d_cursor = conn.cursor() + for i,issue in enumerate(issues): + + if get_override('SRTDBG_MINIMAL_DB') and (i > 10): + break + + issue_json = issue.raw + d.reset() + d.name = issue_json['key'] + d.date_updated = issue_json['fields']['updated'] + d.date_created = issue_json['fields']['created'] + d.summary = issue_json['fields']['summary'] + d.url = JIRA_BROWSE_LINK_FORMAT % d.name + d.priority = translate_priority(d.name,issue_json['fields']['priority']['name']) + d.status = translate_status(d.name,issue_json['fields']['status']['name']) + + if issue_json['fields']['resolution'] is not None: + d.resolution,cve_status,vi_status,vi_outcome = translate_resolution(d.name,issue_json['fields']['resolution']['name']) + else: + d.resolution,cve_status,vi_status,vi_outcome = translate_resolution(d.name,'Unresolved') + if JIRA_PUBLISHED_FIELD in issue_json['fields'] and issue_json['fields'][JIRA_PUBLISHED_FIELD] is not None: + d.publish = issue_json['fields'][JIRA_PUBLISHED_FIELD]['value'] + else: + d.publish = '' + if JIRA_FIX_VERSION_FIELD in issue_json['fields'] and issue_json['fields'][JIRA_FIX_VERSION_FIELD] is not None: + d.release_version = issue_json['fields'][JIRA_FIX_VERSION_FIELD]['name'] + else: + d.release_version = '' + + sql = "SELECT * FROM orm_defect WHERE name='%s'" % d.name + defect = c.execute(sql).fetchone() + + #if defect does not exists then create it, if defect is out of date then update the database record, else ignore + if defect is None: + log.write("\tINSERTING %s\n" % d.name) + sql = '''INSERT INTO orm_defect (name, summary, url, priority, status, resolution, publish, release_version, product_id, date_created, date_updated, srt_updated) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)''' + c.execute(sql, (d.name, d.summary, d.url, d.priority, d.status, d.resolution, str(d.publish), d.release_version, d.product_id, d.date_created, d.date_updated, datetime.now())) + # Get the new id + sql = "SELECT * FROM orm_defect WHERE name='%s'" % d.name + defect = c.execute(sql).fetchone() + defect_id = defect[ORM.DEFECT_ID] + elif force_update or (d.date_updated > defect[ORM.DEFECT_DATE_UPDATED]): + log.write("\tUPDATING %s\n" % d.name) + sql = '''UPDATE orm_defect SET summary=?, priority=?, status=?, resolution=?, publish=?, release_version=?, date_updated=? WHERE id = ?''' + c.execute(sql, (d.summary, d.priority, d.status, d.resolution, d.publish, d.release_version, d.date_updated, defect[ORM.DEFECT_ID])) + defect_id = defect[ORM.DEFECT_ID] + else: + log.write("\tSKIPPING %s\n" % d.name) + continue + + # + # Update CVE -> Vulnerability -> Investigation -> this Defect chain + # + + # V/I priority and status minimum from defect + # Add audit lines + + # Find parent CVE + m = cve_regex.search(d.summary) + if m: + cve_name = m.group(0) + else: + print("WARNING: Missing CVE in defect name '%s'" % (d.summary)) + continue + sql = "SELECT * FROM orm_cve WHERE name='%s'" % cve_name + cve = c.execute(sql).fetchone() + if not cve: + # create the placeholder CVE + log.write("\tINSERTING CVE for %s\n" % cve_name) + print("INSERTING CVE for %s,%s" % (cve_name,d.name)) + + try: + a = cve_name.split('-') + cve_name_sort = '%s-%s-%07d' % (a[0],a[1],int(a[2])) + except: + cve_name_sort = cve.name + + sql = ''' INSERT into orm_cve (name, name_sort, priority, status, comments, comments_private, cve_data_type, cve_data_format, cve_data_version, public, publish_state, publish_date, description, publishedDate, lastModifiedDate, recommend, recommend_list, cvssV3_baseScore, cvssV3_baseSeverity, cvssV2_baseScore, cvssV2_severity, packages, srt_updated) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''' + c.execute(sql, (cve_name, cve_name_sort, d.priority, cve_status, '', '', '', '', '', 1, 0, '', 'Created from defect %s' % d.name, '', '', 0, '', '', '', '', '', '', datetime.now())) + + # Find the new id + print("FINDING CVE ID for %s\n" % cve_name) + c.execute("SELECT * FROM orm_cve where name = '%s'" % cve_name) + cve = c.fetchone() + c_id = cve[ORM.CVE_ID] + # Also create CVE history entry + sql = '''INSERT INTO orm_cvehistory (cve_id, comment, date, author) VALUES (?,?,?,?)''' + c.execute(sql, (c_id,'Created from defect %s' % d.name,srtool_today,USER_SRTOOL_ID)) + else: + c_id = cve[ORM.CVE_ID] + + # Find CVE's vulnerability, else create + d_cursor.execute("SELECT * FROM orm_cvetovulnerablility where cve_id = '%s'" % c_id) + c2v = d_cursor.fetchone() + if not c2v: + # Create Vulnerability + v_name = new_vulnerability_name(d_cursor) + log.write("\tINSERTING VULNERABILITY %s for %s\n" % (v_name,cve_name)) + print("INSERTING VULNERABILITY for (%s,%s)" % (cve_name,v_name)) + sql = '''INSERT INTO orm_vulnerability (name,description,cve_primary_name,public,comments,comments_private,status,outcome,priority) VALUES (?,?,?,?,?,?,?,?,?)''' + c.execute(sql, (v_name, cve[ORM.CVE_DESCRIPTION], cve_name, True, 'Created from defect %s' % d.name, '', vi_status, vi_outcome ,d.priority)) + # Find the new id + d_cursor.execute("SELECT * FROM orm_vulnerability where name = '%s'" % v_name) + v = d_cursor.fetchone() + v_id = v[ORM.VULNERABILITY_ID] + # Also create CVE to Vulnerability + sql = '''INSERT INTO orm_cvetovulnerablility (vulnerability_id, cve_id) VALUES (?,?)''' + c.execute(sql, (v_id,c_id)) + # Also create Vulnerability history entry + sql = '''INSERT INTO orm_vulnerabilityhistory (vulnerability_id, comment, date, author) VALUES (?,?,?,?)''' + c.execute(sql, (v_id,'Created from defect %s' % d.name,srtool_today,USER_SRTOOL_ID)) + else: + print("FOUND VULNERABILITY ID for %s" % (cve_name)) + v_id = c2v[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID] + + # Find CVE's investigation, else create + sql = "SELECT * FROM orm_investigation where vulnerability_id = '%s' AND product_id = '%s';" % (v_id,d.product_id) +# print("I_TEST:%s" % sql) + d_cursor.execute(sql) + investigation = d_cursor.fetchone() + if not investigation: + # Create Investigation + i_name = new_investigation_name(d_cursor) + log.write("\tINSERTING INVESTIGATION for %s\n" % cve_name) + print("INSERTING INVESTIGATION for %s,%s" % (i_name,d.name)) + sql = '''INSERT INTO orm_investigation (name,vulnerability_id,product_id,public,comments,comments_private,status,outcome,priority) VALUES (?,?,?,?,?,?,?,?,?)''' + c.execute(sql, (i_name, v_id, d.product_id, True, 'Created from defect %s' % d.name, '', vi_status, vi_outcome, d.priority)) + # Find the new id + d_cursor.execute("SELECT * FROM orm_investigation where name = '%s'" % i_name) + investigation = d_cursor.fetchone() + i_id = investigation[ORM.INVESTIGATION_ID] + # Also create Vulnerability to Investigation + sql = '''INSERT INTO orm_vulnerabilitytoinvestigation (vulnerability_id, investigation_id) VALUES (?,?)''' + c.execute(sql, (v_id,i_id)) + # Also create Investigation history entry + sql = '''INSERT INTO orm_investigationhistory (investigation_id, comment, date, author) VALUES (?,?,?,?)''' + c.execute(sql, (i_id,'Created from defect %s' % d.name,srtool_today,USER_SRTOOL_ID)) + else: + print("FOUND INVESTIGATION ID for %s" % (cve_name)) + i_id = investigation[ORM.INVESTIGATION_ID] + + # Add this defect to the investigation + d_cursor.execute("SELECT * FROM orm_investigationtodefect where investigation_id = '%s' and product_id = '%s' and defect_id = '%s'" % (i_id,d.product_id,defect_id)) + i2d = d_cursor.fetchone() + if not i2d: + # Create Investigation + i_name = new_investigation_name(d_cursor) + log.write("\tINSERTING INVESTIGATION to DEFECT for %s\n" % i_name) + sql = '''INSERT INTO orm_investigationtodefect (investigation_id, product_id, defect_id) VALUES (?,?,?)''' + c.execute(sql, (i_id,d.product_id,defect_id)) + + #print("=========================================================================================\n") + #print("\n") + c.close() + +################################# +# Jira list update +# + +def jira_update_list(jira_list): + try: + jira = JIRA(JIRA_PRODUCTION_LINK, auth=(srt_user, srt_passwd)) + except Exception as e: + print("CONNECTION TO JIRA FAILED") + return + + conn = sqlite3.connect(srtDbName) + c = conn.cursor() + + products = c.execute('''SELECT * FROM orm_product''').fetchall() + log = sys.stdout # open("./update_logs/update_jira_log_%s_%s.txt" % (weeknum, weekday), "a") + + for jira_name in jira_list.split(','): + print("Updating:\t" + jira_name, flush=True) + # find the matching parent project + for i,product in enumerate(products): + product_defect_prefix = get_tag_key(product[ORM.PRODUCT_DEFECT_TAGS],'key') + print("\tfrom:\t%s %s %s %s" %(product[ORM.PRODUCT_NAME],product[ORM.PRODUCT_VERSION],product[ORM.PRODUCT_PROFILE],product_defect_prefix), flush=True) + block_size = 500 + block_num = 0 + while True: + start_idx = block_num*block_size + #searches current project's bug issues that contain "cve" in their text + issues = jira.search_issues('project=%s AND text ~ "%s" AND type = Bug' % (product_defect_prefix,jira_name), start_idx, block_size, False, fields='key,summary,priority,status,resolution,project,updated,created,%s,%s' % (JIRA_PUBLISHED_FIELD, JIRA_FIX_VERSION_FIELD)) #project argument could be better written I guess :) + if len(issues) == 0: + # Retrieve issues until there are no more to come + break + # Development support + block_num += 1 + update_project_issues(product, issues, conn, log) + conn.commit() #commit to db after each block + +################################# +# Jira Enhancement Requests +# + +#Gets all JIRA Enhancement Requests for the specified project +def get_jira_er(project_prefix): + #log.write("GETTING ENHANCEMENT REQUESTS FOR " + project_prefix) + print ("GETTING ENHANCEMENT REQUESTS FOR " + project_prefix) + try: + jira = JIRA(JIRA_PRODUCTION_LINK', auth=(srt_user, srt_passwd)) + except Exception as e: + print("CONNECTION TO JIRA FAILED") + return + + block_size = 100 + block_num = 0 + while True: + start_idx = block_num*block_size + #searches current project's bug issues that contain "cve" in their text + issues = jira.search_issues('project=%s AND type = "Enhancement Request"' % project_prefix, start_idx, block_size, False) #project argument could be better written I guess :) + if len(issues) == 0: + # Retrieve issues until there are no more to come + break + block_num += 1 + for issue in issues: + print (issue.raw['key']) + +################################# +# Jira add to investigation +# + +def jira_add_to_defect_db(jira_name): + print("Jira_name=%s" % (jira_name)) + jira_name = jira_name.strip().upper() + + #try connecting to jira + try: + jira = JIRA(JIRA_PRODUCTION_LINK, auth=(srt_user, srt_passwd)) + conn = sqlite3.connect(srtDbName) + c = conn.cursor() + except Exception as e: + print("xhr_investigation_commit:CONNECTION TO JIRA FAILED:(%s)\n" % e, file=sys.stderr) + return 1 + + #Import the issue into the SRTool + try: + issue = jira.issue(jira_name, fields='key,summary,priority,status,resolution,project,updated,created,%s,%s' % (JIRA_PUBLISHED_FIELD, JIRA_FIX_VERSION_FIELD)) + except Exception as e: + print("ERROR:key '%s' does not exist(%s)" % (jira_name,e), file=sys.stderr) + return 1 + + d = Defect() + try: + issue_json = issue.raw + d.name = issue_json['key'] + d.date_updated = issue_json['fields']['updated'] + d.date_created = issue_json['fields']['created'] + d.summary = issue_json['fields']['summary'] + d.url = JIRA_BROWSE_LINK_FORMAT % d.name + d.priority = translate_priority(d.name,issue_json['fields']['priority']['name']) + d.status = translate_status(d.name,issue_json['fields']['status']['name']) + + + if issue_json['fields']['resolution'] is not None: + d.resolution,d.cve_status,d.vi_status,d.vi_outcome = translate_resolution(d.name,issue_json['fields']['resolution']['name']) + else: + d.resolution,d.cve_status,d.vi_status,d.vi_outcome = translate_resolution(d.name,'Unresolved') + if JIRA_PUBLISHED_FIELD in issue_json['fields'] and issue_json['fields'][JIRA_PUBLISHED_FIELD] is not None: + d.publish = issue_json['fields'][JIRA_PUBLISHED_FIELD]['value'] + else: + d.publish='' + if JIRA_FIX_VERSION_FIELD in issue_json['fields'] and issue_json['fields'][JIRA_FIX_VERSION_FIELD] is not None: + d.release_version = issue_json['fields'][JIRA_FIX_VERSION_FIELD]['name'] + else: + d.release_version = '' + + # Get the product ID + products = c.execute('''SELECT * FROM orm_product''').fetchall() + d.product_id = 1 + for product in products: + if d.name.startswith(get_tag_key(product[ORM.PRODUCT_DEFECT_TAGS],'key')): + d.product_id = product[ORM.PRODUCT_ID] + break + + #log.write("\tINSERTING %s\n" % d.name) + sql = '''INSERT INTO orm_defect (name, summary, url, priority, status, resolution, publish, release_version, product_id, date_created, date_updated) VALUES (?,?,?,?,?,?,?,?,?,?,?)''' + c.execute(sql, (d.name, d.summary, d.url, d.priority, d.status, d.resolution, str(d.publish), d.release_version, d.product_id, d.date_created, d.date_updated)) + conn.commit() + c.close() + conn.close() + except Exception as e: + print("ERROR:could not find/import defect(%s)" % e, file=sys.stderr) + return 1 + +################################# +# jira_del_from_defect_db +# + +# Not yet implemented +def jira_del_from_defect_db(jira_name): + pass + +################################# +# New defect +# + +### +### You will need to fill in any required custom fields required +### to create new organization defects in Jira +### + +# Use "jiratest" for development testing +IS_TEST = True + +def jira_new_defect(product_key,summary,description,priority,components,link): + + if IS_TEST: + # Test URL + jira_url = JIRA_TESTSERVER_LINK + else: + # Production URL + jira_url = JIRA_PRODUCTION_LINK + + # Connect to Jira server + try: + jira = JIRA(jira_url, auth=(srt_user, srt_passwd)) + except Exception as e: + print("CONNECTION TO JIRA FAILED") + return + + conn = sqlite3.connect(srtDbName) + c = conn.cursor() + + # append the jira link to description + description += "\n\n\n%s" % link + + #print("JIRA_NEW_DEFECT:%s,%s,%s" % (product_key,summary,description)) + + jira_components=list() + for component in components.split(' '): + jira_components.append({'name' : component}) + + issue_dict = { + 'project': {'key': product_key}, + 'summary': summary, + 'description': description, + 'issuetype': {'name': 'Bug'}, + + 'priority': {'name': priority}, + 'components': jira_components, # Components by name + 'assignee': {"name":SRT_USER }, # assign to the SRTool developer Jira account +### 'customfield_11000': 'unknown', # EXAMPLE: "Found In Versions" = "unknown" +### 'customfield_11001': {"value":"Review"}, # EXAMPLE: "Where Found" = "Review" + } + + if True: + new_issue = jira.create_issue(fields=issue_dict) + else: + print("CREATE:%s" % issue_dict) + new_issue = '%s-%s' % (product_key,datetime.now().strftime('%H%M%S')) + + new_issue = str(new_issue.key) + if not new_issue.startswith(product_key): + print("ERROR:%s" % new_issue) + else: + print("CREATED:%s,%s/browse/%s" % (new_issue,jira_url,new_issue)) + + +def get_projects(): + + try: + jira = JIRA(JIRA_PRODUCTION_LINK, auth=(srt_user, srt_passwd)) + except Exception as e: + print("CONNECTION TO JIRA FAILED") + return + + # Get all projects viewable by anonymous users. + projects = jira.projects() + print("Projects:%s" % projects) + + +################################# +# Init/Update from Jira status +# + +def update_jira(is_init): + if get_override('SRTDBG_SKIP_DEFECT_IMPORT'): + print("...Skipping Defect import") + exit(0) + try: + print("BEGINNING JIRA UPDATES PLEASE WAIT ... this can take some time") + do_update_jira() + master_log.write("SRTOOL:%s:DEFECT TABLE & JIRA ISSUES:\t\t\t...\t\t\tUPDATED\n" % (date.today())) + print("DATABASE UPDATE FINISHED\n") + except Exception as e: + master_log.write("SRTOOL:%s:DEFECT TABLE & JIRA ISSUES:\t\t\t...\t\t\tFAILED ... %s\n" % (date.today(), e)) + print("DATABASE UPDATES FAILED ... %s" % e) + +################################# +# main loop +# + +def main(argv): + global force_update + global verbose + global master_log + global srt_user + global srt_passwd + + parser = argparse.ArgumentParser(description='srtool_jira.py: Manage the SRTool to Jira connection') + parser.add_argument('--init-jira', '-i', action='store_const', const='init_jira', dest='command', help='Init and import Jira states and update defects') + parser.add_argument('--update-jira', '-u', action='store_const', const='update_jira', dest='command', help='Import Jira states and update defects') + parser.add_argument('--update-jira-list', '-l', dest='jira_update_list', help='List of Jira defects to update in SRTool database') + parser.add_argument('--jira_er', '-e', nargs=1, help='Query list of pending ERs under a project, review them, and assign to Rally themes') + parser.add_argument('--force', '-f', action='store_true', dest='force_update', help='Force updates') + parser.add_argument('--verbose', '-v', action='store_true', dest='verbose', help='Verbose debugging') + parser.add_argument('--user', dest='user', help='User name for Jira access') + parser.add_argument('--passwd', dest='passwd', help='User password for Jira access') + + parser.add_argument('--add', nargs=1, help='Add an existing defect to SRTool defect database') + parser.add_argument('--delete', nargs=1, help='Delete an existing defect from SRTool defect database') + parser.add_argument('--new', nargs=6, help='Create a new defect "--new <program> <summary> <description> <priority> <components> <urllink>"') + parser.add_argument('--jira-projects', '-P', action='store_const', const='jira-projects', dest='command', help='Jira projects') + + args = parser.parse_args() + + master_log = open("./update_logs/master_log.txt", "a") + + # Authorization + if args.user: + srt_user = args.user + else: + srt_user = os.environ.get('SRT_USER') + if args.passwd: + srt_passwd = args.passwd + else: + srt_passwd = os.environ.get('SRT_PASSWD') + if not srt_user or not srt_passwd: + msg = "FATAL ERROR: Missing user/password for Jira access" + print(msg) + srt_error_log(msg) + return 1 + + force_update = False + if None != args.force_update: + force_update = args.force_update + if None != args.verbose: + verbose = True + + jira_list = '' + if None != args.jira_update_list: + jira_list = args.jira_update_list + + if args.jira_er: + get_jira_er(args.jira_er[0]) + elif args.add: + jira_add_to_defect_db(args.add[0]) + elif args.delete: + jira_del_from_defect_db(args.add[0]) + elif args.new: + jira_new_defect(args.new[0],args.new[1],args.new[2],args.new[3],args.new[4],args.new[5]) + elif 'init_jira' == args.command: + update_jira(True) + elif 'update_jira' == args.command: + update_jira(False) + elif jira_list: + jira_update_list(jira_list) + elif 'jira-projects' == args.command: + get_projects() + else: + print("Command not found") + +if __name__ == '__main__': + global srtool_basepath + + if verbose: print("srtool_jira(%s)" % sys.argv[1:]) + + # fetch any environment overrides + #set_override('SRTDBG_MINIMAL_DB') + + srtool_basepath = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(sys.argv[0])))) + main(sys.argv[1:]) |