As a minor point of no real consequence the Cessna T-41 Mescalero has been in service in the US airforce and army from 1964 until today, and I've been told the Diamond DA20 Katana is in indirect service via a through a civilian contract that screens prospective pilots.
Weird nitpick, I know.
There's also a slew of drones that may or may not use efficient small piston engines or rotary varients which may or may not count as piston.
On the data aquisition side I'm willing to bet there's still a place in the US military for low, slow, ground hugging piston engine craft that run radiometrics or EM mapping.
There’s also stuff like the super tucano where it’s got a sorta warbird vibe but with a turbo prop (not technically a piston engine plane but performs a similar role to older types that did have piston engines)
The Battle of Beersheba ties two worlds together: on the one hand both sides were using aerial reconnaissance (akin to this 2024 thread), on the other the australian 12th channeled their inner Alexander and galloped through a gap directly to their victory condition (akin to Gaugamela 331 BC).
I'm getting strong PAC 750XL family vibes from that, we had a modified version hardened to fly 80 m drapes across all of Mali some years back .. locals were taking random potshots as it passed over: https://www.youtube.com/watch?v=1_MO5Wfomks
Our civilian aerospace industry had a scandal a decade or two back because apparently various African countries were buying our trainers, discovering that as shipped the trim was a little off, and rebalancing by mounting MGs in the too-light-because-it-was-empty space.
These are the "good stuff" .. for various data gathering activities you do not want a fast high flying jet, you want a slow close to ground platform travelling at sub 70 m/s speeds to maximise "dwell time" over each ground point (while not travelling so slow as to fall out of the bloody sky).
Crop dusters carry weight, excel at STOL (Short Takeoff and Landing) on "Oh shit, that's not a runway".
US Special Operations Command is into the sneaky stuff - intelligence gathering, quick in | out person on ground infil and exfiltrates.
Stubby little planes that pull like tractors and can depart flying upside down underneath a bridge are ideal, they get overshot by fighter jets and have engines too cold for air to air missiles (fingers crossed).
I'm guessing that these days there are two kinds of airborne objects: multi-modal drones and targets.
Oddly enough, heat seeking air to air came in ca.1950s, so self-flying has been around a while, and now people (especially those with heavy logistics requirements) are talking about land based self-driving, but shouldn't the seas be an intermediate problem? Where did all the self-sailing vessels go?
Weird nitpick, I know.
There's also a slew of drones that may or may not use efficient small piston engines or rotary varients which may or may not count as piston.
On the data aquisition side I'm willing to bet there's still a place in the US military for low, slow, ground hugging piston engine craft that run radiometrics or EM mapping.