Espionage can be quite effective, if you learn how to use it right. I was having a ball with it playing as an HRE minor.
Sabotage reputation can make the difference between everyone loving an expansive Austrian emperor, or being in a mass coalition against them. Pop a sow discontent on a country that is ethnically or religiously diverse (Commonwealth for example)... during a war. Drive their war exhaustion to 20, and their stability will drop to -3. It's going to be a pain for them to recover with the +50% cost (especially if they're religiously diverse), and when they do, they'll be a shadow of their former selves with all the rebels popping everywhere (which you want to support for new vassals). There's often no need to take territory directly, so you can show superiority for massive power projection gains (which increases again with rebel support), and have zero aggressive expansion.
I do wish espionage in the game was a little bit more in-depth. Perhaps some DLC would be warranted - but I wouldn't say espionage is useless in the current state.
In theory that all sounds cute but in practice situations when you can really do significant damage with spies
which couldn't be done with other means are really rare.
When I need diplo points for diplo annexation, or trade stuff, or Diplomatic/Trade/Exploration ideas, well, Espionage is really low priority.
I think espionage should be greatly expanded. Allow us on
- killing heirs, generals, advisors
- sabotaging diplo relations of target country with another countries
- show bar 'how much rebel progress is going to rise' when I am supporting rebels
- decreasing stability
-
offering enemy mercenaries 2x higher payment to make them leave enemy army and join closest army belonging to you
- damaging ships
- increasing inflation [this effect would have to be great to cause difference]
-
sabotaging enemy forts, allowing on winning sieges quicker
- 'double - agent' option where your agent joins enemy army and decreases its morale/slows down its movement speed
- sabotaging enemy trade in nodes
- slowing down coring, diploannexation, westernization

- just freakin' stealing money
- converting provinces to your faith
Etc.
See? There are so many interesting possibilities. I don't think all of them would be impossible to balance.