Sql query - specific time frame

I have installed the SQL Plugin Demo Dashboard. I would like to change the sql query for “20 noisiest objects” and “10 most common alerts” to show results for a specific period of time, the last 7 days, last 30 days and so on, but I have not been able to do that. Any help would be appreciated.

1 Like

Here’s what I have in place to cover the Most Common Alerts part of your question. I’ve highlighted the part of the t-SQL where the duration is applied. The key is to toggle the first parameter for the dateadd function from d to m to y based on if you need to query by day, month or year.

Most Common Alerts – Last 1 Hour:

select count(*) as “Alerts”,VA.AlertName as “Alert Name”, VMET.ManagedEntityTypeDefaultName as “Target Object”, 
VMP.ManagementPackDefaultName as “Management Pack”
from Alert.vAlert VA
JOIN vManagedEntity VME ON VA.ManagedEntityRowId = VME.ManagedEntityRowId
JOIN vManagedEntityType VMET ON VME.ManagedEntityTypeRowId = VMET.ManagedEntityTypeRowId
JOIN vManagementPack VMP ON VMET.ManagementPackRowId = VMP.ManagementPackRowId
where VA.DWCreatedDateTime > dateadd(hh,-1,getutcdate())
group by VA.AlertName, VMET.ManagedEntityTypeDefaultName, VMP.ManagementPackDefaultName
order by “Alerts” desc

Most Common Alerts – Last 24 Hours

select count(*) as “Alerts”, VA.AlertName as “Alert Name”, VMET.ManagedEntityTypeDefaultName as “Target Object”,
VMP.ManagementPackDefaultName as “Management Pack”
from Alert.vAlert VA
JOIN vManagedEntity VME ON VA.ManagedEntityRowId = VME.ManagedEntityRowId
JOIN vManagedEntityType VMET ON VME.ManagedEntityTypeRowId = VMET.ManagedEntityTypeRowId
JOIN vManagementPack VMP ON VMET.ManagementPackRowId = VMP.ManagementPackRowId
where VA.DWCreatedDateTime > dateadd(hh,-24,getutcdate())
group by VA.AlertName, VMET.ManagedEntityTypeDefaultName, VMP.ManagementPackDefaultName
order by “Alerts” desc

Most Common Alerts – Last 7 Days (change the 7 in the bolded text to desired days as needed for 1 month , e.g. 30):

select VA.AlertName as “Alert Name”, VMET.ManagedEntityTypeDefaultName as “Target Object”, 
VMP.ManagementPackDefaultName as “Management Pack”, count(*) as “Number” 
from Alert.vAlert VA
JOIN vManagedEntity VME ON VA.ManagedEntityRowId = VME.ManagedEntityRowId
JOIN vManagedEntityType VMET ON VME.ManagedEntityTypeRowId = VMET.ManagedEntityTypeRowId
JOIN vManagementPack VMP ON VMET.ManagementPackRowId = VMP.ManagementPackRowId
where VA.DWCreatedDateTime > dateadd(dd,-7,getutcdate()) 
group by VA.AlertName, VMET.ManagedEntityTypeDefaultName, VMP.ManagementPackDefaultName order by “Number” desc, “Management Pack”
1 Like

Thanks, that was spot on for the most common alerts part of my question. The query syntax is very different from the one in the demo dashboard, so I am not able to use the “where VA.DWCreatedDateTime > dateadd(hh,-1,getutcdate())” part of you query in the query for the “most noisiest objects”. I tried

SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS [#], me.DisplayName AS “Object name”, COUNT(al.ManagedEntityRowId) AS “Alert count”FROM Alert.vAlert al JOIN vManagedEntity me on me.ManagedEntityRowId = al.ManagedEntityRowId where VA.DWCreatedDateTime > dateadd(hh,-1,getutcdate()) GROUP BY me.ManagedEntityRowId, me.DisplayName ORDER BY COUNT(al.ManagedEntityRowId) DESC

That did not work. If anyone has a query that would return the x most noisiest objects during a specific time frame, like 7 days, it would be very useful.

I should maybe have added more context to my first question. We try to implement weekly meetings and go over the most common alerts and noisiest objects in order to improve our environment. I think a dashboard with this information is a very good tool in this respect.

How does this work? I had to consult with my DBA and he added a convert to the DWCreatedTime. Should do the trick

SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS [#], me.DisplayName AS "Object name", COUNT(al.ManagedEntityRowId) AS "Alert count" FROM Alert.vAlert al JOIN vManagedEntity me on me.ManagedEntityRowId = al.ManagedEntityRowId 
where (CONVERT(datetime, al.DWCreatedDateTime, 102) >= GETDATE() - 5) 
GROUP BY me.ManagedEntityRowId, me.DisplayName ORDER BY COUNT(al.ManagedEntityRowId) DESC
3 Likes

Works great, now have views for last day/week/month and all. Much appreciated.

I’ve just submitted a dashboard to the community that uses the SQL plugin to show top 10 common alerts for the last 1, 7 and 30 days as well as the same view for noisiest objects. We hold similar meetings with our management pack business owners so this will be very useful for us. It’s pending moderation now and if approved you’ll be able to download it soon from the dashboard section of the community site.

Just a note on using Community Answers, you should try to add responses like this as a comment rather than a new answer. Glad you got an answer to your question :slight_smile: