Was the German Abwehr effective as an intelligence agency during World War II?
If you are asking specifically about the Abwehr, as opposed to German intelligence efforts in general, I would argue that the Abwehr was not particularly effective. Part of this was due to the agency's own failings, but part was due to the fact that it did not enjoy the trust of Hitler and was therefore hamstrung in some important ways.
One problem within the Abwehr was the fact that many of its personnel were not particularly motivated to do their best. The Abwehr was known for its lack of dedication to Hitler and to the Nazi Party in general. Many of the Abwehr's people were simply trying to ride out the war in relative security rather than getting sent to the Eastern Front. This made it difficult for the agency to be very effective. In addition, Abwehr agents did various things, like passing information to the enemy, that are not hallmarks of an effective agency.
Perhaps more importantly, Hitler knew the Abwehr was not solidly on his side. Therefore, he did not trust it as much as he trusted the SS. This meant that Abwehr activities did not always receive the levels of support from above that they might have needed.
The Abwehr did enjoy some success, particularly in counter-espionage operations. However, they were not able to make any major inroads or mount any important operations even in a country like the US that had so many people of German descent who might have been used as agents. (Not implying that all people of German descent such as my grandfather would have been spies, just saying that there were many people who they might have tried to use.)
Thus, the Abwehr was not particularly effective, largely because of its divided loyalties and the fact that Hitler did not trust it.