Hundreds of fountains across the region were checked for cleanliness, including whether there was mould too close to the spout, or if there was rubbish in the fountain.
The survey also assessed drinkability, using criteria such as the flow of water and whether it spurted out high enough for easy and hygienic drinking.
A councillor says the results are troubling, and reflect a wider problem of basic services not being provided.
Almost four-in-10 drinking fountains in Auckland have been found to be dirty, with mould and rubbish among problems.
Other issues included water not spurting high enough to allow easy drinking and discolouration from limescale.
The Weekend Herald can reveal results from a comprehensive survey of hundreds of the city's public drinking fountains, done as part of a project to get people drinking more water.
Health officials are increasingly worried about cheap, sugary drinks rotting teeth and fuelling obesity and chronic disease. However, the survey showed drinking fountains were often unusable.
Of the 282 fountains checked, 38 per cent were dirty