cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

"insert code" to pull SLA report from opscenter analytics

Hi Team, Am using below stored procedure to define SLA report.need your help to exclude below tabs from the script.can you please help me on this. Reruns Completed,File Count these two tabs need to exclude from the below sql query.can you please help me out  

 

 

IF EXISTS( SELECT 1 FROM sys.sysprocedure WHERE upper(proc_name)=upper('sp_stats_by_client_rerun_mod') ) THEN DROP PROCEDURE DBOPERATOR.sp_stats_by_client_rerun_mod; END IF; CREATE PROCEDURE "DBOPERATOR"."sp_stats_by_client_rerun_mod"( @reportDate "DATETIME",@intNumberofDays integer ) BEGIN CREATE TABLE #Temp_t100( #Temp_t100_Id integer not null default autoincrement, #Temp_t100_MS_Id INTEGER NULL, #Temp_t100_MasterServer varchar(255) null, #Temp_t100_ClientCount bigint null default 0, #Temp_t100_ClientSuccess bigint null default 0, #Temp_t100_JobSuccessFinal BIGINT NULL DEFAULT 0, #Temp_t100_JobFailed bigint null default 0, #Temp_t100_JobFailedOK bigint null default 0, #Temp_t100_JobTrueFailed bigint null default 0, #Temp_t100_JobFailedAgent bigint null default 0, #Temp_t100_JobRerun bigint null default 0, #Temp_t100_DataGB numeric(9,2) null default 0, #Temp_t100_FileCount bigint null default 0, #Temp_t100_TPKB bigint null default 0); CREATE TABLE #Temp_t200 ( #Temp_t200_un_Id INTEGER NOT NULL DEFAULT AUTOINCREMENT, #Temp_t200_masterServerId INTEGER NULL, #Temp_t200_clientName VARCHAR(255) NULL, #Temp_t200_id NUMERIC(42, 0) NULL, #Temp_t200_clientId INTEGER NULL, #Temp_t200_type INTEGER NULL, #Temp_t200_backupType INTEGER NULL, #Temp_t200_state INTEGER NULL, #Temp_t200_statusCode BIGINT NULL, #Temp_t200_startTime BIGINT NULL, #Temp_t200_endTime BIGINT NULL, #Temp_t200_bytesWritten BIGINT NULL, #Temp_t200_attemptCount INTEGER NULL, #Temp_t200_scheduleName VARCHAR(255) NULL, #Temp_t200_scheduleType INTEGER NULL, #Temp_t200_policyId INTEGER NULL, #Temp_t200_policyName VARCHAR(255) NULL, #Temp_t200_policyType INTEGER NULL, #Temp_t200_policyVersionNo INTEGER NULL, #Temp_t200_isValid INTEGER NULL, #Temp_t200_groupId NUMERIC(42, 0) NULL, #Temp_t200_parentJobId NUMERIC(42, 0) NULL, #Temp_t200_expirationTime BIGINT NULL, #Temp_t200_filesBackedUp BIGINT NULL, #Temp_t200_final_id NUMERIC(42, 0) NULL, #Temp_t200_final_job INTEGER NULL DEFAULT 0); CREATE INDEX #Temp_t200_all_ids on #Temp_t200(#Temp_t200_masterServerId,#Temp_t200_clientId,#Temp_t200_policyId); CREATE INDEX #Temp_t200_start_time on #Temp_t200(#Temp_t200_startTime); CREATE INDEX #Temp_t200_end_time on #Temp_t200(#Temp_t200_endTime); INSERT INTO #Temp_t100( #Temp_t100_MS_Id,#Temp_t100_MasterServer ) VALUES( -1, 'ALL' ); INSERT INTO #Temp_t100( #Temp_t100_MS_Id,#Temp_t100_MasterServer ) SELECT C.id, C.Name FROM "DBA"."View_Tree" A,"DBA"."view_Node" B,"DBA"."domain_entity" C WHERE A.ID = -1 and A.ID = B.TreeId and B.entityId = C.Id and B.deleted = 0 and C.Name not in( 'uxnbmaster100,uxnbmaster200' ) ORDER BY C.Name ASC; INSERT INTO #Temp_t200 ( #Temp_t200_masterServerId,#Temp_t200_clientName,#Temp_t200_id,#Temp_t200_clientId,#Temp_t200_type, #Temp_t200_backupType,#Temp_t200_state,#Temp_t200_statusCode,#Temp_t200_startTime,#Temp_t200_endTime, #Temp_t200_bytesWritten,#Temp_t200_attemptCount,#Temp_t200_scheduleName,#Temp_t200_scheduleType, #Temp_t200_policyId,#Temp_t200_policyName,#Temp_t200_policyType,#Temp_t200_policyVersionNo, #Temp_t200_isValid,#Temp_t200_groupId,#Temp_t200_parentJobId,#Temp_t200_expirationTime,#Temp_t200_filesBackedUp) SELECT masterServerId,clientName,id,clientId,type,backupType, state,statusCode,startTime,endTime, bytesWritten,attemptCount,scheduleName,scheduleType, policyId,policyName,policyType,policyVersionNo, isValid,groupId,parentJobId,expirationTime,filesBackedUp FROM DBA.domain_JobArchive WHERE DBA.domain_JobArchive.type in (0,6,28) and DBA.domain_JobArchive.State = 3 and domain_JobArchive.statusCode not in( 150,230,190,191 ) and DBA.domain_JobArchive.masterServerId IN ( SELECT DISTINCT #Temp_t100_MS_Id FROM #Temp_t100) and domain_JobArchive.endTime <= NOMTIMETOUTCBIGINT(@reportDate) and domain_JobArchive.endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate)); UPDATE #Temp_t200 A SET A.#Temp_t200_final_id = ( SELECT Min(B.id) FROM domain_JobArchive B WHERE B.masterServerId = A.#Temp_t200_masterServerId AND B.clientName = A.#Temp_t200_clientName AND B.clientId = A.#Temp_t200_clientId AND B.policyId = A.#Temp_t200_policyId AND B.policyName = A.#Temp_t200_policyName AND B.policyType = A.#Temp_t200_policyType AND B.scheduleType = A.#Temp_t200_scheduleType AND B.scheduleName = A.#Temp_t200_scheduleName AND B.isValid = 1 AND B.statusCode IN (0,1) AND B.startTime > A.#Temp_t200_startTime AND B.startTime < NOMTIMETOUTCBIGINT(DateAdd(hh, 24, UTCBIGINTTONOMTIME(A.#Temp_t200_startTime)))) WHERE A.#Temp_t200_statusCode NOT IN (0,1,150,230,190,191) and Lower(A.#Temp_t200_policyName) not like '%test%' and Lower(A.#Temp_t200_policyname) not like '%temp%'; UPDATE #Temp_t100 as A SET #Temp_t100_ClientCount = ( SELECT Count(distinct #Temp_t200_clientName) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode not in( 150,230,190,191 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_ClientCount = ( SELECT Sum(B.#Temp_t100_ClientCount) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; UPDATE #Temp_t100 as A SET #Temp_t100_JobFailed = ( SELECT Count(distinct #Temp_t200_clientName) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode not in( 0,1,150,230,190,191 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_JobFailed = ( SELECT Sum(B.#Temp_t100_JobFailed) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; UPDATE #Temp_t100 as A SET #Temp_t100_JobFailedAgent = ( SELECT Count(distinct #Temp_t200_clientName) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode not in( 0,1,150,230,190,191 ) and #Temp_t200.#Temp_t200_policyType not in( 13,0 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_JobFailedAgent = ( SELECT Sum(B.#Temp_t100_JobFailedAgent) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; UPDATE #Temp_t100 as A SET #Temp_t100_JobTrueFailed = ( SELECT Count(distinct #Temp_t200_clientName) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode not in( 0,1,150,230,190,191 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200_final_id IS NULL and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_JobTrueFailed = ( SELECT Sum(B.#Temp_t100_JobTrueFailed) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; UPDATE #Temp_t100 as A SET #Temp_t100_JobFailedOK = A.#Temp_t100_JobFailed - A.#Temp_t100_JobTrueFailed; UPDATE #Temp_t100 as A SET #Temp_t100_ClientSuccess = #Temp_t100_ClientCount - #Temp_t100_JobFailed; UPDATE #Temp_t100 as A SET #Temp_t100_JobSuccessFinal = #Temp_t100_ClientCount - #Temp_t100_JobTrueFailed; UPDATE #Temp_t100 as A SET #Temp_t100_DataGB = ( SELECT convert(numeric(9,3),(Sum(#Temp_t200_bytesWritten /1024.0/1024.0/1024.0))) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode in( 0,1 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_DataGB = ( SELECT Sum(B.#Temp_t100_DataGB) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; UPDATE #Temp_t100 as A SET #Temp_t100_FileCount = ( SELECT Sum(#Temp_t200_filesBackedUp) FROM #Temp_t200,domain_MasterServer WHERE #Temp_t200.#Temp_t200_type in( 0,6 ) and #Temp_t200.#Temp_t200_State = 3 and #Temp_t200.#Temp_t200_statusCode in( 0,1 ) and Lower(#Temp_t200.#Temp_t200_policyName) not like '%test%' and Lower(#Temp_t200.#Temp_t200_policyname) not like '%temp%' and #Temp_t200.#Temp_t200_masterServerId = domain_MasterServer.id and domain_MasterServer.friendlyName = A.#Temp_t100_MasterServer and #Temp_t200.#Temp_t200_endTime <= NOMTIMETOUTCBIGINT(@reportDate) and #Temp_t200.#Temp_t200_endTime >= NOMTIMETOUTCBIGINT(DateAdd(day,@intNumberofDays*-1,@reportDate))); UPDATE #Temp_t100 as A SET A.#Temp_t100_FileCount = ( SELECT Sum(B.#Temp_t100_FileCount) FROM #Temp_t100 B WHERE B.#Temp_t100_MasterServer <> 'ALL' ) WHERE A.#Temp_t100_MasterServer = 'ALL'; SELECT #Temp_t100_MasterServer as "Master Server", #Temp_t100_ClientCount as "Total Clients", #Temp_t100_ClientSuccess as "Success Clients", #Temp_t100_JobFailed as "Failed Clients", #Temp_t100_JobFailedOK as "Reruns Completed", #Temp_t100_JobSuccessFinal as "Final Success Clients", #Temp_t100_JobTrueFailed as "Final Failed Clients", convert(numeric(9,2),(#Temp_t100_JobSuccessFinal*100.0/case #Temp_t100_ClientCount when 0 then 1 else #Temp_t100_ClientCount end)) as "Success %", #Temp_t100_DataGB as "Data Backed Up (GB)", #Temp_t100_FileCount as "File Count" FROM #Temp_t100 ORDER BY #Temp_t100_Id asc; DROP TABLE #Temp_t100; DROP TABLE #Temp_t200; END

Tags (2)
3 Replies
Highlighted

Re: sql script correction to pull SLA report from opscenter analytics

Please could you edit your original posting and instead add the SQL using the "insert code" feature so that it is structured and readable, so that others can read it.

Highlighted

Re: sql script correction to pull SLA report from opscenter analytics

Sure, Thanks

Highlighted

Re: sql script correction to pull SLA report from opscenter analytics

As per suggestions ,I have updaed