SolarWinds & Jira & Integration for Infrastructure Monitoring using Python, SQL, REST API

import json, pyodbc, requests, threading, pandas as pd, time, os
from requests.auth import HTTPBasicAuth
tic = time.perf_counter() 

#Jira Connection
url = "https://<jira-instance-url>/rest/api/2/issue/"
auth = HTTPBasicAuth("<jira_user>", "jira_user_password")
headers = { "Accept": "application/json","Content-Type": "application/json"}

#Solarwinds Database Connection
server = '<DB-INSTANCE-NAME>'         #DB Instance where Script will store Data
database = '<DB-NAME>'                 #DB Name where Script will store Data
username = '<DB-USERNAME>' 
password = '*********' 
con = 'Yes'
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
#conn.autocommit = True
cursor = conn.cursor()

def create_ticket(ai,name,ec,rnc,ltdt,sev,ot,ia,ct,cy,r,ag,o):
    """ This Function Create a Ticket in Jira Service Desk """
    cursor = conn.cursor()
    Alert_ID=ai
    summary = rnc + ' ' + name
    desc = name + '\n' + ec + '\n' + rnc
    desc = "Alert: {} \n Entity: {} \n Node: {}".format(name,ec,rnc)
    desc = f"*Alert: {name}*\n*Node: {rnc}*\n*Alert_Object: {ec}*\n*Object_Type: {ot}*\nIP_Address: {ia}\nTrigger Time: {ltdt} (UTC)\nCity: {ct}\nCountry: {cy}\nRegion: {r}\nAssignment_Group: {ag}\nOwners: {o}"
    if sev == 1:
        v_priority = "High"
    elif sev == 2:
        v_priority = "Medium"
    else:
        v_priority = "Low"
    payload = json.dumps(
        {
            "fields": 
            {
            "project":
                {
                "key": "<PROJECT-KEY>"
                },
            "summary": summary,
            "description": desc,
            "issuetype": 
                {
                "name": "Incident",
                },
            "components":
                [{"name":"Active Directory" }],
            "priority":
                {"name":v_priority},
            "assignee":
                {
                "name":"aashutosh.kumar@technicolor.com"
                }
           }
        }
    )
    response = requests.request(
    "POST",
    url,
    data=payload,
    headers=headers,
    auth=auth,
    verify=r'bundle.pem'
    )
    print('\nTicket Created:')
    print('Alert ID: ',ai,'\n',json.dumps(json.loads(response.text), sort_keys=True, indent=4, separators=(",", ": ")))    
    Ticket_No = json.loads(response.text)['key']
    update_ticket_table_sql = """insert into jira_test.Ticket_Log values ({},'{}','OPEN');commit;""".format(Alert_ID,Ticket_No)
    #print(update_ticket_table_sql)
    cursor.execute(update_ticket_table_sql)
    cursor.commit()
    cursor.close()

def resolve_ticket(ticket):
    """This Function Resolves Jira SD Ticket for Resolved Alerts"""
    cursor = conn.cursor()
    comment_url='<jira-instance-url>/rest/api/2/issue/' + ticket + '/comment'
    comment_payload = json.dumps(
        {
        "body":"Issue Detected Resolved by Solarwinds"
        }
    )
    response = requests.request("POST",comment_url,data=comment_payload,headers=headers,auth=auth,verify=r'bundle.pem')      
    url='<jira-instance-url>/rest/api/2/issue/'+ticket+'/transitions'
    payload = json.dumps(
        {
        "update": {},
        "fields": 
            {
            "resolution": 
                {
                "name": "Done"
                }
            },
        "transition":
            {
            "id": "111"
            }
        }
        )
    response = requests.request(
        "POST",
        url,
        data=payload,
        headers=headers,
        auth=auth,
        verify=r'bundle.pem'
        )
    update_ticket_table_sql = "update <DB-NAME>.ticket_log set Ticket_Status='RESOLVED' where Ticket_No='{}';".format(ticket)
    #print(update_ticket_table_sql)
    cursor.execute(update_ticket_table_sql)
    cursor.commit()
    cursor.close()
    if len(response.text) == 0:
        print(f'\n\tTicket Resolved:{ticket}\n')
    else:
        print(f'\n\tError in Updating : {ticket}')
        print(json.dumps(json.loads(response.text), sort_keys=True, indent=4, separators=(",", ": ")))

fetch_alerts="""
drop table <DB-NAME>.jira_test.Active_Alerts;
select
    AA.AlertActiveID as AlertActiveID,
    AO.AlertObjectID as AlertObjectID,AHV.Name as Name, AO.EntityCaption as EntityCaption, AO.RelatedNodeCaption as RelatedNodeCaption,AO.LastTriggeredDateTime,
    AC.ObjectType as ObjectType,AHV.Severity as Severity, 
    N.NodeID ,N.IP_Address, N.Comments ,N.City, N.Country ,N.Region ,N.Assignment_Group ,N.Owners
    into JIRA_SW_TEST.jira_test.Active_Alerts
from
    [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.AlertObjects AO
    INNER JOIN     [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.AlertActive AA ON AO.AlertObjectID    =    AA.AlertObjectID
    INNER JOIN  [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.AlertHistoryView AHV ON AA.AlertActiveID    =    AHV.AlertActiveID         
                                                                                                        AND AO.AlertObjectID    =    AHV.AlertObjectID
    INNER JOIN     [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.AlertConfigurations AC ON AC.AlertID            =    AO.AlertID
    INNER JOIN     [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.NodesCustomProperties NCP ON AO.RelatedNodeID     =    NCP.NodeID
    INNER JOIN     [<Solarwinds-DB-Server-Instance>].solarwindsorion.dbo.Nodes N ON AO.RelatedNodeID     =     N.NodeID
where 
    AHV.EventTypeWord='Triggered'
and upper(AHV.Name) not like '%HIGH RESPONSE TIME%' 
and upper(AHV.Name) not like '%HIGH PACKET LOSS%'
Order By AO.LastTriggeredDateTime desc;
commit;    
"""

process_alerts="""
DECLARE @NEWVALUE int
DECLARE @RESOLVED int
select @NEWVALUE = count(*) from jira_test.Active_Alerts where NOT EXISTS 
(select 1 from jira_test.Alert_log where jira_test.Active_Alerts.AlertActiveID = jira_test.Alert_log.AlertActiveID)
select @RESOLVED = count(*) from jira_test.Alert_log where NOT EXISTS 
(select 1 from jira_test.Active_Alerts where jira_test.Active_Alerts.AlertActiveID = jira_test.Alert_log.AlertActiveID)
IF (@NEWVALUE > 0)
BEGIN
    insert into jira_test.Alert_log select *,'NEW' from jira_test.Active_Alerts where NOT EXISTS 
    (select 1 from jira_test.Alert_log where jira_test.Active_Alerts.AlertActiveID = jira_test.Alert_log.AlertActiveID);
    commit;
END
IF (@RESOLVED > 0)
BEGIN
    update jira_test.Alert_log set Alert_Status = 'RESOLVED' where 
    AlertActiveID in (select AlertActiveID from jira_test.Alert_log where NOT EXISTS 
    (select 1 from jira_test.Active_Alerts where jira_test.Active_Alerts.AlertActiveID = jira_test.Alert_log.AlertActiveID));
    commit;
END 
"""

cursor.execute(fetch_alerts)
cursor.execute(process_alerts)
cursor.commit()
cursor.close()

#Create Ticket for New Alerts
new_alerts = pd.read_sql_query('SELECT * FROM JIRA_SW_TEST.jira_test.Alert_log where ALert_Status=\'NEW\' and  AlertActiveID not in (select Alert_ID from jira_test.Ticket_log) ',conn)
data = new_alerts[['AlertActiveID','Name','EntityCaption','RelatedNodeCaption','LastTriggeredDateTime','Severity','ObjectType','IP_Address','City','Country','Region','Assignment_Group','Owners']]
for index,row in data.iterrows():
    threading.Thread(target=create_ticket,args=[ row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12] ]).start()

#Resolve Ticket for Resolved Alerts
resolved_alerts = pd.read_sql_query('select Ticket_No from jira_test.Ticket_Log TL left join jira_test.Alert_log AL on TL.Alert_ID = AL.AlertActiveID where TL.Ticket_Status=\'OPEN\' and AL.Alert_Status=\'RESOLVED\'',conn)
for index, row in resolved_alerts.iterrows():
    threading.Thread(target=resolve_ticket,args=[row[0]]).start()
toc = time.perf_counter() 
print(f"\nDone!    Time Taken= {toc-tic:0.4f} Seconds\n")