import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_coreReport) res = fD.querySelect( typeConn='source', querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0], 'pool': rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source', modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() # fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse= xCS.connServ52_neHuawei) # res = fD.querySelect(typeConn='source',querySource="select time, ne, attach, total from V_Kpi_HW_HR_RT where total > 0 and time >='2020-02-16'" ) # for rx in res: # datax = {'time': rx[0],'msc':rx[1], 'attach': rx[2], 'total': rx[3], 'system':'huawei'} # dfUseKpi = dfUseKpi.append(datax, ignore_index=True) # fD.setConnection(typeConn='source',modeConnOnOff='off') fD.setConnection(typeConn='target',
import configServer as xCS import dbFunction as fD import csv fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServEnixCs) res = fD.querySelect(typeConn='source', querySource="""SELECT OSS_ID, ELEM AS NE, CONVERT(VARCHAR,[MAX_DATE],120) AS [DATE], [TABLE],ROWSTATUS,REMARK = CASE WHEN [MAX_DATE] < DATEADD(HOUR,-3,GETDATE()) THEN 'NOK' ELSE 'OK' END FROM dcpublic._HOUR_UPDATE_MSC WHERE ELEM NOT IN ('MSMD2','MSPK1','MSBT1','TSB14','TSDP8','TSB13','TSDP7','TSYG7','TBD11','TBD10','TSSM2','TSJ32','TSJ33','TSJ34','TSJ31','TSLP6','TSPK4','MSJK5') ORDER BY [DATE], ELEM, [TABLE]""") with open('resAlert01 MSC - ENIQ.csv', 'w', newline='') as f_handle: fileOut = csv.writer(f_handle) fileOut.writerow("OSS_ID,NE,DATE,TABLE,STATUS,REMARK".split(",")) for rx in res: fileOut.writerow(rx) res = fD.querySelect(typeConn='source', querySource="""SELECT MSC AS NE, MP, CONVERT(VARCHAR,[MAX_DATE],120) AS [DATE], [TABLE],REMARK = CASE WHEN [MAX_DATE] < DATEADD(HOUR,-3,GETDATE()) THEN 'NOK' ELSE 'OK' END FROM dcpublic._HOUR_UPDATE_TRD WHERE MSC NOT IN ('MSMD2','MSPK1','MSBT1') AND MSC NOT LIKE '%MSC-B%' AND MSC NOT LIKE '%MSCBC%' and MP >= 71
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_coreReport) res = fD.querySelect( typeConn='source', querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0], 'pool': rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source', modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_neHuawei) res = fD.querySelect(typeConn='source', querySource=""" SELECT XDATE ,POOL ,NE
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse = xCS.connServ52_coreReport) res = fD.querySelect(typeConn='source',querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0],'pool':rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source',modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='target',modeConnOnOff='on',connectionUse= xCS.connServ52_neEri) res = fD.querySelect(typeConn='target',querySource="select time, pool, ne, [SCR (%)], [call_Attempt (times)], [call_Success (times)] from V_WEBCESOC_MSC_ER_SCR_HOURLY where time >= CONVERT(varchar(10), GETDATE()-5, 120) and time < CONVERT(varchar(10), GETDATE()-1, 120)" ) for rx in res: datax = {'time': rx[0], 'pool': rx[1], 'msc':rx[2], 'scr': rx[3], 'attach': rx[4], 'success': rx[5],'system':'eri'} dfUseKpi = dfUseKpi.append(datax, ignore_index=True) fD.setConnection(typeConn='target',modeConnOnOff='off') start2_time = time.time() dfMscUse = pd.DataFrame({'msc': dfUseKpi.msc.unique() }) dfUseKpi = dfUseKpi dfPoolMsc = pd.merge(dfMscUse, dfDoMsc, on = 'msc')
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_coreReport) res = fD.querySelect( typeConn='source', querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0], 'pool': rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source', modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_neHuawei) res = fD.querySelect(typeConn='source', querySource=""" select XDATE, POOL, NE,
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse = xCS.connServ52_coreReport) res = fD.querySelect(typeConn='source',querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0],'pool':rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source',modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse= xCS.connServ52_neHuawei) res = fD.querySelect(typeConn='source',querySource="select XDATE, POOL, NE, SCR_MONITORING, [call attempt times (times)] from WEB_SCR_MSC_HW_HR where XDATE >='2020-02-16'" ) for rx in res: datax = {'time': rx[0], 'pool': rx[1], 'msc':rx[2], 'scr': rx[3], 'attach': rx[4], 'system':'huawei'} dfUseKpi = dfUseKpi.append(datax, ignore_index=True) fD.setConnection(typeConn='source',modeConnOnOff='off') fD.setConnection(typeConn='target',modeConnOnOff='on',connectionUse= xCS.connServ52_neEri) res = fD.querySelect(typeConn='target',querySource="select time, pool, ne, [SCR (%)], [call_Attempt (times)] from V_WEBCESOC_MSC_ER_SCR_HOURLY where time >='2020-02-16'" ) for rx in res: datax = {'time': rx[0], 'pool': rx[1], 'msc':rx[2], 'scr': rx[3], 'attach': rx[4], 'system':'eri'}
import configServer as xCS import dbFunction as fD import csv fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_coreReport) res = fD.querySelect(typeConn='source', querySource=""" declare @dateStart AS VARCHAR(128) declare @dateEnd AS VARCHAR(128) set @dateStart = '20200206' set @dateEnd = '20200313' set @dateStart = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-14 )) set @dateEnd = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ) ) Select * from [v_report_vlr_per_ne_daily_pool] where date >=@dateStart and date <@dateEnd and attach >1 order by date, pool, ne """) with open('reportRawDataVlr.csv', 'w', newline='') as f_handle: fileOut = csv.writer(f_handle) fileOut.writerow("POOL,NE,DATE,ATTACH,DETECH,TOTAL".split(",")) for rx in res: fileOut.writerow(rx)
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse = xCS.connServ52_coreReport) res = fD.querySelect(typeConn='source',querySource='select msc, pool from do_msc group by msc, pool') dfDoMsc = pd.DataFrame() for rx in res: datax = {'msc': rx[0],'pool':rx[1]} dfDoMsc = dfDoMsc.append(datax, ignore_index=True) fD.setConnection(typeConn='source',modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse= xCS.connServ52_neHuawei) res = fD.querySelect( typeConn='source', querySource="select xdate, pool, ne, attach, total from WEB_VLR_MSC_HW_HR where total > 0 and [xdate] >= CONVERT(varchar(10), GETDATE()-7, 120) ") for rx in res: datax = {'time': rx[0], 'pool': rx[1], 'msc':rx[2], 'attach': rx[3], 'total': rx[4], 'system':'huawei'} dfUseKpi = dfUseKpi.append(datax, ignore_index=True) fD.setConnection(typeConn='source',modeConnOnOff='off') fD.setConnection(typeConn='source',modeConnOnOff='on',connectionUse= xCS.connServ52_neEri) res = fD.querySelect( typeConn='source', querySource= "select time, pool, ne, att_subs, tot_subs from WEB_VLR_MSC_ER_HR where[time] >= CONVERT(varchar(10), GETDATE()-7, 120) " )
import configServer as xCS import dbFunction as fD import pandas as pd import time import matplotlib.pyplot as plt start_time = time.time() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_coreReport) res = fD.querySelect( typeConn='source', querySource='select mgw, pool from do_mgw group by mgw, pool') dfDoMgw = pd.DataFrame() for rx in res: datax = {'mgw': rx[0], 'pool': rx[1]} dfDoMgw = dfDoMgw.append(datax, ignore_index=True) fD.setConnection(typeConn='source', modeConnOnOff='off') #------------------------ dfUseKpi = pd.DataFrame() fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ52_neHuawei) res = fD.querySelect(typeConn='source', querySource=""" SELECT TIME, NE, SCC_USAGE,
import configServer as xCS import dbFunction as fD import csv fD.setConnection(typeConn='source', modeConnOnOff='on', connectionUse=xCS.connServ103_neHuawei) for i in range(0, 2): for j in range(4, 7): StrQuery = "select * from tbl_Result_8388830" + str( i ) + " where ( [Result time] >='2020-0" + str( j ) + "-01' and [Result time] <'2020-0" + str( j + 1 ) + "-01' ) and ([object name] like '%818%' or [object name] like '%817%' or [object name] like '%838%')" # print(StrQuery) res = fD.querySelect(typeConn='source', querySource=StrQuery) with open('pm8388830' + str(i) + '_0' + str(j) + '.csv', 'w', newline='') as f_handle: fileOut = csv.writer(f_handle) fileOut.writerow("POOL,NE,DATE,ATTACH,DETECH,TOTAL".split(",")) for rx in res: fileOut.writerow(rx) fD.setConnection(typeConn='source', modeConnOnOff='off')