Use of this skill in the Ruins of Lordaeron allowed for players to see undead level 15 ghosts floating around in the court yard.
In Raven Hill, Duskwood. Inside the houses, there are located invisible "passive" mobs, ranging from 55-56. Earlier used for farming Soul Shards among the Alliance warlocks, to a lesser extent Horde warlocks.
Tips and tactics
Detect Invisibility was needed to see some of the succubi in the Arcatraz where the second and third bosses are.
In PvP, Detect Invisibility allowed buffed players to see mages who were using the Invisibility spell. However, the buff also allowed invisible mages to see the buffed player.
Removed in Patch 4.0.1., prior to the release of the Cataclysm Expansion