Re: [ADSM-L] SQL statement

2016-04-11 13:35:36
Subject: Re: [ADSM-L] SQL statement
From: Robert Talda <rpt4 AT CORNELL DOT EDU>
Date: Mon, 11 Apr 2016 17:34:09 +0000

  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
rpt4 AT cornell DOT edu

> On Apr 11, 2016, at 10:07 AM, Loon, EJ van (ITOPT3) - KLM <Eric-van.Loon AT 
> KLM DOT 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 
> 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: 
> 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
> ********************************************************

<Prev in Thread] Current Thread [Next in Thread>