cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter SQL query - problem

r4mzeso
Level 3
Hi there, I am trying to get list of all clients and policies from Netbackup

ex:
policy1 node1
policy1 node2
policy2 node3

ect

I use generate report and run query

Select policyName, clientName from domain_PolicyClient ORDER BY policyName

OpsCenter gives me report but its shows also policies which have been deleted. I would like to have report up to date (online). Is It anyway I can achieve that?
 
11 REPLIES 11

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

You can use this

 

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName, MAX(domain_PolicyClient.policyversionno)
from domain_PolicyClient
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

 

It adds another column at the end (MAX(domain_PolicyClient.policyversionno) that you can ignore. I don't know enough about SQL to know how to hide it :).

r4mzeso
Level 3

Ok thx, but it still shows me deleted policies, what does it mean policyversionno? Some got =1 some =13 or 10

 

??

 

This query shows me poliecies which are not shown in netbck GUI..

 

There is also "active" flag, but I cannot what what does it mean.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Ah ok, sorry, I got side tracked by the policy version number. They indicate how many times a policy has changed, 13 being 13 changes.

 

I'll work on the deleted bit :)

r4mzeso
Level 3
I just need the envinroment report, all exisiting policies and nodes in simple query

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Fixed

 

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

r4mzeso
Level 3

Hm thx, it looked good, but I have checked, and some policies are inactive :) heh, I think I will have to do this manualy..

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Included the check for active only (text in BOLD)

 

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0) AND domain_policy.active IN (1)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

Have you verified its working now?

r4mzeso
Level 3

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0) AND domain_policy.active IN (1)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

 

No this query still shows me deactivated policies. While I change domain_policy.active IN (0) this shows me all deactivated policy (and its good outpust, its the same numer of rows we did manually)

So still is something wrong with query :)

 

 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hmm, works fine on mine. There must be some other discrepancy that you're catching in your environment.

r4mzeso
Level 3

Anyway I am looking for another SQL,

What I have to do is list all scheduler (calendar only) with policy name, scheduler type, days, start time and end time

Like this:

 

Policy name     Type     ScheduleType      ScheduleName     Daily                           Weekly         Monthly

policy1            standard   Calendar               sched1             Mon,Tu                       Saturday

                                                                                       start 12:00 end 14:00       start 14:00 end 15:00

 

Is it possible? Cuz it looks more complex I was trying to write it using domain_Schedule and domain_ScheduleCalendar but with no success