How do I go about adding the effect of blurred water spots to the camera lens when I pan close to my fluid sim? Can this be done in Houdini or is there some sort of post process 3rd party software used to get such effects?

I would think you'd have to first think how you want those blurred spots to look at as end result.

Then model and position them in a suitable place between your camera and sim.

Then through a relationship of your camera position tied to the either/or combination of the blurred shader/lighting. control the spots visibility,ie.

When your are panned out, you don't see the spots…when you are panned in, you see them.

water spots are just out of focus - post blur or render with correct DOF.

Gotcha, thanks guys.