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
[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 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 > > > > > ------------------------------------------------------------------------------ > Please see the following link for the BlueCross BlueShield of Tennessee > E-mail disclaimer: > https://www.bcbst.com/about/our-company/corporate-governance/privacy-security/email-policy.page > < > https://www.bcbst.com/about/our-company/corporate-governance/privacy-security/email-policy.page > > >
