Mine is the one about the girl who only likes her man for his Willie Nelson impression:
Da Yoopers are moderately well-known in Michigan and Wisconsin, but pretty obscure everywhere else. My favorite song of theirs is “Rusty Chevrolet.”
Lawn Dart, by Ed’s Redeeming Qualities.
I heard this about 30 years ago on a college station. Kind of a light, dry, dark humor. . .I feel like I’m critiquing a wine. 
Linda Laurie - “Ambrose (Part 5)”:
(Probably well-known among people who know obscurities.)
Susan Christie - “I Love Onions”:
(Classic annoying/catchy novelty song.)
Dufflecoats - “Don’t See Me Soon”:
(Good old-fashioned Indie Pop from a band on The Waaaaah! CD. This song isn’t on it, though.)
Dufflecoats - “Motorbikesong”:
(More Dufflecoats. Faster, more upbeat, imagine a hipster on a cafe cruiser.)
Mariya Takeuchi - “Plastic Love”
(Japanese “City Pop” from 1984. Recently got big on YouTube.)
My first MP3 player around 20 years ago came with a CD of sample MP3s from various indie artists. Two of my favorite songs from the CD are Albert Einstein Dreams
And Taking Up Space.
I’m not sure if these fit the definition of “novelty songs” but they surely must fit “most people haven’t heard them”.
I don’t know about “most people” because they have their popularity. But these are somewhat local to me so you guys probably don’t know them. Full of early 80s misogyny.
B.A.C.O.N. and E.G.G.s
“There’s Another Trumpet in the Sky”
Used to hear it on Doug Pledger Polka Party KSMO
I’d say Christine Nelson’s “Driving Test” song, but it’s not on the Internet. Here’s “Marvin,” the only thing of hers I can find.
Blotto’s “Heavy Metal Head” is one of several of their works.
And, of course, there’s Bowser and Blue with Working Where the Sun Don’t Shine
Gloria Balsam screeching “Here, Fluffy. Where are you! Where are you”.
A bad, bad morning DJ in town used to play this around 1990 when I was working the morning shift.
NSFW language and trigger warning
This was popular on KROQ (LA) back in the 80s.
Ballad of a Dog Named Staines performed by Red Peters on the Howard Stern Show.
To me, Weird Al is at his best when he does an original song, sometimes emulating someone else’s style perfectly without parodying their work. Dare To Be Stupid and I’ll Sue Ya are great, done spot-on in the style of Devo and Rage Against The Machine, and One More Minute is a great breakup song.
A few favorites:
Todd Snider: Beer Run
Statistician’s blues:
Jonathan Coulton: SkullCrusher Mountain
Isn’t it enough to know that I ruined a pony for you? Maybe I used too many monkeys.
This classic by Styx is something that people generally do not believe exists until they hear it, and sometimes not even then. It was a #1 request on the “Dr. Demento Show” for many years, so “obscure” may not be the right word for it.
(19 posts and nobody else mentioned it?)
All My Shootings Be Drive-Bys- MC Hawking