select @@servername, a.database_id as Database_ID ,a.name as Database_Name, b.name as FileName, substring(b.physical_name, 1,1) as Drive_Letter, b.physical_name as Path,

b.size as File_Size, b.max_size, b.growth, a.create_date,

a.recovery_model_desc, a.page_verify_option_desc

from sys.databases a

inner join sys.master_files b

on a.database_id = b.database_id

declare @dayofthemonth int;

declare @month int;

declare @hour int;

declare @minute int;

set @dayofthemonth = (select DATEPART(day, getdate()));

set @month = (select DATEPART(month, getdate()));

set @hour = (select DATEPART(hour, getdate()));

set @minute = (select DATEPART(minute, getdate()));

with free_hd_space_cte

as

(

select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, convert(int, b.countervalue) as CounterValue, max(convert(datetime, substring(b.counterdatetime,1, 16))) as TimeRead

from counterdetails a inner join counterdata b

on a.counterid = b.counterid

where b.counterid in (select CounterID

from counterdetails

where objectname = 'logicaldisk'

and countername in ('Free Megabytes')

and instancename != '_Total')

and convert(datetime, substring(b.counterdatetime,1, 16)) > getdate() -1

group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername,b.countervalue, b.counterdatetime

--order by a.machinename, a.instancename, a.counterid

)

select distinct MachineName, ObjectName, CounterName, InstanceName, Countervalue, TimeRead

from free_hd_space_cte

where datepart(day, timeread) = @dayofthemonth

and datepart(month, timeread) = @month

and datepart(hour, timeread) = @hour

and datepart(minute, timeread) between 0 and 9

and countervalue < 30

--and (select countername from free_hd_space_cte where countervalue < 30 and countername = '% Free Space')

group by MachineName, ObjectName, CounterName, InstanceName, CounterValue, timeread

--having datepart(minute, timeread) between 0 and 9

order by machinename, countername, countervalue asc, instancename