cancel
Showing results for 
Search instead for 
Did you mean: 

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

Bharath_Achar
Level 6
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.
1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

View solution in original post

18 REPLIES 18

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

Bharath_Achar
Level 6
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.

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

Bharath_Achar
Level 6
Thanks, will be waiting for your reply.

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

 

Bharath_Achar
Level 6

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
Level 6

Thanks Riaan,

 

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

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

Bharath_Achar
Level 6

Hi Riaan, 

 

Am getting error as below -

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

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

 

select * from domain_masterserver

 

and send me the output.
 

Bharath_Achar
Level 6

Hi Riaan,

 

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

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified
Please run   select * from domain_masterserver   and send me the output.

Bharath_Achar
Level 6
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

Bharath_Achar
Level 6

required info has been file attached.

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified

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

Bharath_Achar
Level 6
Hi Riaan, we are at latest on opscentre 7.6.0.2.

Bharath_Achar
Level 6

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.

RiaanBadenhorst
Level 6
Partner    VIP    Accredited Certified
I dont understand what you want. The week at glance run this as sql query, i added the detail about the file list.