Dynamic Alert query
-
Hello,
I have a requirement to send an Alert based on Silo Level data , when it hits <= 30%
This is more a process design question....I've created a view of silo level data, which is called by a Power automate flow, scheduled every hour to run. which is working well. except the problem now is that the recipients are being spammed with Alerts.
I'm looking for some help figuring out a way to setup a query where I Flag the data once it has been called for the first time and then suppress the data until it resets and goes back over the 30% threshold.
SELECT TOP (5) [datetime]
,CASE meshtype
WHEN '40/70' THEN 'Silo1 -40/70'
WHEN '50/140' THEN 'Silo2 -50/140'
WHEN '40/140 - 1' THEN 'Silo3 -50/140'
WHEN '40/140 - 2' THEN 'Silo4 -40/140'
WHEN '40/140 - 3' THEN 'Silo5 -40/140'
WHEN 'Non Frac' THEN 'Non Frac'
end as Silos
,[whsecd]
,[plant]
,[meshtype]
,[siloinventory]
,CASE meshtype
WHEN '40/70' THEN Cast(round([Siloinventory]*100/2000 ,2) as numeric(38,2))
WHEN '50/140' THEN Cast(round([Siloinventory]*100/2000 ,2) as numeric(38,2))
WHEN '40/140 - 1' THEN Cast(round([Siloinventory]*100/5000 ,2) as numeric(38,2))
WHEN '40/140 - 2' THEN Cast(round([Siloinventory]*100/5000 ,2) as numeric(38,2))
WHEN '40/140 - 3' THEN Cast(round([Siloinventory]*100/5000 ,2) as numeric(38,2))
end as TonsPerc
FROM [dbo].[silo_inventory]
where whsecd =211 and meshtype <> 'Non Frac'
order by 1 desc
Hope that makes sense. Thanks in advance.
-GJ
-
Do you have any database monitoring tools (such as RedGate SQL Monitor)? If so, put the alerts in there.
If that isn't an option, I think you are going to need to capture the data in a permanent table and include a bit field to indicate a notice has been sent. So, with your above example, I would create a table with the coulmns you listed above and add 1 extra bit field for Alert sent. Then in your stored procedure that grabs the data, it does a merge of the above data into the new table based on the Silo (and any other thing that uniquely identifies a row). The merge would update existing rows where the Silo value exists and insert where it didn't exist. I would not DELETE any rows for silos that don't exist as they may be helpful for historical purposes, but that is your call.
Once the data is merged in, I would next update all rows to set the bit alert sent to 0 where the percent is under 30. Next, send an alert where percent is over 30 and bit alert sent is 0. Finally, set bit alert sent to 1 where percent is over 30.
Maybe not the most elegant solution, but should work. The other thing I'd (likely) do is have a second alert that fires daily or weekly to let people know of cases where the percent is over 30 for an extended period of time. It could be that sending out a single alert gets ignored and the problem persists longer than it should. You could do this by having a second datetime column that is set to 0 (january 1st, 1900) until the percent goes over 30%. On the date/time it goes over 30% you update the second column. Then if the datediff between the new column and the datetime column is a multiple of 7 days, you send out the alert again.
Just how I'd approach it. I like the "use the tool" approach best because it is easy to configure and monitor. But it depends on your setup and tools available.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Would it be feasible for you to use a different SQL Server Alert for each silo? That is, the Alert would be the same except for silo name, but you would have a unique Alert for each silo. (On the plus side, that would allow you to use a different % for different silos in the future.)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Brian,
Thank you for the ideas around the approach. we don't have Redgate unfortunately
I've followed your suggestion on the merge table with the bit "Alert sent" column. I think that will work. The piece I'm still missing is when the SP runs again in an hour , it will bring in basically the same data , maybe slight different volume % i.e. when SP previously ran and sent alert, the percentage was 22% now an hour later when it runs again its 28% but still under 30% threshold and therefore it will trigger again. (trying to avoid the Spam factor) maybe a 2nd bit column to deal with repeats ?
I like the other idea of a 2nd Alert for prolonged low levels.
thanks
-GJ
-
Scott, thanks.
Not sure that would work in our case, also not quite sure it it would address the ability to suppress certain reports and reset etc.
-
If you don't want second alerts, what I might do is log alerts as they are sent based on a silo and time. If you have sent an alert in some recent time, the don't send additional alerts.
This is complex, because if you run this ever hour, how can you tell things were low at 9am, fine at 10am, and low at 11am? Monitoring tools don't always do this well, either, but re-raising an alert is often important for users.
If I were doing this, sending an alert every hour when this isn't fixed might be the best thing.
Of course, you could store each hour's data in the table and then use window functions to check if the previous row(s) for a silo all contain the same low percentage. In that case, you wouldn't send a second alert. If you keep these batches of data, then I'd be sure you have some archive/trim process to limit data growth.
- gjoelson 29755 wrote:
Scott, thanks.
Not sure that would work in our case, also not quite sure it it would address the ability to suppress certain reports and reset etc.
SQL alerts have a built-in capability to be delayed for a specific period of time. But, yes, you'd need additional logic in the code fired by the alert to handle disabling / "stalling" the alert until the threshold was crossed again. I understand, though, why you might want to not use this approach at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply