on 03-27-2015 04:03 AM
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.
Master | Policy | Action | Timestamp | User |
Master | Client | Policy | Timestamp | User |
Client Name | Policy Name | Policy Type | Backup Selection |
Master Server | Client Name | Policy Name | Status Code | Days since Last successful | Last successful |
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 |
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 |
Written (GB) | Pre Dedup Size (GB) | Dedupe % | Jobs End Time | Media | Space left, TB | Size used, TB |
Written (GB) | Pre Dedup Size (GB) | Dedupe % | Jobs End Time | Media | Space left, TB | Size used, TB |
Written (GB) | Pre Dedup Size (GB) | Dedupe % | Jobs End Time | Media | Policy |
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
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
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
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
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
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 != ''
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
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"
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"
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"
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.
Thank you V. God bless you for this great post.
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
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
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 |
*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.
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?
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 :)
One of the finest work on Symconnect :)
Good going Pavel
~Gulzar
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
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!
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
Thanks a lots for your sharing. It's very helpful.
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%'