Tuesday, September 7, 2010

Find all machines with less than 100MB free space in their C Drive

select distinct
SYS.Name0 as 'Machine Name',
SYS.Resource_Domain_OR_Workgr0 as 'Domain',
SYS.User_Domain0 + '\' + SYS.User_Name0 as 'Username',
ASSG.SMS_Installed_Sites0 as 'SMS Sitecode',
Site.PrimaryOwnerName0,
Build.Caption0 as 'OS',
Build.CSDVersion0 as 'SP Level',
ldisk.deviceid0 as 'Drive Letter',
ldisk.Size0 as 'Total Drive Size',
ldisk.freespace0 as 'Free space avaiable',
scan.LastHWscan as 'Last Hardware Scan',
DateDiff(Day,scan.LastHWscan,GetDate()) as 'No. of Days Since Last scan'
FROM
v_R_System as SYS inner join v_RA_System_SMSInstalledSites as ASSG on ASSG.ResourceID = SYS.ResourceID
inner join v_GS_OPERATING_SYSTEM as Build on SYS.ResourceID=Build.resourceid
Inner JOIN v_GS_LOGICAL_DISK as LDISK on SYS.ResourceID=LDISK.ResourceID
inner join v_GS_COMPUTER_SYSTEM as Site on SYS.resourceid=Site.resourceid
Left outer Join v_GS_WORKSTATION_STATUS as scan on sys.ResourceID=scan.ResourceID
WHERE
LDISK.DriveType0=3 AND
ldisk.deviceid0='C:' AND
ASSG.SMS_Installed_Sites0 like '%'
AND build.Name0 not like '%server%'
and SYS.Obsolete0<>1
and ldisk.freespace0<=100
ORDER BY SYS.Name0

Description:

The Above query finds all the SMS client machines(workstations) those who have 100MB or below free space on their C Drive.

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home