I actually had to try this one for myself...
Transmission in D, brake depressed: 750-800 rpm. As soon as you release the brake pedal, rpms rise a bit to about 1000 rpms.
I have never noticed this or paid any attention to it. It's probably been like this since day one... and I have 42,000mi (70,000km) on my Rogue.
As for the engine tapping noise, it's normal. It's not actually piston slap, it valve train noise and is due to the design Nissan used. I have had this motor (QR25DE) on two Nissan's so far and the valve train noise was present in both of them.
Read this thread for an explanation:
zerothread?id=392210