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
  • Grest Post,

     

    I need tape base policy & other details.

    I need below column,

    Media ID, Media barcode, Media Volume Pool, Policy Name (If Map to Policy), Storage Unit Name (If Staging to Media), Schedule to backup on media,

     

    but i am unable to do get data becuase it's only showing media id which bind to policy,

     

    unable to do join query. I am running below query.

     

     

    select
        domain_media.id as "id",
        domain_media.deleted as "is delete",
        domain_media.barcode as 'Media Barcode',
        domain_media.volumePoolName as 'Media Pool',
        nb_VolumePool.name as "V Pool",
        nb_Policy.name as "Policy",
        (select max (nb_Policy.versionno) as "Version")
    from domain_media, nb_Policy, nb_VolumePool
    where domain_media.volumePoolName = nb_Policy.volumePoolName
    and domain_media.volumePoolName = nb_VolumePool.name
    and domain_media.id <> 'A00000'
    and domain_media.id <> 'A00001'
    and domain_media.id <> 'A00002'
    group by domain_media.id, domain_media.deleted, domain_media.barcode, domain_media.volumePoolName, nb_VolumePool.name, nb_Policy.name
    order by domain_media.id

  • Great post lots of useful information here.

    I was wandering if you know how to get the details of a backup for the last 14 days, the code I had seems to have broken with the latest update to OpsCenter

     

    select
    domain_JobArchive.policyName as "Policy",
    lookup_ScheduleType.name as "TypeBackup",
    JobDuration = (NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime)/60),
    domain_JobArchive.filesBackedUp as "FilesBackedUp",
    domain_JobArchive.throughput as "Throughput",
    cast(domain_JobArchive.Id as varchar(20)) as "domain_JobArchive.id",
    lookup_JobType.name as "JobType",
    domain_JobArchive.attemptCount as "Attempts",
    domain_JobArchive.scheduleName as "scheduleName",
    adjust_timestamp(domain_JobArchive.startTime,-25200000 ) as "startTime",
    adjust_timestamp(domain_JobArchive.endTime,-25200000 ) as "endTime",
    lookup_JobStatus.name as "JobStatus",
    nb_JobArchive.clientName as "Client Name",
    nb_JobBackupAttributesArchive.destStorageUnit as "destStorageUnit"
    from domain_JobArchive
    left outer join nb_JobArchive nb_JobArchive on domain_JobArchive.masterServerId = nb_JobArchive.masterServerId
    and domain_JobArchive.clientName = nb_JobArchive.clientName
    and domain_JobArchive.id = nb_JobArchive.id left outer join nb_JobArchive a1 on domain_JobArchive.masterServerId = a1.masterServerId
    and domain_JobArchive.clientName = a1.clientName
    and domain_JobArchive.id = a1.id left outer join nb_JobBackupAttributesArchive nb_JobBackupAttributesArchive on domain_JobArchive.masterServerId = a1.masterServerId
    and domain_JobArchive.id = a1.id AND a1.masterServerId = nb_JobBackupAttributesArchive.masterserverId
    and a1.clientName = nb_JobBackupAttributesArchive.clientName
    and a1.id = nb_JobBackupAttributesArchive.jobId inner join domain_Policy domain_Policy on domain_Policy.masterServerId = domain_JobArchive.masterServerId
    and domain_Policy.name = domain_JobArchive.policyName
    and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName
    and domain_Policy.versionNo = domain_JobArchive.policyVersionNo
    inner join domain_MasterServer domain_MasterServer on domain_MasterServer.id = domain_JobArchive.masterServerId
    inner join domain_ClientImageCollectionLevel domain_ClientImageCollectionLevel on domain_ClientImageCollectionLevel.id = domain_JobArchive.clientID
    inner join app_Hardware app_Hardware on app_Hardware.hostEntityId = domain_JobArchive.jobMediaServerId
    inner join lookup_ScheduleType lookup_ScheduleType on lookup_ScheduleType.id = domain_JobArchive.scheduletype
    inner join lookup_JobType lookup_JobType on lookup_JobType.id = domain_JobArchive.type
    inner join lookup_JobStatus lookup_JobStatus on (case when domain_JobArchive.state =106 then 3 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =0 then 0 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =1 then 1 when domain_JobArchive.state =3 and domain_JobArchive.statusCode>1 then 2 else -1 end) = lookup_JobStatus.id
    where
    (( domain_ClientImageCollectionLevel.name  = 'PHX000'))
    AND ( ( (UTCBigIntToNomTime(domain_JobArchive.endTime) BETWEEN 'January 20 2016' and 'February 3 2016') ))
    AND ( (domain_JobArchive.type IN (0 )) ) AND ( ( (domain_JobArchive.masterServerId IN (51622,52343,1249637,2015564,2154662,2154889,2172037,2220838,2266125 )) ) )
    order by domain_JobArchive.startTime DESC

  • try replacing this

    AND ( ( (UTCBigIntToNomTime(domain_JobArchive.endTime) BETWEEN 'January 20 2016' and 'February 3 2016') ))

    with

    AND  DATEDIFF(DAY,UtcBigIntToNomTime(domain_JobArchive.endTime),GETDATE())<=14

    this should get you everything that has run in the past 14 days

  • Thanks for writing all the coding of SQL procedures. Database is the most important part to a program as it holds all the data. I was seeking for the coding to take a back-up of the whole program. Lucky I'm to get this page. These codes are really helpful for me to resolve my issues. IT Service Provider El Segundo

  • evangorp I have that in my code now and it still does not work.

    Does anyone know if they changed the mappings in 7.7.1

  • Hi Guys,

    Im so glad i have found this post! we are currently using this to run a daily report for our media servers.

    The only issue im having at the moment is that every time i run this report for a particular media server the report wont show any details :( how ever in activity monitor you can see the job has ran. 

    i have even tried the like %servername%

    the below is what we are using...

    SELECT
    domain_MediaServer.Name as [Media Server Name]
    ,domain_JobArchive.clientName as "Client 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"
    ,DATEDIFF(minute,UTCBigIntToNomTime(domain_JobArchive.startTime)
    ,UTCBigIntToNomTime(domain_JobArchive.endTime)) as "Job Duration(Minutes)"
    ,nb_JobFilesArchive.fileinfo as "Files"
    ,(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"

    //,CAST(domain_JobArchive.bytesWritten/1024/1024/1024 as NUMERIC(20,2)) as "Job Size(MB)",
    //,domain_JobArchive.statusCode as "Status Code"


    FROM domain_JobArchive LEFT JOIN domain_MediaServer on domain_JobArchive.MediaServerId = domain_MediaServer.Id, 
     lookup_JobState, domain_ScheduledJob, nb_JobFilesArchive 

    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_JobArchive.state =lookup_JobState.id AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=48
    AND domain_JobArchive.policyName != ''
    AND domain_MediaServer.Name like '%SERVERNAME%'