08-18-2015 07:49 AM
We are going through an Audit and I need to show the backup logs for 3 servers i backup with Netbackup for the last 3 months . Any idea how i can do this from ops center . cannot see any out of the box reports in there . I am using Ops center 7.6.0.4
Solved! Go to Solution.
08-20-2015 05:51 AM
Adding backup jobachive.type and removing the requirement to match the parent id to job id
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
domain_jobarchive.type in (0)
and
lookup_policytype.id=domain_JobArchive.policyType
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=1
08-18-2015 11:28 AM
Hi,
Here is a simple query that show ciient, policy name, status, end time. It only shows parent job result.
You can change the %Oracle% to match which ever policy you're looking to report on.
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive
where
domain_jobarchive.type in (0)
and
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 3
and
domain_JobArchive.policyName like '%Oracle%'
08-18-2015 10:05 PM
Thanks Rian however the policy conatins so many clients and just need to report on 3 clients in the policy for the last 3 months
08-18-2015 10:24 PM
Ok,
Try
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive
where
domain_jobarchive.type in (0)
and
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 3
and
domain_JobArchive.clientName like '%Client1%'
and
domain_JobArchive.clientName like '%Client2%'
and
domain_JobArchive.clientName like '%Client3%'
08-18-2015 10:39 PM
The report pulling back no data . not sure what am doing wrong
changed teh %Client1% to client names
08-18-2015 10:43 PM
tried the top one with the policy name and same thing , not pulling back any data
08-18-2015 11:42 PM
Tried using a client in the normal ms-windows type policy and the scripts seems to work . However the clients needed are in a Policy Type - Vmware
also would be nice to have another colum showing if the backup was a diff or Full backup if possible
Thank you for all your help
08-19-2015 12:21 AM
Lets stick with one client. I think I should maybe use OR and not AND.
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
domain_jobarchive.type in (0)
and
lookup_policytype.id=domain_JobArchive.policyType
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 3
and
domain_JobArchive.clientName like '%Client1%'
08-19-2015 11:20 PM
seems to be coming back with an error
OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -131: Syntax error near 'domain_jobarchive' on line 16.
08-20-2015 12:07 AM
Sorry, I forgot one AND.
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
domain_jobarchive.type in (0)
and
lookup_policytype.id=domain_JobArchive.policyType
and
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 3
and
domain_JobArchive.clientName like '%Client1%'
08-20-2015 12:15 AM
Thanks Riaan The script seems to be now running but pulling back no data
08-20-2015 12:24 AM
Remove the last condition
----
and
domain_JobArchive.clientName like '%Client1%'
------
and see if its bringing back anything. Please note that Client1 is case sensitive.
08-20-2015 12:35 AM
The scripts works perfect for all clients but does not pull back any data for all the clients with policy type = VMware
08-20-2015 01:25 AM
please pm me the output of
select *
from domain_JobArchive
where
domain_JobArchive.policytpe in (40)
and
DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 3
08-20-2015 01:46 AM
OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -143: Column 'policytpe' not found.
08-20-2015 02:08 AM
running it now think was a typo - domain_JobArchive.policytpe in (40)
08-20-2015 03:15 AM
Thanks,
Please send the output of these (export as csv) to verify
select *
from domain_JobArchive
where
domain_JobArchive.policytpe in (40)
and
DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
And then
select *
from domain_JobArchive
where
DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
08-20-2015 03:50 AM
Still don't see what is wrong.
Please send in csv
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
domain_jobarchive.type in (0)
and
lookup_policytype.id=domain_JobArchive.policyType
and
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=1
08-20-2015 04:46 AM
Lets try without the domain_jobarchive.type. It will now give all the job types (not just backup).
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
lookup_policytype.id=domain_JobArchive.policyType
and
domain_jobarchive.parentjobid=domain_jobarchive.id
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=1
08-20-2015 05:51 AM
Adding backup jobachive.type and removing the requirement to match the parent id to job id
select
domain_JobArchive.clientName as 'Client',
domain_JobArchive.Statuscode as "Status",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.ScheduleName as "Schedule Name",
lookup_policytype.name as "Policy Type",
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_JobArchive, lookup_policytype
where
domain_jobarchive.type in (0)
and
lookup_policytype.id=domain_JobArchive.policyType
and
DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=1