Discovered a join problem. Need to have both node_name and Filespace_ID in the equivalence between the tables. Otherwise you are joining only on the Filespace_ID which can be the same for multiple nodes. Thus giving large errors.
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.Node_Name=OC.Node_Name AND FS.Filespace_ID=OC.Filespace_ID group by FS.Node_name, FS.FILESPACE_NAME, FS.Filespace_ID, FS.AGE ORDER BY FS.NODE_NAME [image: OCIO-logo-Transparent.png] <https://ocio.iowa.gov/> 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 [image: social media icons_Twitter cir.png] <https://twitter.com/IowaOCIO>[image: social media icons_LinkedIn cir.png] <https://www.linkedin.com/company/state-of-iowa> On Thu, Jan 16, 2020 at 8:11 PM Bronder, David M <david-bron...@uiowa.edu> wrote: > 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 >
