cancel
Showing results for 
Search instead for 
Did you mean: 

Backup Logs for last 3months for a server

yobole
Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

26 REPLIES 26

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

yobole
Level 6

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

 

yobole
Level 6

The report pulling back no data . not sure what am doing wrong

 

changed teh %Client1% to client names

yobole
Level 6

tried the top one with the policy name and same thing , not pulling back any data
 

yobole
Level 6

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

 

yobole
Level 6

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.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

yobole
Level 6

 

Thanks Riaan The script seems to be now running but pulling back no data

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Remove the last condition

----

and
domain_JobArchive.clientName like '%Client1%'

------

and see if its bringing back anything. Please note that Client1 is case sensitive.

yobole
Level 6

The scripts works perfect for all clients but does not pull back any data for all the clients with policy type = VMware

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

yobole
Level 6

OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -143: Column 'policytpe' not found.

yobole
Level 6

running it now think was a typo - domain_JobArchive.policytpe in (40)

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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