Billing SQL Query

[bigred15]

Hi all,

I am looking for an SQL query which will allow me to determine how many GB is being used in the primary storage pool by the nodes associated with domains.

I have my policy domains configured for departments, like so:
ACCNT_FILE
ACCNT_DB2
ACCNT_EXCH
LEGAL_FILE
LEGAL_DB2
LEGAL_EXCH

So I am looking for an SQL query which will determine the amount of occupancy by the nodes associated with the domains for accounting, for legal, for marketing etc...

I've spent a couple of hours trying to work it out myself but I've really gotten nothing but excessive failed SQL queries. Does anyone have any queries which will help me here?

Thanks in advance.

Kind regards,
Michael Andrews

 

[Jeroen]

This might be a good starting point
http://www.thobias.org/tsm/sql/index.html

 

[Tiger22]

Here's a starting point...

By Node:

select auditocc.node_name, nodes.domain_name, auditocc.backup_mb from auditocc inner join nodes on auditocc.node_name = nodes.node_name order by nodes.domain_name

Totals for each Domain:

select nodes.domain_name as DOMAIN, sum(auditocc.backup_mb)/1024 as BACKUP_GB from auditocc inner join nodes on auditocc.node_name = nodes.node_name group by nodes.domain_name

Format and tweak as you see fit.

 

[bigred15]

Totals for each Domain:

select nodes.domain_name as DOMAIN, sum(auditocc.backup_mb)/1024 as BACKUP_GB from auditocc inner join nodes on auditocc.node_name = nodes.node_name group by nodes.domain_name

Format and tweak as you see fit.

That's exactly what I needed. Thanks for that mate.

Cheers,

bigred15