cancel
Showing results for 
Search instead for 
Did you mean: 

Ops Centre Analytics Disk Pool SQL Report

Woo
Level 3

First time caller, long time listener.

I am after a SQL Query in OPS Centre Analytics to produce the below information on Disk Pools in my NetBackup environment.

I am not an SQL person (clearly), and I don't think a report like this should be too hard to create.

I have used a few SQL queries, but they are not quite what I am after, and the Current Disk Pool Usage Report template also isn't quite doing it for me.

 

Any help would be greatly appreciated!

(Status being Up or Down)

Disk Pool

Total Capacity Used Space Free Space Percentage Full Status
           
1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello,

 

Here you go. You have to do a lookup to another table and translate the number to a status.

 

select
domain_diskpool.name as "Diskpool Name",
domain_diskpool.StorageServerName as "Storage Server",
domain_diskpool.ServerType as "Type",
lookup_diskpoolstatus.name as "Status",
cast((domain_diskpool.usablesize) as numeric) as "Capacity",
cast((domain_diskpool.usedCapacity) as numeric) as "Used",
cast(cast((domain_diskpool.usedCapacity) as numeric) / cast((domain_diskpool.usablesize) as numeric) * 100   as numeric (5,2)) "% Full"
from domain_diskpool, lookup_diskpoolstatus
where lookup_diskpoolstatus.id=domain_diskpool.status

View solution in original post

7 REPLIES 7

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Here you go.

 

select
domain_diskpool.name as "Diskpool Name",
domain_diskpool.StorageServerName as "Storage Server",
domain_diskpool.ServerType as "Type",
cast(cast((domain_diskpool.usablesize) as numeric) / cast((1073741824) as numeric) as numeric (5,2)) "Capacity",
cast(cast((domain_diskpool.usedCapacity) as numeric) / cast((1073741824) as numeric) as numeric (5,2)) "Used",
cast(cast((domain_diskpool.usedCapacity) as numeric) / cast((domain_diskpool.usablesize) as numeric) * 100   as numeric (5,2)) "% Full"
from domain_diskpool

Woo
Level 3

Thanks for replying Riann,

When I run the query the following error appears.

OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -158: Value 57014.13681793212890625 out of range for destination.

Is it me or the script?

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

Sorry, I'm not a SQL guru, it works on my lab environment but its quite small.

 

Try this one and lets see (it would give the size in bytes)

 

select
domain_diskpool.name as "Diskpool Name",
domain_diskpool.StorageServerName as "Storage Server",
domain_diskpool.ServerType as "Type",
cast((domain_diskpool.usablesize) as numeric) as "Capacity",
cast((domain_diskpool.usedCapacity) as numeric) as "Used",
cast(cast((domain_diskpool.usedCapacity) as numeric) / cast((domain_diskpool.usablesize) as numeric) * 100   as numeric (5,2)) "% Full"
from domain_diskpool

Woo
Level 3

Thats it!

Just gotta tidy up the decimal points with the Capacity and Used and I'm all good.

Thanks Riann appreciate your help


Also, is there a way to add in the Status of the disk pool without a number being output?

When I add the line domain_diskpool.status as "Status" the number 2 appers in the output instead of 'Up'

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello,

 

Here you go. You have to do a lookup to another table and translate the number to a status.

 

select
domain_diskpool.name as "Diskpool Name",
domain_diskpool.StorageServerName as "Storage Server",
domain_diskpool.ServerType as "Type",
lookup_diskpoolstatus.name as "Status",
cast((domain_diskpool.usablesize) as numeric) as "Capacity",
cast((domain_diskpool.usedCapacity) as numeric) as "Used",
cast(cast((domain_diskpool.usedCapacity) as numeric) / cast((domain_diskpool.usablesize) as numeric) * 100   as numeric (5,2)) "% Full"
from domain_diskpool, lookup_diskpoolstatus
where lookup_diskpoolstatus.id=domain_diskpool.status

Woo
Level 3

Exactly what I was after.

Thanks very much Riann, appreciate it.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Pleasure