As a surmise, the boundary between "noodling" and "solving real problems" is rather vague. One needs at least some vague external idea to guide the "noodling", otherwise they can simply normalize non-negative functions with finite integral to "discover" endless distributions. On the other hand, even Gauss, who did come up with the correct error curve (normal distribution) by reflecting on the least squares method he used to fit orbits, did not need that for the "real solving". It was more of "noodling" around with abstract error assumptions.
The way Poisson arrived at his distribution can also be fairly described as "noodling with the binomial". Despite the title of the book where it appears (Research on the Probability of Judgments in Criminal and Civil Matters Preceded by the General Rules for Calculating Probability), the part about the distribution itself does not model anything tangible or solve any real problem. It is more of a pure math exercise in asymptotics of binomial distribution along the lines sketched in problems from de Moivre's book. The relevant portion is translated in full by Stiglitz in Poisson on the poisson distribution, who comments:
"Curiously, the Poisson distribution appears but
once in all of Poisson's works, and then on but a
single page (Haight, 1967, p. 113). This reference is on p. 206 of Poisson's 1837 book, Recherches sur la Probabilité des Jugements en Matière Criminelle et en Matière Civile Précédés des Règles Générales du Calcul des Probabilités... The distribution appears in Chapter 3 as limit to the binomial. Actually, as will be seen below, Poisson derived the distribution directly as an approximation to the negative binomial cumulative distribution. There is no indication that he sensed the wide applicability of the distribution; rather, it was one of several approximations and received no special comment.
[...] Poisson's derivation of his distribution, was foreshadowed by an analysis De Moivre had presented over a century earlier in a series of problems in his book, The Doctrine of Chances... It must be admitted that Poisson added little to De Moivre's mathematical approximation, with which he was quite familiar, although one would have to stretch the point to claim the discrete distribution $e^{-\omega}\frac{\omega^n}{n!}$ is found in De Moivre."
Apparently, Newcomb was the first to apply the distribution to fit the data in 1860, namely, to find the "probability that, if the stars were scattered at random over the heavens, any small space selected at random would contains stars." The current ubiquity of the distribution is mostly unrelated to Poisson, it was rediscovered in biology and physics a century later and its applicability stems from that. Further history is discussed by Hanley and Bhatnagar:
"Poisson himself never gave an example of, or applied the distribution now called after him. It ended up being independently rediscovered and used by early-20th century biological and physical scientists who derived it using space and time considerations. Some of them also linked it with what we refer to today as the exponential distribution. Quite early on, it was also recognized that counts involving human activities/behavior were not always well fitted by the Poisson distribution, and that extensions were needed."
More broadly, Stahl in The Evolution of the Normal Distribution describes how Simpson and Laplace came up with three different distributions by "noodling" theoretically to find the error curve: triangular, double exponential and one with logarithmic density.
Although I am not aware of a book on history of distribution discoveries specifically, books on history of probability and statistics cover that in particular, see, for example, highly acclaimed Stigler's History of Statistics: The Measurement of Uncertainty before 1900. Hald also has a series of history books on different time periods. The most relevant one is probably History of mathematical statistics from 1750 to 1930.