Starting with Master of Orion, spies are present in virtually every game of this type because they dramatically expand and alter the gameplay in such an important way beyond just the race to military strength that, to me, not implementing them today is a real cut on gameplay. Even, I don't know, Star Wars: Rebellion had spies. Because they are not cosmetic: they can really turn tides for a power with weaker military.
In not one single game that I have ever played has an active espionage and sabotage system amounted to anything more than a rarely-useful system which allows the computer to harass the human player while providing no meaningful avenues of counterplay. Take the system in Distant Worlds, for example: Sure, you'll have a couple of spies, but so do each of the ten or twenty computer-controlled empires, and so do each of the dozens of pirate factions, and if even a couple of them dislike the human player's empire then you'll see a lot more hostile agent activity than you can put out. Furthermore, how do you engage with it - say foreign agents are blowing up random minor space stations in your empire, what do you do? Basically, nothing - you just put up with it and hope that a coinflip goes your way sooner rather than later; maybe you move the responsible parties to the top of your hit list if you know which empires or pirate factions are responsible for the activity, and if you're feeling really vindictive you build a fleet of bombardment ships or go dig up a planet-killer. As far as using it yourself goes, how many developed empires can actually be crippled by a sabotage and assassination campaign? Unless a computer-controlled empire holds the Bakuras High Speed Shipyard, there probably aren't any especially critical spaceports for your agent to blow up; likewise, most resources are plentiful enough that using a couple agents to blow up mines is mostly a waste of time. Similarly, few colonies are valuable enough for a successfully-incited rebellion there to really matter. You're probably not going to successfully assassinate a good character, but even if you do, how much does the loss of a fleet commander, a scientist, an empire leader, or a colony governor really matter? Usually, not much - and the computer probably wasn't making enough of them for assassinating the character to be worth the trouble, anyways. Does triggering a change of government in another empire really help you that much? Probably not; there's no way to control which government type gets chosen, so at best it's a chance to fragment an empire. Basically, these things are ways for the computer to harass the human player; they are not particularly useful tools for the human player to employ against the computer.
Beyond that, the whole espionage and sabotage system is just a hidden RNG roll - hurray, you won a coinflip and so your agent brought back an operations map or blew up some random space station somewhere or whatever. Don't you feel so good about how skillfully you've employed your assets to accomplish your objectives - oh, wait, the only thing you had any influence over was target selection, and then the computer rolled a die somewhere you can't see and said you won. How satisfying.
I find using agents for technology theft is really powerful. It IS very RNG though.