EJ: Are you sure the missing filespaces have data? if not, they won’t have associated occupancy records and thus won’t appear in the output.
I trip over that from time to time myself Robert Talda EZ-Backup Systems Engineer Cornell University +1 607-255-8280 r...@cornell.edu > On Apr 11, 2016, at 10:07 AM, Loon, EJ van (ITOPT3) - KLM > <eric-van.l...@klm.com> wrote: > > Hi guys! > I'm trying to create a SQL statement which should list all filespaces, along > with their occupancy, with a backup date longer than 2 days ago, but only for > nodes with an last access date of today or yesterday. If the node hasn't > contacted the server for two days or more it's reported in a different report. > This is what I came up with thus far: > > SELECT f.node_name AS "Node name", f.filespace_name AS "Filespace", > to_char(char(f.backup_end),'YYYY-MM-DD') AS "Last Backup Date", > CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" FROM nodes n, > filespaces f, occupancy o WHERE o.node_name=n.node_name AND > n.node_name=f.node_name AND o.filespace_name=f.filespace_name AND > days(f.backup_end)<(days(current_date)-2) AND cast(timestampdiff(16, > current_timestamp - n.lastacc_time) as decimal(5,1))>= 2 ORDER BY f.node_name > DESC > > I am however missing several filespaces in the output returned. I must be > doing something wrong but I can't find what. > Thanks in advance for any help! > Kind regards, > Eric van Loon > Air France/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 > ********************************************************
