Thanks Eswar, I will give that a try. @Reobert, I tried TOP before but did not give me the correct results.
On Sat, Jun 14, 2014 at 6:49 AM, Eswar Koneti <[email protected]> wrote: > you can try with coalesce function as given in the blog post > > http://stackoverflow.com/questions/6270316/sql-server-select-most-recent-records-from-a-group-of-similar-records > > Thanks, > Eswar Koneti > www.eskonr.com > > > > ------------------------------ > Date: Fri, 13 Jun 2014 15:26:28 -0500 > > Subject: Re: [mssms] SQL Query for reporting help - free Disk space > From: [email protected] > To: [email protected] > > Brian mason or Garth > > > On Fri, Jun 13, 2014 at 11:58 AM, elsalvoz <[email protected]> wrote: > > Anyone? > > > On Wed, Jun 11, 2014 at 9:06 AM, elsalvoz <[email protected]> wrote: > > Hello All, > > I'm trying to find a way to query for disk space <500 MB on all servers > last scan plus last 10 history scans. > > I'm able to get the data that I'm after but I can't seem find a way to > limit the history to only latest 10 rows. > > I'm hoping someone has created something similar or can me to adjust this > query. I'm not sure if I'm approaching this task with the best query either > so feel free to suggest a better approach query wise. > > Thanks in advance. > > > SELECT SYS.Name AS [Server Name], CASE WHEN RSYS.Is_Virtual_Machine0 = > 1 THEN 'Virtual Machine' ELSE 'Physical Machine' END AS [Hardware Type], > vHSLDISK.TimeStamp AS [Scaned Date], > vHSLDISK.DeviceID0 AS [System Drive C:], vHSLDISK.FreeSpace0 AS [Free space > (MB)] > FROM v_FullCollectionMembership AS SYS INNER JOIN > v_HS_LOGICAL_DISK AS vHSLDISK ON SYS.ResourceID = > vHSLDISK.ResourceID INNER JOIN > v_R_System AS RSYS ON SYS.ResourceID = > RSYS.ResourceID > WHERE (vHSLDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'MAS00E1E') > AND (vHSLDISK.FreeSpace0 <= 500) > UNION ALL > SELECT SYS.Name, CASE WHEN RSYS.Is_Virtual_Machine0 = 1 THEN 'Virtual > Machine' ELSE 'Physical Machine' END AS [Hardware Type], LDISK.TimeStamp, > LDISK.DeviceID0, LDISK.FreeSpace0 AS [Free space > (MB)] > FROM v_FullCollectionMembership AS SYS INNER JOIN > v_GS_LOGICAL_DISK AS LDISK ON SYS.ResourceID = > LDISK.ResourceID INNER JOIN > v_R_System AS RSYS ON SYS.ResourceID = > RSYS.ResourceID > WHERE (LDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'MAS00E1E') AND > (LDISK.FreeSpace0 <= 500) > ORDER BY [Free space (MB)] > > [image: Inline image 1] > > > > > > >
