The defending champions were shown three yellow cards in the second half, which ultimately proved their undoing.
Former Wallabies coach Michael Cheika called the ending "absolute carnage".
"It means a lot," O'Connor said after the win. "I'm super proud. It was a tough contest. It always is with the Brumbies, credit to them because that was a Test match.
"I'm just super proud of my boys. We started a bit slow again, but we got there. I'm bloody stoked."
Brumbies captain Allan Alaalatoa said: "We've got to be proud of that effort. We spoke about resilience coming out here against 40,000 people and we really dug deep.
"To all our supporters back home, and our families, thank you so much and we'll go again next week in the Trans Tasman."
The hosts were awarded a penalty two minutes after kick-off, and Queensland captain James O'Connor held his nerve to slot the three-pointer from more than 40 metres out.
But the defending champions struck back soon after, with young gun Noah Lolesio setting up fullback Tom Banks with a classy pop pass for the opening try, briefly silencing the Brisbane crowd.
However, the Brumbies were utterly outclassed in the scrum throughout the first half, with Taniela Tupou relentlessly dominating his rival prop.
The Reds thought they had mustered their first try of the match when Feao Fotuaika dived over in the 29th minute.
But replays showed the foot of Brumbies halfback Nic White had intervened, with referee Nic Berry adjudicating the Queensland prop took too long grounding the ball.
The Reds had to settle for a penalty kick as a chorus of boos echoed around the venue.
The visitors extended their lead back to four points when Lolesio slotted a three-pointer from the other end in the 32nd minute.
Moments before the halftime siren, the Brumbies fly half booted another penalty kick to give his side a seven-point lead.
Jordan Petaia came inches from pulling off a miracle try on the right wing early in the second half, but his body had gone into touch moments before grounding the ball.
Soon after, Reds forward Harry Wilson was helped off the ground after suffering a head knock while attempting a tackle.
Queensland captain James O'Connor missed a crucial penalty kick in the 47th minute, striking the left post from 33 metres out, but redeemed himself soon after to reduce the deficit to just four points.
Rob Valetini was shown a yellow card in the 61st minute after a dangerous tackle, leaving the Brumbies with 14 men for 10 minutes.
Soon after, O'Connor booted his fourth penalty kick of the night to set up a thrilling finish.
Another crucial Lolesio three-pointer extended the Brumbies lead back to four points as Valetini rejoined the action in the 72nd minute.
With less than five minutes remaining on the clock, the Reds started bravely called for scrums and lineouts instead of taking regulation penalty kicks in front of goal.
The Brumbies were shown another yellow card following a Darcy Swain infringement while defending on the tryline.
The visitors botched a lineout in the dying moments, gifting the Reds one final chance to push for an unlikely win.
Queensland thought they had mustered the decisive try in the 81st minute, but they were instead awarded another scrum in front of the posts.
If things weren't dramatic enough at Suncorp Stadium, the Brumbies were then reduced to 13 men with Luke Reimer sent to the bin.
Berry continued to award penalties as the Reds relentlessly peppered the opposition tryline, but the defiant Brumbies showed their class in defence with just 13 men.
However, the decisive moment finally came in the 85th minute, with O'Connor diving over the end a thrilling contest.
