If I'm not mistaken, Vibrant Technologies has a version of MeScope that allows the user to input variables (e.g. mass of water) and make assessments of what you are looking for. You might want to contact them directly.
For the modal test, are you impacting above ground and obtaining response data from the support column below ground? I've not done that but would be very curious how well the data comes out.
So when the ODS is performed (now there is the added mass of the water in the wetted piping) you are hoping you might see a change in Fn. You're right in that you may or may not even excite or see the mode(s) depending on location of Fn to running speed. You could try to "jog" the pump to provide an impulse to the whole structure in the hopes it might provide enough energy to see the modes.
If you do find solution to this testing, please share