Somewhere there is a laundry list of dumb spies that miserably failed at doing their jobs for the American government, but we don't know about those guys. We do know about Aldrich Ames though, the pitiful buffoon he was.
Aldrich Ames very well might be the reason that the federal investigator's vetting process includes random drug testing.
Additionally, he was most likely the reason why modern insurgent spies need to pass multiple aptitude and behavioral tests before they're even considered for employment. (Although this hasn't been "federally proven," several failed spy candidates have come forward about their experiences when trying to attain a position within the U.S. government)
Aldrich Ames began his career as a counter insurgent for the CIA against the KGB during the Cold War. For some reason (nepotism, duh) Ames got promoted up to uncharacteristically high ranks despite admitting to multiple firable offenses, insubordination, drunken disorderly conduct, and horrible performance reviews around the board.
Ames worked as a counter-spy alongside the KGB while still employed by the CIA to point out other CIA spies in the Soviet Union.
Many of those CIA operatives were executed by the KGB and the Russian government, while Ames lived comfortably on both an American and Russian salary.
As stupid as the U.S. government was to trust Ames without digging into his personal life for over nine years of employment, they took notice of his treason because his bank account reflected far more money than they were offering him.
When he was approached by the U.S. authorities, he lied his way to safety for nearly six months before they actually did the smallest amount of digging to discover that he was actually a double agent.
He is currently serving a life sentence with no chance of parole.