what extra security does this bring
Out of the box? None. You need to add rules to the system in order to start restricting access. The problem is that some of these may break your application.
We don't know what your magic button in cPanel does. I would recommend you have a look at the OWASP rules. If they don't make any sense to you then you need to work on your knowledge of HTTP, attack methods and mod_security before you press that button. And you should be asking the people who provided the button to tell you what it does.
You didn't say if this was a VM, container or shared host.
I look to minimise our risk of attack
Is that your question or are you only asking about mod_security? Your biggest risk is probably the applications you are running on your server. Keeping them patched and up to date will therefore give you your biggest security wins. Similar for the underlying OS and distribution.
The attack you reference in your post was possible because the admins had not installed a patch available from the software supplier.