Below Python program can be used to fetch count from PG database , for given duration.
import psycopg2 as pg
import pandas as pd
import pandas.io.sql as psql
def get_data_MSC(dbname,test_starttime,test_endtime,timezone_tenant):
timezone =timezone_tenant
Created_Column_Append =" where ((to_timestamp(created_date) AT TIME ZONE '"+timezone+"' ) >= '"+test_starttime+"' ) and ((to_timestamp(created_date) AT TIME ZONE '"+timezone+"' ) <= '"+test_endtime+"' )"
connection = pg.connect(host="XXX.xx.XX.XXX",database=dbname, user="postgres", password="XXXX")
Tenant_VendorData = pd.read_sql('select count(*) as VendorData from com_bmc_dsm_ticket_brokerin755 ' +Created_Column_Append, connection)
Tenant_Incident = pd.read_sql('select count(*) as Incident from com_bmc_dsm_ticket_brokering_l ' +Created_Column_Append, connection)
Tenant_Change = pd.read_sql('select count(*) as Change from com_bmc_dsm_ticket_brokerin747 ' +Created_Column_Append, connection)
Tenant_Problem = pd.read_sql('select count(*) as Problem from com_bmc_dsm_ticket_brokerin868 ' +Created_Column_Append, connection)
Tenant_Worklog = pd.read_sql('select count(*) as Worklog from com_bmc_dsm_ticket_brokerin756 '+ Created_Column_Append, connection)
Tenant_ActiveVendorTickets = pd.read_sql('select count(*) as ActiveVendorTickets from com_bmc_dsm_ticket_brokerin760 '+ Created_Column_Append, connection)
Tenant_BrokeredIncidents = pd.read_sql('select count(*) as BrokeredIncidents from com_bmc_dsm_ticket_brokerin762 '+ Created_Column_Append, connection)
Tenant_FlowTransaction = pd.read_sql('select count(*) as FlowTransaction from com_bmc_dsm_ticket_brokerin746 '+ Created_Column_Append, connection)
connection.close()
print(" ##### Tenant ###### ",dbname)
print(Tenant_VendorData.columns[0],Tenant_VendorData.iat[0,0])
print(Tenant_Incident.columns[0],Tenant_Incident.iat[0,0])
print(Tenant_Change.columns[0],Tenant_Change.iat[0,0])
print(Tenant_Problem.columns[0],Tenant_Problem.iat[0,0])
print(Tenant_Worklog.columns[0],Tenant_Worklog.iat[0,0])
print(Tenant_ActiveVendorTickets.columns[0],Tenant_ActiveVendorTickets.iat[0,0])
print(Tenant_BrokeredIncidents.columns[0],Tenant_BrokeredIncidents.iat[0,0])
print(Tenant_FlowTransaction.columns[0],Tenant_FlowTransaction.iat[0,0])
print("\n",)
def get_data_JIRA(dbname,test_starttime,test_endtime,timezone_jira):
Created_Column_Append =" where created >= '"+test_starttime+""+timezone_jira+"' and created <= '"+test_endtime+""+timezone_jira+"'"
connection = pg.connect(host="XXX.xx.XX.XXX",database=dbname, user="postgres", password="postgres")
Jia_Issues = pd.read_sql('select count(*) as Jia_Issues from public.jiraissue ' +Created_Column_Append , connection)
connection.close()
print(" ##### Jira ###### ",dbname)
print(Jia_Issues.columns[0],Jia_Issues.iat[0,0])
print("\n",)
if __name__ == '__main__':
test_starttime ="2018-12-05 02:40:00"
test_endtime ="2018-12-05 03:50:00"
timezone_tenant ="PST"
timezone_jira ="-08"
get_data_MSC("tenant1",test_starttime,test_endtime,timezone_tenant)
get_data_MSC("tenant2",test_starttime,test_endtime,timezone_tenant)
get_data_MSC("tenant3",test_starttime,test_endtime,timezone_tenant)
get_data_JIRA("jira",test_starttime,test_endtime,timezone_jira)
Below Python program can be used to fetch count from PG database for given item.
import psycopg2 as pg
import pandas as pd
import pandas.io.sql as psql
def get_data(dbname):
connection = pg.connect(host="XXX.xx.XX.XXX",database=dbname, user="postgres", password="XXXX")
Tenant_VendorData = pd.read_sql('select count(*) as VendorData from com_bmc_dsm_ticket_brokerin755', connection)
Tenant_Incident = pd.read_sql('select count(*) as Incident from com_bmc_dsm_ticket_brokering_l', connection)
Tenant_Change = pd.read_sql('select count(*) as Change from com_bmc_dsm_ticket_brokerin747', connection)
Tenant_Problem = pd.read_sql('select count(*) as Problem from com_bmc_dsm_ticket_brokerin868 ' , connection)
Tenant_Worklog = pd.read_sql('select count(*) as Worklog from com_bmc_dsm_ticket_brokerin756', connection)
Tenant_ActiveVendorTickets = pd.read_sql('select count(*) as Tenant_ActiveVendorTickets from com_bmc_dsm_ticket_brokerin760', connection)
Tenant_BrokeredIncidents = pd.read_sql('select count(*) as BrokeredIncidents from com_bmc_dsm_ticket_brokerin762', connection)
Tenant_FlowTransaction = pd.read_sql('select count(*) as FlowTransaction from com_bmc_dsm_ticket_brokerin746', connection)
connection.close()
print(" ##### Tenant ###### ",dbname)
print(Tenant_VendorData.columns[0],Tenant_VendorData.iat[0,0])
print(Tenant_Incident.columns[0],Tenant_Incident.iat[0,0])
print(Tenant_Change.columns[0],Tenant_Change.iat[0,0])
print(Tenant_Problem.columns[0],Tenant_Problem.iat[0,0])
print(Tenant_Worklog.columns[0],Tenant_Worklog.iat[0,0])
print(Tenant_ActiveVendorTickets.columns[0],Tenant_ActiveVendorTickets.iat[0,0])
print(Tenant_BrokeredIncidents.columns[0],Tenant_BrokeredIncidents.iat[0,0])
print(Tenant_FlowTransaction.columns[0],Tenant_FlowTransaction.iat[0,0])
print("\n",)
def get_data_JIRA(dbname):
connection = pg.connect(host="172.19.79.231",database=dbname, user="postgres", password="postgres")
Jia_Issues = pd.read_sql('select count(*) as Jia_Issues from public.jiraissue ' , connection)
connection.close()
print(" ##### Jira ###### ",dbname)
print(Jia_Issues.columns[0],Jia_Issues.iat[0,0])
print("\n",)
def get_data_JIRA_Project(dbname,projectid):
connection = pg.connect(host="XXX.xx.XX.XXX",database=dbname, user="postgres", password="postgres")
Jia_Issues = pd.read_sql('select count(*) as Jia_Issues from public.jiraissue where project ='+projectid , connection)
connection.close()
print(" ##### Jira ###### ",projectid)
print(Jia_Issues.columns[0],Jia_Issues.iat[0,0])
print("\n",)
if __name__ == '__main__':
get_data("tenant1")
get_data("tenant2")
get_data("tenant3")
get_data_JIRA_Project("jira","10306") # projectid AUG
get_data_JIRA_Project("jira","10307") # projectid AUG
get_data_JIRA_Project("jira","10308") # projectid AUG
get_data_JIRA("jira")
Comments