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')
