08-03-2015 10:10 PM
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 |
Solved! Go to Solution.
08-06-2015 08:25 AM
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
08-04-2015 10:26 PM
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
08-05-2015 09:19 PM
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?
08-05-2015 09:35 PM
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
08-05-2015 11:19 PM
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'
08-06-2015 08:25 AM
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
08-06-2015 04:04 PM
Exactly what I was after.
Thanks very much Riann, appreciate it.
08-06-2015 09:28 PM
Pleasure