I loved that sound in Civ4.
How many time I was making that last of the last one more turn late at night, everything was going fine, and then the dreaded War Horn, not once but twice or more at the same turn. Nights were short back in the day.
But there was a reason to fear it then, I wonder if the AI inability to wage wars for civ5 and civ6 wasn't made on purpose, to boost our day's productivity after a peaceful and long night.
I think a simple tweet is enough to warn you that the AI is going to suicide its army on your defense in the next 3 turns.