I think you can do it without the explicit join (I'm sure an actual SQL
expert could explain the difference). (I also added some formatting of the
age and occupancy output):
SELECT '$myTsmServer' AS TsmServer, FS.Node_Name, FS.Filespace_Name, -
FS.Filespace_ID, SUM(OC.Num_Files) as NUM_FILES, -
FS.Age || ' days' as "LAST_BACKUP" -
FROM (SELECT Node_Name, Filespace_Name, Filespace_ID, -
DAYS(Current_Date)-DAYS(Backup_End) AS Age -
FROM Filespaces -
WHERE days(Current_Date)-DAYS(Backup_End)>$myAgeLimit) FS, -
Occupancy OC -
WHERE FS.Filespace_ID=OC.Filespace_ID -
GROUP BY FS.Node_Name, FS.Filespace_Name, FS.Filespace_ID, FS.Age -
ORDER BY FS.Node_Name
If it's OK to report the date of the last backup completion instead of the
number of days since the last backup, the following is a slightly simpler
query (you could also cast/trim the time out of the end date):
SELECT '$myTsmServer' AS TsmServer, FS.Node_Name, FS.Filespace_Name, -
FS.Filespace_ID, SUM(OC.Num_Files) as NUM_FILES, FS.Backup_End -
FROM Filespaces FS, Occupancy OC -
WHERE FS.Filespace_ID=OC.Filespace_ID and -
DAYS(Current_Date)-DAYS(FS.Backup_End)>$myAgeLimit -
GROUP BY FS.Node_Name, FS.Filespace_Name, FS.Filespace_ID, FS.Backup_End -
ORDER BY FS.Node_Name
=Dave
On 1/15/20 2:00 PM, Hall, Lynn wrote:
> SELECT '$myTsmServer' AS TsmServer, FS.Node_Name, FS.Filespace_Name,
> FS.Filespace_ID, FS.Age, SUM(OC.Num_Files) FROM (SELECT Node_Name,
> Filespace_Name, Filespace_ID, DAYS(Current_Date)-DAYS(Backup_End) AS Age
> FROM Filespaces where days(Current_Date)-DAYS(Backup_End)>$myAgeLimit) FS
> Join Occupancy OC on FS.Filespace_ID=OC.Filespace_ID group by FS.Node_name,
> FS.FILESPACE_NAME, FS.Filespace_ID, FS.AGE ORDER BY FS.NODE_NAME
>
>
> Lynn Hall
> Information Technology Specialist IV
> Platform Services
> Office of the Chief Information Officer
> 1305 E Walnut Street | Des Moines, IA 50319 - Mailing
> mobile 515-669-4325
> ocio.iowa.gov
>
>
> On Wed, Jan 15, 2020 at 6:45 AM Schaub, Steve <steve_sch...@bcbst.com>
> wrote:
>
>> SP 8.1.9
>> I'm trying to write a Powershell script that will return data on all
>> filespaces that haven't been backed up in > x days. I've had the basic
>> query from the Filespaces table working for years, but now I'm trying to
>> include the total object count from the Occupancy table for each filespace,
>> and I'm not adept enough at query writing to do that. I suspect I need
>> some flavor of join but not sure which one.
>>
>> Here was my first attempt, more to show what I'm trying to do, it
>> obviously isn't working. If anyone has this working and is willing to
>> share, I'd appreciate it.
>>
>> SELECT '$myTsmServer' AS TsmServer, FS.Node_Name, FS.Filespace_Name,
>> FS.Filespace_ID, DAYS(Current_Date)-DAYS(FS.Backup_End) AS Age,
>> SUM(OC.Num_Files) FROM Filespaces FS, Occupancy OC WHERE
>> FS.Filespace_ID=OC.Filespace_ID AND FS.filespace_id=OC.filespace_id AND
>> DAYS(Current_Date)-DAYS(FS.Backup_End)>$myAgeLimit
>>
>> Steve Schaub
>> Systems Eng II, Backup & Recovery
>> BlueCross BlueShield of Tennessee
>>
--
Hello World. David Bronder - Systems Architect
Segmentation Fault ITS-EI, Univ. of Iowa
Core dumped, disk trashed, quota filled, soda warm. david-bron...@uiowa.edu
