Detect Invisibility was a spell that allowed a warlock (or its target) to see invisible units so it could attack them. It did NOT allow you to see stealthed units.
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.