Madrid had already qualified for the round of 16 as winners of Group G, but the three points were not enough to help CSKA take third position ahead of Viktoria Plzen as the Czech outfit beat Roma to secure the Europa League spot.
It is the first time in history that Madrid have lost a European home tie by more than two goals, and the manner of the defeat will be a concern ahead of the last-16 stage of the competition.
As expected, Madrid boss Santiago Solari rung the changes at the Bernabeu. Jesus Vallejo, Javi Sanchez and Marcelo came into the back four, while Federico Valverde, Isco, Marco Asensio and Vinicius Junior also started.
The first 15 minutes of the match were low-key. Both teams had spells with possession of the ball, but aside from a weak Valverde effort in the early stages, which was gathered by Igor Akinfeev, not too much occurred.
Vinicius was the player making things happen for the home side, but Karim Benzema just failed to meet an excellent low cross from the Brazilian in the 18th minute of action.
Akinfeev made a smart save to keep out a low Vinicius strike in the 23rd minute, before Asensio clipped the top of the CSKA crossbar with a curling effort moments later as Madrid just started to force the issue.
Asensio then tested Akinfeev with a dipping effort during a period where CSKA were finding it difficult to keep the ball.
The CSKA goalkeeper had to make another stop shortly after to deny Asensio, who had been presented with a shooting chance by Benzema.
It was CSKA that made the breakthrough in the 37th minute, however, when the Russian outfit's leading scorer Fedor Chalov expertly placed the ball into the far corner of the net from inside the Real box.
Vinicius curled just wide of the CSKA goal in the 52nd minute as Real, who had introduced Gareth Bale at the interval for the ineffective Benzema, looked to hit back.
Toni Kroos was the next player off the home side's bench in the 58th minute during a period where Los Blancos were dominating the possession but struggling to carve out clear opportunities.
On-loan Everton attacker Nikola Vlasic sent a low strike wide of the Madrid post just past the hour, but the Russian outfit were also finding it tough to create clear chances.
Valverde had a strike deflected into the arms of Akinfeev in the 72nd minute as CSKA stayed 2-0 ahead in Madrid.
That was not the case for too long after, however, with CSKA scoring their third in the 73rd minute when Arnor Sigurdsson found the bottom corner with an excellent strike to stun the Bernabeu once again.
Asensio struck over the CSKA crossbar in the 86th minute as Madrid looked to register, but it was a desperately disappointing night for the reigning European champions.
The evening was to end in disappointment for CSKA also, however, with Plzen's 2-1 home win over Roma meaning that the Russian side finished fourth in the group despite their historic win.
REAL MADRID: Courtois; Odriozola, Vallejo, Sanchez, Marcelo (Carvajal 74'); Valverde, Llorente (Kroos 58'), Isco; Asensio, Benzema (Bale 46'), Vinicius
CSKA MOSCOW: Akinfeev; Fernandes, Magnusson, Becao, Nababkin, Shchennikov; Oblyakov (Kuchaev 89'), Bistrovic, Vlasic; Chalov (Hernandez 84'), Sigurdsson (Nishimura 94')