Knowledge Base Article

OpsCenter common custom reports

Here I will be posting custom reports, that were created by me or other forum members. All correction, improvements and optimizations to the code below are highly appreciated. If you have some useful report and willing to share, post it as a comment below.

  • SQL procedure for converting time in seconds to hh:mm:sec format. May be useful for displaying duration in human readable form.
  • Audit report deleted and deactivated policies.
  • Master Policy Action Timestamp User
  • Audit report clients removed from policies.
  • Master Client Policy Timestamp User
  • List of all active clients, policies, policy types and backup selections in configuration.
  • Client Name Policy Name Policy Type Backup Selection
  • Last successful backup for failed jobs, done by M_henriksen
  • Master Server Client Name Policy Name Status Code Days since Last successful Last successful
  • Generic daily report
    Policy Name Client Name Platform Version Schedule Name Job Scheduled Time Job Start Time Job End Time Job Duration Job Status Files(Selection) Job Size(MB) Status Code
  • Unique failures daily(Parent jobs only child jobs will be omitted)
  • Client Name Client OS Job Type Job Status Number of entries Policy Name Job Start Time Job End Time Job Duration(Minutes) Job Size(MB) Job File Count Job Throughput (KB/Sec) Status Code Status Code Description
  • Storage usage with dedup ratio per policy type and month.
  • Written (GB) Pre Dedup Size (GB) Dedupe % Jobs End Time Media Space left, TB Size used, TB
  • Disk pool usage per day
  • Written (GB) Pre Dedup Size (GB) Dedupe % Jobs End Time Media Space left, TB Size used, TB
  • Actual data written by policy by date
  • Written (GB) Pre Dedup Size (GB) Dedupe % Jobs End Time Media Policy
    *This procedure used in some of my reports, make sure to add it.

    SQL procedure

    
    CREATE FUNCTION SecToTime(seconds INT)
    RETURNS CHAR(20)
    AS
    BEGIN
    RETURN (
            (SELECT RIGHT('00' + CAST(CAST(floor(seconds/3600) as NUMERIC(3)) as CHAR(3)), 2)) + ':' +
            (SELECT RIGHT('00' + CAST(CAST(floor(seconds/60) % 60 as NUMERIC(2)) as CHAR(2)), 2)) +':' +
            (SELECT RIGHT('00' + CAST(CAST(seconds % 60 as NUMERIC(2)) as CHAR(2)), 2))
    )
    

    Example

    
    SELECT SecToTime(1000);
    00:16:40
    SELECT SecToTime(100000); -- hours will go over 24.
    27:46:40
    

    Audit report deleted and deactivated policies

    
    SELECT
    domain_MasterServer.networkName as "Master",
    LIST(audit_Key.value) as "Policy",
    LIST((CASE audit_Record.messageId 
            WHEN 2 THEN 'Deactivated'
            WHEN 5 THEN 'Deleted'
    END)) as "Action",
    LIST(UTCBigIntToNomTime(audit_Record.auditTime)) as "Timestamp",
    LIST(audit_UserIdentity.userName) as "User"
    FROM audit_Record, audit_Key, domain_MasterServer, audit_UserIdentity
    WHERE domain_MasterServer.id = audit_Key.masterServerId
    AND audit_Record.masterServerId = audit_Key.masterServerId
    AND audit_Record.tieInId = audit_Key.tieInId
    AND audit_Record.recordId = audit_Key.recordId
    AND audit_Record.userIdentityId = audit_UserIdentity.id
    AND audit_Record.messageId IN (2 ,5)
    AND DATEDIFF(day,UTCBigIntToNomTime(audit_Record.auditTime), GETDATE()) <= 7 -- Change Time here
    GROUP BY audit_Record.tieInId,domain_MasterServer.networkName, audit_Record.recordId
    ORDER BY "Timestamp" DESC
    

    Audit report clients removed from policies

    
    ELECT
    domain_MasterServer.networkName as "Master",
    SUBSTR(LIST(audit_Key.value), 0, CHARINDEX(',',LIST(audit_Key.value))) as "Client",
    SUBSTR(LIST(audit_Key.value), CHARINDEX(',',LIST(audit_Key.value))+1, LEN(LIST(audit_Key.value))) as "Policy",
    MAX(UTCBigIntToNomTime(audit_Record.auditTime)) as "Timestamp",
    MAX(audit_UserIdentity.userName) as "User"
    FROM audit_Record, audit_Key, domain_MasterServer, audit_UserIdentity
    WHERE domain_MasterServer.id = audit_Key.masterServerId
    AND audit_Record.userIdentityId = audit_UserIdentity.id
    AND audit_Record.masterServerId = audit_Key.masterServerId
    AND audit_Record.tieInId = audit_Key.tieInId
    AND audit_Record.recordId = audit_Key.recordId
    AND audit_Record.messageId = 10
    AND DATEDIFF(hour,UTCBigIntToNomTime(audit_Record.auditTime), GETDATE()) <= 48 --- Change time here
    GROUP BY audit_Record.tieInId, domain_MasterServer.networkName, audit_Record.recordId
    ORDER BY "Timestamp" DESC
    

    List of all active clients, policies, policy types and backup selections in configuration

    
    SELECT
    domain_PolicyClient.clientName as "Client Name",
    domain_PolicyClient.policyName as "Policy Name",
    lookup_PolicyType.name as "Policy Type",
    nb_Policy.fileList as "Backup Selection"
    FROM
    domain_PolicyClient, domain_Client,lookup_PolicyType, domain_Policy, nb_Policy
    WHERE lookup_PolicyType.id = domain_Policy.type
    AND domain_PolicyClient.clientName = domain_Client.name
    AND domain_PolicyClient.masterServerId = domain_Client.masterServerId
    AND domain_PolicyClient.policyName = domain_Policy.name
    AND domain_PolicyClient.masterServerId = domain_Policy.masterServerId
    AND domain_PolicyClient.policyDomainName = domain_Policy.policyDomainName
    AND domain_PolicyClient.policyVersionNo= domain_Policy.versionNo
    AND domain_Policy.name = nb_Policy.name
    AND domain_Policy.masterServerId = nb_Policy.masterServerId
    AND domain_Policy.versionNo = nb_Policy.versionNo
    AND domain_Policy.policyDomainName = nb_Policy.policyDomainName
    AND domain_Policy.status = 0
    AND domain_Policy.active = 1
    -- domain_Policy.name LIKE '%PR%'
    -- domain_PolicyClient.clientName LIKE '%winprd%'
    ORDER BY domain_PolicyClient.clientName
    

    Last successful backup for failed jobs

    
    SELECT DISTINCT
    B.friendlyName as 'Master Server',
    A.clientName as 'Client Name',
    A.policyName as 'Policy Name',
    LIST( DISTINCT A.statuscode) as 'Status Code',
                (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' 
                        from   domain_JobArchive CKP
                        where CKP.masterServerId = A.masterServerId AND
                                  CKP.id = CKP.parentJobId AND
                                  CKP.clientId = A.clientId AND
                                  CKP.policyId = A.policyId AND
                                  CKP.policyName = A.policyName AND
                                  CKP.policyType = A.policyType AND
                                  ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
                (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                        from   domain_JobArchive BKP
                        where BKP.masterServerId = A.masterServerId AND
                                  BKP.Id = BKP.parentJobId AND
                                  BKP.clientId = A.clientId AND
                                  BKP.policyId = A.policyId AND
                                  BKP.policyName = A.policyName AND
                                  BKP.policyType = A.policyType AND
                                 ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
    FROM  "domain_JobArchive" A , "domain_masterserver" B
    WHERE
    A.masterServerId = B.id AND A.id = A.parentjobid AND
              DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
    AND
    (         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                        from   domain_JobArchive CKP
                        where CKP.masterServerId = A.masterServerId AND
                                  CKP.id = A.parentJobId AND
                                  CKP.clientId = A.clientId AND
                                  CKP.policyId = A.policyId AND
                                  CKP.policyName = A.policyName AND
                                  CKP.policyType = A.policyType AND
                                  CKP.scheduleType = A.scheduleType AND
                                  ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0 AND
    A.scheduleName != 'user_backup' AND
    A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
    A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
    A.scheduletype not in (-1,2)
    Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid
    ORDER BY B.friendlyName , A.clientName
    

    Generic daily report

    
    SELECT
    domain_JobArchive.policyName as "Policy Name",
    domain_JobArchive.clientName as "Client Name",
    domain_Client.hardwareDescription as "Platform",
    domain_Client.versionLabel as "Version",
    domain_JobArchive.scheduleName as "Schedule Name",
    adjust_timestamp(domain_ScheduledJob.scheduledTime,10800000) as "Job Scheduled Time",
    UTCBigIntToNomTime(domain_JobArchive.startTime) as "Job Start Time",
    UTCBigIntToNomTime(domain_JobArchive.endTime) as "Job End Time",
    SecToTime(DATEDIFF(minute,UTCBigIntToNomTime(domain_JobArchive.startTime),UTCBigIntToNomTime(domain_JobArchive.endTime))) as "Job Duration(Minutes)",
    (CASE
            WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
            WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 1 THEN 'Partial'
            WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode > 1 THEN 'Failed'
            ELSE 'Failed'
    END)  as "Job Status",
    nb_JobFilesArchive.fileinfo as "Files",
    CAST(domain_JobArchive.bytesWritten/1024/1024/1024 as NUMERIC(20,2)) as "Job Size(MB)",
    domain_JobArchive.statusCode as "Status Code"
    FROM domain_JobArchive,  lookup_JobState, domain_ScheduledJob, nb_JobFilesArchive, domain_MasterServer, domain_Client
    WHERE domain_ScheduledJob.jobid  = domain_JobArchive.id
    AND domain_JobArchive.masterserverId = nb_JobFilesArchive.masterserverId
    AND domain_JobArchive.clientName = nb_JobFilesArchive.clientName
    AND domain_JobArchive.id = nb_JobFilesArchive.jobID
    AND domain_MasterServer.id = domain_JobArchive.masterServerId
    AND domain_JobArchive.clientName = domain_Client.name
    AND domain_JobArchive.masterserverId = domain_Client.masterserverId
    AND domain_JobArchive.state =lookup_JobState.id
    AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=24
    AND domain_JobArchive.policyName != ''
    

    Unique failures daily(Parent jobs only child jobs will be omitted)

    
    SELECT
    domain_Job.clientName as "Client Name",
    domain_Client.hardwareDescription as "Client OS",
    lookup_JobType.name as "Job Type",
    lookup_JobState.name as "Job Status",
    count(domain_Job.statusCode) as "Number of entries",
    domain_Job.policyName as "Policy Name",
    max(UTCBigIntToNomTime(domain_Job.startTime)) as "Job Start Time",
    max(UTCBigIntToNomTime(domain_Job.endTime)) as "Job End Time",
    max(DATEDIFF(minute,UTCBigIntToNomTime(domain_Job.startTime),UTCBigIntToNomTime(domain_Job.endTime))) as "Job Duration(Minutes)",
    max(domain_Job.bytesWritten/1024/1024/1024) as "Job Size(MB)",
    max(domain_Job.filesBackedUp) as "Job File Count",
    max(domain_Job.throughput) as "Job Throughput (KB/Sec)",
    domain_Job.statusCode as "Status Code",
    lookup_JobStatusCode.name as "Status Code Description"
    FROM
    domain_Job, domain_Client, lookup_JobType, lookup_JobState, lookup_JobStatusCode
    WHERE
    domain_Job.type = lookup_JobType.id AND
    domain_Job.statusCode > 0 AND
    domain_Job.statusCode = lookup_JobStatusCode.id AND
    domain_Job.id = domain_Job.ParentJobID AND
    domain_Job.state = lookup_JobState.id AND
    domain_Job.clientName = domain_Client.name AND
    DATEDIFF(hour,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <=24 AND
    domain_Job.policyName != ''
    GROUP BY domain_Job.clientName,domain_Client.hardwareDescription, domain_Job.policyName, domain_Job.statusCode, lookup_JobStatusCode.name, lookup_JobType.name, lookup_JobState.name
    

    Storage usage with dedup ratio per policy type and month.

    
    SELECT
    
    COALESCE(CAST(SUM(domain_JobArchive.bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Written (GB)",
    COALESCE(CAST(SUM(domain_JobArchive.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Pre Dedup Size (GB)",
    COALESCE(CAST((sum(domain_JobArchive.preSISSize) - sum (domain_JobArchive.bytesWritten))/(sum(domain_JobArchive.preSISSize)+1)*100 AS NUMERIC(20,2)),0) as "Dedupe %",
    DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd') as "Jobs End Time",
    lookup_PolicyType.name as "Policy Type",
    lookup_JobType.name as "Job Type"
    
    FROM 
    domain_JobArchive, lookup_JobType, lookup_PolicyType
    
    WHERE
    lookup_PolicyType.id = domain_JobArchive.policyType AND
    lookup_JobType.id = domain_JobArchive.jobType AND
    
    DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 24
    
    GROUP BY "Jobs End Time", "Policy Type", "Job Type"
    
    ORDER BY "Jobs End Time"
    

    Disk pool usage per day

    
    SELECT
    COALESCE(CAST(SUM(domain_JobArchive.bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Written (GB)",
    COALESCE(CAST(SUM(domain_JobArchive.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Pre Dedup Size (GB)",
    COALESCE(CAST((sum(domain_JobArchive.preSISSize) - sum (domain_JobArchive.bytesWritten))/(sum(domain_JobArchive.preSISSize)+1)*100 AS NUMERIC(20,2)),0) as "Dedupe %",
    DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd') as "Jobs End Time",
    domain_JobArchive.mediaServerName as "Media",
    (CAST(AVG(domain_DiskPoolHistory.usableSize)/1024/1024/1024/1024 as NUMERIC(20,2))) as "Space left, TB",
    (CAST(AVG(domain_DiskPoolHistory.usedCapacity)/1024/1024/1024/1024 as NUMERIC(20,2))) as "Size used, TB"
    FROM
    domain_JobArchive, domain_DiskPoolHistory
    
    WHERE  DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 14
    AND DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd') = DATEFORMAT(cast(UTCBigintToNOMTime(domain_DiskPoolHistory.snapshotTime) as DATETIME), 'yyyy-mm-dd') 
    AND domain_DiskPoolHistory.serverType = 'PureDisk'
    AND domain_DiskPoolHistory.storageServerName = domain_JobArchive.mediaServerName
    GROUP BY "Jobs End Time", "Media"
    ORDER BY "Jobs End Time", "Media"
    

    Actual data written by policy by date

    
    SELECT
    COALESCE(CAST(SUM(domain_JobArchive.bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Written (GB)",
    COALESCE(CAST(SUM(domain_JobArchive.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS "Pre Dedup Size (GB)",
    COALESCE(CAST((sum(domain_JobArchive.preSISSize) - sum (domain_JobArchive.bytesWritten))/(sum(domain_JobArchive.preSISSize)+1)*100 AS NUMERIC(20,2)),0) as "Dedupe %",
    DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd') as "Jobs End Time",
    domain_JobArchive.mediaServerName as "Media",
    domain_JobArchive.policyName as "Policy"
    
    FROM
    domain_JobArchive
    
    WHERE  DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 14
    AND "Policy" IN (SELECT name FROM nb_Policy)
    GROUP BY "Jobs End Time", "Media","Policy" 
    ORDER BY "Jobs End Time", "Media","Policy"
    
Published 10 years ago
Version 1.0