I was trying to create query which returns the number/percentage of failed backups. Since I don't want to be dependant on third-party software, I am digging the MSDB database for this answer. However, I can't find this information. It seems an aborted backup is doing a rollback or am I wrong?
Failed backups are not stored there. You'd need to record that based on the failure report from whatever called the backup. I'd assume that would be SQL Agent in the majority of cases. You can capture the failed jobs there.
Have you looked at using master..sysdatabases and join on the msdb..backupset to find the successful backups say the last 24 hours if you backup daily. From that you can see which ones were not backed up within the last 24 hours.