-1

Dears, I have written below inner join query to get the data from my database tables. But output of this data is coming up with almost 3k lines. Output is below. I need data in only unique recent "TimeSampled" rows

enter image description here

select DISTINCT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where ObjectName = 'Processor' AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
AND DisplayName Like '%'
group by  [PATH], [DisplayName], [ObjectName], [CounterName], [InstanceName], [SampleValue]

How I can retrieve results with only last Time Sampled rows with respect to DisplayName. I have almost 5k servers (DisplayName) like this to retrieve the data.

Request to need to a help to achieve this.

  • Replace SampleValue (it seems it is ) with FIRST_VALUE(SampleValue) OVER (PARTITION BY DisplayName ORDER BY TimeSampled DESC), remove it from GROUP BY and (maybe?) add DISTINCT. If CounterName and/or InstanceName can differ within the same DisplayName do the same with them. – Akina Jul 5 '19 at 5:51
  • 1
    This is the second time you've posted this (dba.stackexchange.com/q/242045/181661) – Chris Rolliston Jul 5 '19 at 7:11
0

Using ROW_NUMBER() OVER PARTITION can allow you to select the top row per distinct value, in this case the DisplayName by only selecting where the ROW_NUMBER() = 1 in an outer query.

I've included this using your query below for an idea, you may need to adjust further to suite your needs.

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM
(
    select DISTINCT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
    ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY TimeSampled DESC) DispCount
    from PerformanceDataAllView pdv with (NOLOCK)
    inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
    inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
    where ObjectName = 'Processor' AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
    AND DisplayName Like '%'
    group by  [PATH], [DisplayName], [ObjectName], [CounterName], [InstanceName], [SampleValue]
) s
WHERE s.DispCount = 1
|improve this answer|||||
  • Thanks alot Liber. Your query has worked for me with minor changes. – Ramu Chittiprolu Jul 5 '19 at 10:56
0

Thanks alot Liber. Your query has worked for me with minor changes.

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM
(
    select DISTINCT bme.DisplayName , pcv.ObjectName, pcv.CounterName, pcv.InstanceName, pdv.SampleValue, max(pdv.TimeSampled) as TimeSampled,
    ROW_NUMBER() OVER (PARTITION BY bme.DisplayName ORDER BY pdv.TimeSampled DESC) DispCount
    from PerformanceDataAllView pdv with (NOLOCK)
    inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
    inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
    where ObjectName = 'Processor' AND TimeSampled >= DATEADD(MINUTE,-15,GETDATE())
    AND DisplayName Like '%'
    group by  bme.PATH, bme.DisplayName, pcv.ObjectName, pcv.CounterName, pcv.InstanceName, pdv.SampleValue,pdv.TimeSampled
) s
WHERE s.DispCount = 1 
order by TimeSampled desc
|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.