Welcome! » Log In » Create A New Profile

Number of volumes per client

Posted by Loon, Eric van (ITOP NS) - KLM 
Loon, Eric van (ITOP NS) - KLM
Number of volumes per client
July 11, 2019 06:59AM
Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site: http://www..klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Marc Lanteigne
Re: Number of volumes per client
July 11, 2019 07:59AM
Hi Eric,

You could do something like this:

select node_name,count(distinct(volume_name)) from contents group by
node_name

-
Thanks,
Marc...
___________________________________________________
Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
416.478.0233 | marclanteigne@ca.ibm.com
Summer Hours: Monday to Thursday, 6:00 to 16:30 Eastern

Latest Servermon for Spectrum Protect:
http://ibm.biz/IBMSpectrumProtectServermon
Performance Mustgather for Spectrum Protect:
http://ibm.biz/IBMSpectrumProtectPerformanceMustgather
Spectrum Protect Blueprint: http://ibm.biz/IBMSpectrumProtectBlueprints

Follow me on: Twitter, developerWorks, LinkedIn



-----Original Message-----
From: Loon, Eric van (ITOP NS) - KLM <Eric-van.Loon@KLM.COM>
Sent: Thursday, July 11, 2019 10:58 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] Number of volumes per client

Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per
client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.klm.com&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hMBqtRSV0jXgOdXEmlNk_-O9LHkPCGSh9PJBRSlL8Q4&m=nfOo48SnthJS2-_HlS7z2-jiv1rUtIJ711DzUTOx0LY&s=nT8p19UU2HYBIURwdYRBqCDMI_GY8rLissfAm5ZG45E&e= .
This e-mail and any attachment may contain confidential and privileged
material intended for the addressee only. If you are not the addressee, you
are notified that no part of the e-mail or any attachment may be disclosed,
copied or distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately by
return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
employees shall not be liable for the incorrect or incomplete transmission
of this e-mail or any attachments, nor responsible for any delay in
receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Schulz Stefan
AW: Number of volumes per client
July 11, 2019 07:59AM
Hi Eric,

i'd suggest "select node_name,count(distinct volume_name) from volumeusage group by node_name".
You could also further filter with "where stgpool_name..." or "where copy_type='BACKUP'/'ARCHIVE'"

Best Regards,

Stefan Schulz
CANCOM GmbH

CANCOM GmbH
Sitz der Gesellschaft: Jettingen-Scheppach
HRB 10653 Memmingen
Geschäftsführer:
Ralf Binder, Stefan Fritz, Jens-Peter Gotter, Paul Holdschik, Dirk Kiefer, Petra Trost-Gürtner
Diese E-Mail und alle mit gesendeten Dateien sind vertraulich und ausschließlich für den Gebrauch durch den Empfänger bestimmt!
This e-mail and any files transmitted with it are confidential and intended solely for the use of the addressee!



-----Ursprüngliche Nachricht-----
Von: ADSM: Dist Stor Manager <ADSM-L@VM.MARIST.EDU> Im Auftrag von Loon, Eric van (ITOP NS) - KLM
Gesendet: Donnerstag, 11. Juli 2019 15:55
An: ADSM-L@VM.MARIST.EDU
Betreff: [ADSM-L] Number of volumes per client

Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site: http://www..klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Marc Lanteigne
Re: AW: Number of volumes per client
July 11, 2019 07:59AM
Hi Eric,

Stefan's method will be quicker than mine, smaller table to parse.

-
Thanks,
Marc...
___________________________________________________
Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
416.478.0233 | marclanteigne@ca.ibm.com
Summer Hours: Monday to Thursday, 6:00 to 16:30 Eastern

Latest Servermon for Spectrum Protect:
http://ibm.biz/IBMSpectrumProtectServermon
Performance Mustgather for Spectrum Protect:
http://ibm.biz/IBMSpectrumProtectPerformanceMustgather
Spectrum Protect Blueprint: http://ibm.biz/IBMSpectrumProtectBlueprints

Follow me on: Twitter, developerWorks, LinkedIn



-----Original Message-----
From: Schulz Stefan <Stefan.Schulz@CANCOM.DE>
Sent: Thursday, July 11, 2019 11:17 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] AW: Number of volumes per client

Hi Eric,

i'd suggest "select node_name,count(distinct volume_name) from volumeusage
group by node_name".
You could also further filter with "where stgpool_name..." or "where
copy_type='BACKUP'/'ARCHIVE'"

Best Regards,

Stefan Schulz
CANCOM GmbH

CANCOM GmbH
Sitz der Gesellschaft: Jettingen-Scheppach HRB 10653 Memmingen
Geschäftsführer:
Ralf Binder, Stefan Fritz, Jens-Peter Gotter, Paul Holdschik, Dirk Kiefer,
Petra Trost-Gürtner Diese E-Mail und alle mit gesendeten Dateien sind
vertraulich und ausschließlich für den Gebrauch durch den Empfänger
bestimmt!
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the addressee!



-----Ursprüngliche Nachricht-----
Von: ADSM: Dist Stor Manager <ADSM-L@VM.MARIST.EDU> Im Auftrag von Loon,
Eric van (ITOP NS) - KLM
Gesendet: Donnerstag, 11. Juli 2019 15:55
An: ADSM-L@VM.MARIST.EDU
Betreff: [ADSM-L] Number of volumes per client

Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per
client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.klm.com&d=DwIFAw&c=jf_iaSHvJObTbx-siA1ZOg&r=hMBqtRSV0jXgOdXEmlNk_-O9LHkPCGSh9PJBRSlL8Q4&m=o_j_99v5y2_C1cPNbeG8vPoLNRKzQ-f0AK8t2hENg_k&s=K2tlxD5WfG5WUbMPu9ge0ijUoIw4DmZhLBIELbOBuxg&e= .
This e-mail and any attachment may contain confidential and privileged
material intended for the addressee only. If you are not the addressee, you
are notified that no part of the e-mail or any attachment may be disclosed,
copied or distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately by
return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
employees shall not be liable for the incorrect or incomplete transmission
of this e-mail or any attachments, nor responsible for any delay in
receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Ron Delaware
Re: Number of volumes per client
July 11, 2019 08:59AM
Hi Eric,
These should give you what you are looking for


echo
"************************************************************************"

echo "* Node Activities - How many volumes each client has data data11*"

select count(DISTINCT volume_name) as Num_volumes, node_name, stgpool_name
from volumeusage group by node_name, stgpool_name order by volumes desc
echo
"****************************************************************************************************************"


echo
"************************************************************************"

echo "* Node Activities - List each volume and how many nodes are on
tape*"
select volume_name, stgpool_name, count(distinct node_name) as Nodes from
volumeusage group by volume_name, stgpool_name order by 3 desc
echo
"****************************************************************************************************************"



echo
"************************************************************************"

echo "* Node Activities - For each node list avg MB per tape change
%TAPE% to match your specific tape pool*"
select vu.node_name, ao.total_mb, count(distinct vu.volume_name) as tapes,
ao.total_mb/count(distinct vu.volume_name) as "AVG MB/tape" from
volumeusage vu, auditocc ao where vu.stgpool_name like '%TAPE%' and
vu.node_name=ao.node_name group by vu.node_name, ao.total_mb order by 4
echo
"****************************************************************************************************************"



Ronald C. Delaware
Senior Consultant
IBM Corporation | System Lab Services
925-822-3977 (Office)
925-457-9221 (cell phone)
mailto:ron.delaware@us.ibm.com




From: Marc Lanteigne <marclanteigne@CA.IBM.COM>
To: ADSM-L@VM.MARIST.EDU
Date: 07/11/2019 07:15 AM
Subject: [EXTERNAL] Re: [ADSM-L] Number of volumes per client
Sent by: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU>



Hi Eric,

You could do something like this:

select node_name,count(distinct(volume_name)) from contents group by
node_name

-
Thanks,
Marc...
___________________________________________________
Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
416.478.0233 | marclanteigne@ca.ibm.com
Summer Hours: Monday to Thursday, 6:00 to 16:30 Eastern

Latest Servermon for Spectrum Protect:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectServermon&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=sFMbbETX5Vd-NzoKeMgvqidCuJ4lqzzWfrbvhCmp57E&e=

Performance Mustgather for Spectrum Protect:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectPerformanceMustgather&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=1i1fKfzAmWtRtt_3kdvSRz48l_g0KoSdNZV97yLWULY&e=

Spectrum Protect Blueprint:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectBlueprints&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=U_G1kdJgxQvMe1i8Y98Tj8lNOFw_D-Ua0VSfBT5vPsQ&e=


Follow me on: Twitter, developerWorks, LinkedIn



-----Original Message-----
From: Loon, Eric van (ITOP NS) - KLM <Eric-van.Loon@KLM.COM>
Sent: Thursday, July 11, 2019 10:58 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] Number of volumes per client

Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per
client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.klm.com&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hMBqtRSV0jXgOdXEmlNk_-O9LHkPCGSh9PJBRSlL8Q4&m=nfOo48SnthJS2-_HlS7z2-jiv1rUtIJ711DzUTOx0LY&s=nT8p19UU2HYBIURwdYRBqCDMI_GY8rLissfAm5ZG45E&e=
..
This e-mail and any attachment may contain confidential and privileged
material intended for the addressee only. If you are not the addressee,
you
are notified that no part of the e-mail or any attachment may be
disclosed,
copied or distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately by
return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
employees shall not be liable for the incorrect or incomplete transmission
of this e-mail or any attachments, nor responsible for any delay in
receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Loon, Eric van (ITOP NS) - KLM
Re: Number of volumes per client
July 12, 2019 02:59AM
Hi guys,

To everybody who responded: thank you very much for your help!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Ron Delaware
Sent: donderdag 11 juli 2019 17:43
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Number of volumes per client

Hi Eric,
These should give you what you are looking for


echo
"************************************************************************"

echo "* Node Activities - How many volumes each client has data data11*"

select count(DISTINCT volume_name) as Num_volumes, node_name, stgpool_name from volumeusage group by node_name, stgpool_name order by volumes desc echo "****************************************************************************************************************"


echo
"************************************************************************"

echo "* Node Activities - List each volume and how many nodes are on tape*"
select volume_name, stgpool_name, count(distinct node_name) as Nodes from volumeusage group by volume_name, stgpool_name order by 3 desc echo "****************************************************************************************************************"



echo
"************************************************************************"

echo "* Node Activities - For each node list avg MB per tape change %TAPE% to match your specific tape pool*"
select vu.node_name, ao.total_mb, count(distinct vu.volume_name) as tapes, ao.total_mb/count(distinct vu.volume_name) as "AVG MB/tape" from volumeusage vu, auditocc ao where vu.stgpool_name like '%TAPE%' and vu.node_name=ao..node_name group by vu.node_name, ao.total_mb order by 4 echo "****************************************************************************************************************"



Ronald C. Delaware
Senior Consultant
IBM Corporation | System Lab Services
925-822-3977 (Office)
925-457-9221 (cell phone)
mailto:ron.delaware@us.ibm.com




From: Marc Lanteigne <marclanteigne@CA.IBM.COM>
To: ADSM-L@VM.MARIST.EDU
Date: 07/11/2019 07:15 AM
Subject: [EXTERNAL] Re: [ADSM-L] Number of volumes per client
Sent by: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU>



Hi Eric,

You could do something like this:

select node_name,count(distinct(volume_name)) from contents group by node_name

-
Thanks,
Marc...
___________________________________________________
Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
416.478.0233 | marclanteigne@ca.ibm.com
Summer Hours: Monday to Thursday, 6:00 to 16:30 Eastern

Latest Servermon for Spectrum Protect:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectServermon&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=sFMbbETX5Vd-NzoKeMgvqidCuJ4lqzzWfrbvhCmp57E&e=

Performance Mustgather for Spectrum Protect:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectPerformanceMustgather&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=1i1fKfzAmWtRtt_3kdvSRz48l_g0KoSdNZV97yLWULY&e=

Spectrum Protect Blueprint:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IBMSpectrumProtectBlueprints&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hLJKqiq5uZKR2bRDOYSNbd0R_g8juynSkOjr_umT9g4&m=vEW1Kb5UUNJPB6-7WBTG96C-LFZSJeY_p5sCXJARzBE&s=U_G1kdJgxQvMe1i8Y98Tj8lNOFw_D-Ua0VSfBT5vPsQ&e=


Follow me on: Twitter, developerWorks, LinkedIn



-----Original Message-----
From: Loon, Eric van (ITOP NS) - KLM <Eric-van.Loon@KLM.COM>
Sent: Thursday, July 11, 2019 10:58 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] Number of volumes per client

Hi guys,

I'm looking for a SQL statement to count the amount of volumes used per client. Can anybody help me out here?
Thank you very much in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
********************************************************
For information, services and offers, please visit our web site:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.klm.com&d=DwIFAg&c=jf_iaSHvJObTbx-siA1ZOg&r=hMBqtRSV0jXgOdXEmlNk_-O9LHkPCGSh9PJBRSlL8Q4&m=nfOo48SnthJS2-_HlS7z2-jiv1rUtIJ711DzUTOx0LY&s=nT8p19UU2HYBIURwdYRBqCDMI_GY8rLissfAm5ZG45E&e=
..
This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286
********************************************************
********************************************************
For information, services and offers, please visit our web site: http://www..klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286
********************************************************
This message was imported via the External PhorumMail Module
Sorry, only registered users may post in this forum.

Click here to login