March 4, 2010 at 8:23 am
when i run this i get data for the last day. i'm trying to get it to return only for the latest date.
for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work
with free_hd_space_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, 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', '% Free Space')
and instancename != '_Total')
and convert(datetime, substring(b.counterdatetime,1, 19)) > 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 MachineName, free_hd_space_cte.ObjectName, free_hd_space_cte.CounterName, free_hd_space_cte.InstanceName, free_hd_space_cte.CounterValue, max(TimeRead) as TimeRead
from free_hd_space_cte
group by MachineName, ObjectName, CounterName, InstanceName, CounterValue, timeread
March 4, 2010 at 9:23 am
Please provide some sample data to play with.
The best way to post sample data is described in the first link in my signature.
March 4, 2010 at 3:38 pm
you may be able to do it using "row_number" with "partition" by drive and "order" by date in descending order, then filter out those items that are not 1 in the row_number column.
as SSCrazy said, with test data to play with, this can be easier for all of us.
March 4, 2010 at 4:00 pm
Alright, I have no idea if the data I created is representative of what you have. After playing for a bit, I think it's a simple bit of changing this line:
and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate() -1
to this:
and b.counterdatetime in (select max(counterdatetime) from counterdata group by counterid)
Let me know if that was close.
And the example from which I was working:
--Sample data code made possible by inspiration from (and blatant plagiarism of) Jeff Moden... thanks, Jeff!
/* This guy is going to allow us to have multiple values for the same counter id later on*/
if object_id('tempdb..#seq')is not null drop table #seq
select * into #seq from (
select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union
select 11 union select 12 union select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union select 19 union select 20
)a
/*This is, to the best of what I could figure out, a good sample for your detail table*/
if object_id('tempdb..#counterdetails')is not null DROP TABLE #counterdetails
select counterid = IDENTITY(INT,1,1),* INTO dbo.#counterdetails from (
SELECT TOP 10
machinename = CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
objectname = 'logicaldisk',
countername = 'Free Megabytes',
instancename = CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
union all
SELECT TOP 10
machinename = CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
objectname = 'logicaldisk',
countername = '% Free Space',
instancename = CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
)as dtable
/*And this is for the data table*/
if object_id('tempdb..#counterdata')is not null drop table #counterdata
select top 20
counterid = 1,
counterdatetime= CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
countervalue = ABS(CHECKSUM(NEWID()))%50000+1
INTO dbo.#counterdata
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
,#seq
/* This piece just creates more data for each counterid using our #seq table from earlier*/
insert into #counterdata (counterid,counterdatetime,countervalue)
select num,CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),countervalue
FROM #counterdata,#seq
/* Below here is a slightly modified version of your query, just trying to make things easier on myelf */
select MachineName, ObjectName, CounterName, InstanceName, CounterValue, max(TimeRead) as TimeRead
from
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue
, /*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', '% Free Space')
and instancename != '_Total'
)
--and convert(datetime, substring(b.counterdatetime,1, 19)) > getdate() -1
and b.counterdatetime in (select max(counterdatetime) from #counterdata group by counterid)
group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername,b.countervalue, b.counterdatetime
--order by a.machinename, a.instancename, a.counterid
)as derivedtable
group by MachineName, ObjectName, CounterName, InstanceName, CounterValue, timeread
March 9, 2010 at 8:17 am
the data is the ODBC tables that perfmon creates when you set it to log to a database rather than a file
thx, going to play with some of the advice here. i got it to filter out a lot of dupes, but still get some
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply