You're conflating two different things here. Requesting an upgrade at check in and the 48 hour auto upgrade program are two different things.

Auto upgrades are still very much a thing. As the poster prior to me mentioned, they're only good for one category upgrades and as always, it's based on the hotel's definition of what that "upgrade" is. Also, if the property is forecasted to have a higher-ish occupancy, it's not going to happen for anybody. I still get people everyday that assume the property is behind this and it's 100% all from HH. Fwiw, I mostly see auto upgrades happening on cash rates. I don't want to say that it never happens on redemption stays, but, IME, it's comparatively rare.

In regards to what those desk agents told you, no, calling the Diamond Desk isn't going to move the needle on check in day. The 48 hour window has passed and the best they're going to do is call the property and ask the same agent if they'll give you an upgrade. Of course, if said brand doesn't require upgrades to be given regardless of status then as always, good luck as at the end of the day, it'll be agent to agent as to what happens.

On that last point, as a tiny data point, personally, I only upgrade if you ask but if you attempt to beat me over the head with your Diamond status (plenty do), it's not happening ever, TFB. Most of my colleagues will only upgrade if it benefits the hotel in terms of our inventory. Past that, nope. Why? The mindset is very much, don't have to and don't need to.