Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

sql query for opscentre to fetch week at a glance report template.

Created: 30 Aug 2014 • Updated: 07 Nov 2014 | 18 comments
This issue has been solved. See solution.

sql query for opscentre to fetch week at a glance report template. At least how to fetch information for File system or directory or drive information of each backup job.

Operating Systems:
Discussion Filed Under:

Comments 18 CommentsJump to latest comment

Riaan.Badenhorst's picture

Do you want the full list of backed up files, as in bplist? Or the Incude lift of the policy?

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Bharath Achar's picture

By default week at a glance gives information about each stream weather it is / or /var for Linux or c: \ or D:\ for windows. I want how to query the db to get this information. And not the contents of drive or directory.

Riaan.Badenhorst's picture

I'll play around with this and get back to you. It will take a while though wink

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Riaan.Badenhorst's picture

Hello

give this a try :)

The last line is controlling how far back you look, in this case 4 days. If you want more fields let me know.

select
domain_jobarchive.id as "Job ID",
domain_jobarchive.PolicyName as "policy name",
domain_jobarchive.scheduleName as "scedule name",
DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd hh-mm-ss') as myDateTime,
(domain_JobArchive.bytesWritten)/1024 as "KBytes Written",
domain_JobArchive.filesBackedUp as "Files Backed up",
nb_jobfiles.fileinfo as "file list"
from domain_jobarchive, nb_jobfiles
where domain_jobarchive.id = nb_jobfiles.jobid
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 4

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Bharath Achar's picture

Hi,

Am looking for SQL query that gives exactly similar to week at a glance report but for one month in csv or tsv format.

please help me on this.

With first column as

swcnpde119.advancemags.com->C:\
swcnpde119.advancemags.com->E:\
swcnpde250.advancemags.com->Shadow Copy Components:\
swcnpde018.advancemags.com->D:\
swcnpde317.advancemags.com->C:\
swcnpde317.advancemags.com->D:\
swcnpde317.advancemags.com->F:\
swacsaws004.advancemags.com->Shadow Copy Components:\
swcnpde295.advancemags.com->Shadow Copy Components:\
swcnpde207.advancemags.com->C:\
swcnpde119.advancemags.com->Shadow Copy Components:\
swcnpde160.advancemags.com->Shadow Copy Components:\
swcnpde219.advancemags.com->D:\
swcnpde210.advancemags.com->E:\
swcnpde317.advancemags.com->Shadow Copy Components:\
swcnpde206.advancemags.com->C:\
swcnpde210.advancemags.com->SET SNAP_ID=swcnpde210.advancemags.com_1408856521
slcnpde198.advancemags.com->/oracle
slacsaws001.advancemags.com->/nfs_shares/livelink_test
slcnpaws004.advancemags.com->/oraexp/nss
slnssde036.advancemags.com->/app
slcnpaws004.advancemags.com->/oraexp/cnp
slcnpde136.advancemags.com->/oramisc
slcnpde093.advancemags.com->/app
slcnpde163.advancemags.com->/oracle
slcnpde208.advancemags.com->/oramisc
slcnpde177.advancemags.com->/oramisc
slnssde029.advancemags.com->/boot
slcnpde099.advancemags.com->/app
slnssde024.advancemags.com->/app
slcnpaws004.advancemags.com->/oraexp/other
slcnpde162.advancemags.com->/
slcnpde162.advancemags.com->/app
slcnpde102.advancemags.com->/
slcnpde208.advancemags.com->/
slcnpde178.advancemags.com->/data
slcnpde081.advancemags.com->/app
Bharath Achar's picture

Thanks Riaan,

Am looking for SQL query that gives similar to week at a glance report but for 1 month.

Riaan.Badenhorst's picture

What else you want?

Is this better?

select
domain_jobarchive.id as "Job ID",
domain_jobarchive.PolicyName as "policy name",
domain_jobarchive.scheduleName as "scedule name",
DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd hh-mm-ss') as myDateTime,
(domain_JobArchive.bytesWritten)/1024 as "KBytes Written",
domain_JobArchive.filesBackedUp as "Files Backed up",
nb_jobfiles.fileinfo as "file list",
domain_Entity.name as "domain_Entity.name",#entity1.name as "#entity1.name" from domain_MasterServer , domain_Client , domain_JobArchive , domain_Entity, nb_jobfiles , (
select * from domain_entity)#entity1 where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName
and ( (domain_MasterServer.id = domain_Entity.id) )
AND ( (domain_Client.id = #entity1.id) )
AND ( ( (domain_JobArchive.isValid = '1') AND (domain_JobArchive.filesBackedUp NOT IN (0 ) AND (domain_JobArchive.policyType NOT IN (-1, 34 )) AND (domain_JobArchive.type NOT IN (30, 31, 32, 33, 34 )) ) AND ( (domain_JobArchive.type IN (1, 0, 7, 6 )) ) AND ( ( (domain_JobArchive.masterServerId IN (61 )) ) ) ) )
AND domain_jobarchive.id = nb_jobfiles.jobid
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Bharath Achar's picture

Hi Riaan, 

Am getting error as below -

An unknown exception has occurred.
To continue, click on any tab.

Riaan.Badenhorst's picture

Sorry, i put my master server id in there (61). Please run

select * from domain_masterserver

and send me the output.
 

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Bharath Achar's picture

Hi Riaan,

I didnt understand your above post. could you please let me know in detail.

Riaan.Badenhorst's picture

Please run

 

select * from domain_masterserver

 

and send me the output.

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

Bharath Achar's picture
id networkName friendlyName GUID pbxPort preferredNetworkAddress osType hardware product versionLabel offsetFromGMT lastContact status valid catalogLastBackedUp clientsNotBackedUp downDrives catalogSize successfulJobs dataBackedUp failedJobs percentSuccess agentConfigId dataCollectorId isImageCollectionEnabled isAdvancedCollectionEnabled isErrorLogCollectionEnabled bpjavaUserName bpjavaPassword homeDirectory volumeMgrHome isLicenseDataCollectionEnabled isSubJobsCollectionEnabled versionNumber oldGuid isTraditionalLicenseDataCollectionEnabled reason osDescription hardwareDescription attemptNumber
61 slcnpaws001.ppp.com slcnpaws001.ppp.com 5CC49AB8-E3C7-11E3-800085AEE753EAEE 1556 localhost/127.0.0.1 41 0 1 7.6.0.2 -240 1.36E+17 1 1 null null null null null null null null 1 1 0 0 0 null null null null 0 0 7060002 5CC49AB8-E3C7-11E3-800085AEE753EAEE 0 - Linux null 0
696 slcnpny009.ppp.com slcnpny009.ppp.com BC0D78AA-C4E4-11E0-8000FD5EFB491295 1556 localhost/127.0.0.1 41 0 1 7.5.0.6 -240 1.36E+17 1 1 null null null null null null null null 1 2 0 0 0 null null null null 0 0 7050006 BC0D78AA-C4E4-11E0-8000FD5EFB491295 0 - Linux null 0
Riaan.Badenhorst's picture

Thanks,

I see you're monitoring 2 masters? One has ID 61 and the 696. So the above query should work as below.

But I also notice you're using 7.5, and I'm using 7.6. So that could be an issue as the schema's might be slightly different.

Sorry, should have asked earlier. You can aways upgrade, as the opscenter can be higher than the master :)

select
domain_jobarchive.id as "Job ID",
domain_jobarchive.PolicyName as "policy name",
domain_jobarchive.scheduleName as "scedule name",
DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd hh-mm-ss') as myDateTime,
(domain_JobArchive.bytesWritten)/1024 as "KBytes Written",
domain_JobArchive.filesBackedUp as "Files Backed up",
nb_jobfiles.fileinfo as "file list",
domain_Entity.name as "domain_Entity.name",#entity1.name as "#entity1.name" from domain_MasterServer , domain_Client , domain_JobArchive , domain_Entity, nb_jobfiles , (
select * from domain_entity)#entity1 where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName
and ( (domain_MasterServer.id = domain_Entity.id) )
AND ( (domain_Client.id = #entity1.id) )
AND ( ( (domain_JobArchive.isValid = '1') AND (domain_JobArchive.filesBackedUp NOT IN (0 ) AND (domain_JobArchive.policyType NOT IN (-1, 34 )) AND (domain_JobArchive.type NOT IN (30, 31, 32, 33, 34 )) ) AND ( (domain_JobArchive.type IN (1, 0, 7, 6 )) ) AND ( ( (domain_JobArchive.masterServerId IN (61, 696 )) ) ) ) )
AND domain_jobarchive.id = nb_jobfiles.jobid
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)

SOLUTION
Bharath Achar's picture

Am getting the o/p however am lookin for an output in similar foramt like week at a glance but for one month.

Please help me on this.

Riaan.Badenhorst's picture

I dont understand what you want. The week at glance run this as sql query, i added the detail about the file list.

Regards,

Riaan Badenhorst

You need an OpenVision to see the truth about Backups. Restores are a plus. But that's just Semantics ;)

ITs easy :)