identify hosts with most qos data

[Steve B]

hi guys,

just curious if there's a quick and easy way to find out which hosts are producing the most qos data. We have a few thousand hosts and am curious if there are any probes in our fleet configured to check every second...

cheers

[Luc Christiaens]

This is a small query to see what devices are creating the most qos metrics:
-----------
SELECT top 15 sd.robot, count(sd.probe) '#qos'
FROM S_QOS_SNAPSHOT ss with(nolock)
JOIN S_QOS_DATA sd with(nolock) on ss.table_id = sd.table_id
group by sd.robot
order by #qos desc
------------
But this query will not show how many rows of data each device will produce. (this can be obtained on qos level)
It will also not show the configuration setting if it's set to check every second, because this is set in the local config file or mcs profile.

[Steve B]

Thanks as always Luc

We've got one host that has over 180,000 qos records that doesn't even have a robot on it which we'll need to investigate. Otherwise the majority of "top" hosts are the UIM hubs as expected. 

Thanks again :)

[Keith Kruepke]

Each RN_QOS_DATA_xxx table includes a samplerate column. It looks like that contains the # of seconds between samples. ​It's saved with each sample, so it's not a terribly convenient thing to check, especially because that also means it is split across all of the RN_QOS_DATA_xxx tables. But it may be useful to check at least some of those tables for rows that have an unexpected value in the samplerate column. That kind of query might be a bit slow too; I wouldn't necessarily expect that column to be indexed.

[Steve B]

Thanks for this info..

I wonder if there's a query to show a "top 50" list of hosts from say the last week/month with the most QoS data. It would be good to help identify hosts that may have had their intervals set to "every second" or "every 5 seconds" for troubleshooting but never got disabled.

[Luc Christiaens]

This is another view on your qos data with the number of rows for each qos metric. (query saved from communities in the past)
--- query removed because it shows invalid results in more recent sql versions ---

[Steve B]

Thanks @Luc Christiaens,

I've run it that query but im struggling to work out what I'm looking at :)


​

[Luc Christiaens]

In my initial test environment (with an older sql version) this displays 1 line for each qos definition sorted by the number of rows used.
BUT in a newer sql the results are totally different, like in your display. (so i remove the query from the post)