Hi,I'm looking for some help on SQL statement as well. Would you have a statement that lists just the amount of active data a node has stored - not the active and inactive... Thank youMichael
On Thursday, January 28, 2016 10:08 AM, William Sefranek <wtsef...@buffalo.edu> wrote: Eric, One other line you can add to the select section is: DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP - Which will list the number of days since this filespace completed a backup. I use that in our query to list old filespaces due for cleanup. Bill On 1/28/2016 9:58 AM, Skylar Thompson wrote: > Hi EJ, > > I think this will do the trick: > > SELECT - > f.node_name, - > f.filespace_name, - > f.backup_end, - > CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" - > FROM - > filespaces f, - > occupancy o - > WHERE - > o.node_name=f.node_name - > AND o.filespace_name=f.filespace_name - > AND days(f.backup_end)<(days(current_date)-30) - > ORDER BY o.physical_mb DESC > > On Thu, Jan 28, 2016 at 02:43:54PM +0000, Loon, EJ van (ITOPT3) - KLM wrote: >> Hi guys! >> I'm trying to join the filespaces and occupancy tables in one SQL statement, >> but I get stuck on the point where I want to calculate with the amount of >> days... >> I would like to create a list of filespaces (node_name, >> filespace_name,backup_end) which are not backed up for more than 30 days, >> along with the amount of data stored for them in GB >> (cast(round(physical_mb/1024) as int) as "GB Stored"), sorted on size, the >> largest first. >> Can anybody help me out here? Thank you very much for your help in advance! >> Kind regards, >> Eric van Loon >> AF/KLM Storage Engineering >> ******************************************************** >> 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 >> ******************************************************** > -- > -- Skylar Thompson (skyl...@u.washington.edu) > -- Genome Sciences Department, System Administrator > -- Foege Building S046, (206)-685-7354 > -- University of Washington School of Medicine > > -- William Sefranek University of Buffalo Enterprise Infrastructure Services (716)645-5116