That's about how I've always encountered it too. I've most often seen it used when people realize they've just had a brainfart, and I've always assumed that it was supposed to be, like, the imaginary sound a brainfart makes.

(The sound kids in my area made when they were mocking the developmentally disabled wasn't anywhere close to 'derp' so I never made a connection to that at all, but now that I know about it I'm glad I never used the word myself.)