cancel
Showing results for 
Search instead for 
Did you mean: 
VoropaevPavel
Level 4
Partner Accredited

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"
    
Comments
Ramiro-Magan
Level 5
Certified

Great post!

thanks for sharing.

 

I have 2 comments:

1 - the Generic daily report is not working for me, shoud I modify something?

2 - for the "Unique failures daily(Parent jobs only child jobs will be omitted)", I modified one parameter, for showing backups with > 0, for > 1, so it doesn´t show partial backups, only fails.

Dollypee
Moderator
Moderator
   VIP    Certified

Thank you V. God bless you for this great post.

Dollypee
Moderator
Moderator
   VIP    Certified

Hi V..Quick correction fo "Audit report deleted and deactivated policies."

In the schema manual, 2 = deleted & 5 = deactivated

as against above in the query. Thanks

yobole
Level 6

Really nice scripts. My SQL knowledge is non is non existence.

Storage usage with dedup ratio per policy type and month – would like this ratio per individual clients rather than policy for a particular media server name “MediasServer1”

Actual data written by policy by date – would this also for all clients over a week for a particular media server name “MediasServer1”

 

Thanks in advance

Manumohan
Level 4

Hi VoropaevPavel's,

Hope you doing Great!

Here i have requirement to pull only configuration detail from netbackup. How ever i noticed in the opscenter custom report ther dont have a "backup selection" therfore i need to run individual report that not a good idea.

I check the above SQL query (List of all active clients, policies, policy types and backup selections in configuration.)you have posted that did well in my opscenter, in addition to that i need your help in adding some more fields to that so i shall get evey thing in single shot, this will be highly useful for our large enviroment.

Notice: i'm not looking for backup status, only to pull what actually configured in netbackup and get those details. Not only the active. Everything mentioned there.

Below are the fields.

Master Server
Master Server Version
Master server OS
Policy Name
Policy Type
Backup Selection
Schedule Name
Schedule/Level Type
Schedule retention
Client Name
Client Hardware
Client OS

 

nmatlock
Level 1

*This procedure used in some of my reports, make sure to add it.

Awesome post, this will be very helpful. I have a couple of questions.

  • How do you add a stored procedure.
  • Is there a way to use local variable within a query?

I have been trying to write a script with a local variable something like:

declare @averageJobSize bigint
select @averageJobSize = (select sum(domain_JobArchive.byteswritten/(1024*1024)) from domain_JobArchive where datediff(day,UTCBigIntToNomTime(domainJobArchive.endTime),getdate())<= 1)
select @averageJobSize

 

Also is there a way to view stored procedures?

 

KDob
Level 3
Partner Accredited

All scripts worked in my OpsCenter Analytics 7.7 environment!

Great post!  Keep the SQL rolling in!

Cut, paste, and save report is a great way to write "Custom Scripts" for me :)

GulzarShaikhAUS
Level 6
Partner Accredited Certified

One of the finest work on Symconnect :)

Good going Pavel

~Gulzar

Ravindra_Bharat
Level 2

Hello Pavel,

 

Firstly, many thanks to help me with your already posted queries which are good enough for a generalist like me to use and understand. :)

I have a requirement to generate a report where i am able to see the "scheduled start time", "actual start time", "attempt time", "no of attempts". success

Just to give you a context, we have a situation where the number of diskpools are limited in size and count. Hence everytime a backup fires, sometimes it has to wait and re-attempt after the disk is free and some jobs fail while in this. This is leading to a problem where we are not able to track the success of backup jobs run other than first attempt.

Let me update you that we contacted Symantec but unsuccessfully for the same and i am just pasting the relevant part of their reponse here, in case it helps you. now i know Symantec does not support custom queries and reports. (https://support.symantec.com/en_US/article.HOWTO103760.html)

"the requirement which you have for custom report where in you require start time and time at which backup become active separately is not possible to reflect as netbackup itself doesn't treat these two timings differently, it only consider start time and save the same in its database and opscenter pull its information from netbackup database."

 

I am not an expert, but hope was able to explain

Let me thank in advance for your time and efforts (Hope not much) on this. :)

 

Regards

Ravindra Bharati

BreezeWJ
Level 2

Helpfull Post, Great info.

One thing the "Generic daily report" is not working for me, I am running NB V 7.6.0.1

The job TImes out and i get this error >

"OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -265: Procedure 'SecToTime' not found. "

shoud I modify something?

 

Thanks!

Jimesh_Makawana
Level 2

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

robdogaz
Level 0

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

evangorp
Not applicable

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

FloraSullivan
Not applicable

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

robdogaz
Level 0

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

Sherry_Wang
Level 2

Thanks a lots for your sharing. It's very helpful.

Shot81
Not applicable

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

Version history
Last update:
‎03-27-2015 04:03 AM
Updated by: