Need help with sql queries for opscenter to generate deleted clients

And also policy the clients were deleted from. Thank you

Hi Dolly,

This is best achieved using NB Auditing. Don't think we'll keep this daa in opscenter. But you can check in the schema document I showed you, and if its in there, we can check.

I was thinking this could be gotten using"policy audit" modification in the query. But still dont have idea how to apply it. I do get these value running policy audit under audit trails in my opscenter analytic. But will like to have something like a report which can be sent to respective stakeholders. Thanks

SELECT

domain_PolicyClient.policyName as "Policy Name",
domain_PolicyClient.clientName as "Client Name"

FROM domain_PolicyClient

WHERE domain_PolicyClient.clientName NOT IN
	(SELECT domain_PolicyClient.clientName
	FROM domain_PolicyClient,
		(SELECT
		domain_PolicyClient.policyName as pname,
		domain_PolicyClient.clientName as cname

		FROM 
		domain_PolicyClient, 
			(SELECT
			 MAX(domain_PolicyClient.policyVersionNo ) as maxVersion,
			 domain_PolicyClient.policyName as maxPolicy
			 FROM domain_PolicyClient 
			 GROUP BY domain_PolicyClient.policyName) verSubq

		WHERE
		verSubq.maxPolicy = domain_PolicyClient.policyName AND
		domain_PolicyClient.policyVersionNo = verSubq.maxVersion) CurrentClients
	WHERE 
	CurrentClients.pname = domain_PolicyClient.policyName AND
	CurrentClients.cname = domain_PolicyClient.clientName)
GROUP BY "Policy Name","Client Name"
ORDER BY "Policy Name"

A bit messy, but should work.

Hi VoropaevPavel

It does work, But can you please also send me a version or retune above query to generate just  last 48hours report. Above seems to be generating all clients that have been deleted. Thank you so much.

To explain further, Could please insert a parameter to display this report for last 48hours report!. Thanks again

dup

Hello Dolly,

Try this

SELECT

domain_PolicyClient.policyName as "Policy Name",
domain_PolicyClient.clientName as "Client Name"

FROM domain_PolicyClient

WHERE 
domain_PolicyClient.clientName NOT IN
	(SELECT domain_PolicyClient.clientName
	FROM domain_PolicyClient,
		(SELECT
		domain_PolicyClient.policyName as pname,
		domain_PolicyClient.clientName as cname

		FROM 
		domain_PolicyClient, 
			(SELECT
			 MAX(domain_PolicyClient.policyVersionNo ) as maxVersion,
			 domain_PolicyClient.policyName as maxPolicy
			 FROM domain_PolicyClient 
			 GROUP BY domain_PolicyClient.policyName) verSubq

		WHERE
		verSubq.maxPolicy = domain_PolicyClient.policyName AND
		domain_PolicyClient.policyVersionNo = verSubq.maxVersion) CurrentClients
	WHERE 
	CurrentClients.pname = domain_PolicyClient.policyName AND
	CurrentClients.cname = domain_PolicyClient.clientName) AND
domain_PolicyClient.clientName  IN 
	(SELECT
	audit_Key.value
	FROM audit_Record, audit_Key
	WHERE
	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)
GROUP BY "Policy Name","Client Name"
ORDER BY "Policy Name"

Note that NetBackup audit doesn't track changes made from command line or directly trough filesystem.

I just ran the query and It came back with no records. I also extended the hours from 48 to 72 but also came back with no records. I did delete couple of client last 2 days using activity monitor which were captured in opscenter audit.

Hi Dolly,

In the query I use domain_PolicyClient table, perhaps it wasn't updated yet.

You can do,

SELECT
LIST(audit_Key.value)
FROM audit_Record, audit_Key
WHERE
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
GROUP BY audit_Record.recordId

As a simpler version of it, but it could only return CSV values. OpsCenter lacks analytics function of full SYBASE, like regular expression.