Hi,You can try this query: I have added the union results also into this query.
This will limit the rows to 10 for each computer.
with diskspace as (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)] -- ,ROW_NUMBER() OVER (PARTITION BY User
ORDER BY vHSLDISK.TimeStamp DESC) ,
ROW_NUMBER() over (partition by SYS.Name order by vHSLDISK.TimeStamp desc) rno
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 inner join
v_GS_LOGICAL_DISK AS LDISK ON SYS.ResourceID = LDISK.ResourceIDWHERE
(vHSLDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'MAS00E1E') AND
(vHSLDISK.FreeSpace0 >= 500))select [Server Name],[Hardware Type],[Scaned
Date],[System Drive C:],[Free space (MB)] from diskspacewhere rno<=10
http://eskonr.com/2014/06/sccm-configmgr-2012-sql-query-get-most-recent-10-records-with-free-disk-space-history/
Thanks,Eswar Koneti
www.eskonr.com
Date: Sat, 14 Jun 2014 08:03:21 -0700
Subject: Re: [mssms] SQL Query for reporting help - free Disk space
From: [email protected]
To: [email protected]
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.ResourceIDWHERE (vHSLDISK.DeviceID0 = 'c:') AND
(SYS.CollectionID = 'MAS00E1E') AND (vHSLDISK.FreeSpace0 <= 500)
UNION ALLSELECT 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)]
