Couple of ways of doing this.
1. You could create Supervisor Workflow within the Supervisor Desktop. This can use an "Audible Alert" trigger.
2. You could create a custom wallboard - which queried the state of the CSQ queues, and played an audible alert.
Option (2) has the advantage of not requiring the Supervisor Desktop to play the alert. Option (1) is simpler as is all pretty much out of the box functionallity.
Intrinsic Network Soutions
I have also seen a customer that configured the AEF script to do a Place Call step to an analog port on CUCM that was attached to a night bell/strobe. The problem is that every call that reaches the threshold will do the same thing; it's very difficult (read: a global session and date/time variable comparisons) to rate limit this to only happen every so often.