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