You assume that your query should return results. This query will not return results if you do not have users enrolled in the context of a course for which the log entry was created.
Query could also be a bit optimized.
AND FROM_UNIXTIME(lo.time) > '201x-xx-xx' is not good way to do it since you force mysql to convert EVERY value of lo.time to date and than implicitly compare it to another date.
This is much better - lo.time > UNIX_TIMESTAMP('YYYY-MM-DD') - with this mysql will create timestamp value just once and use that to directly compare it to lo.time which is much faster.
To confirm or deny if you actually have a users with some role in courses of your choice replace INNER JOINS to LEFT JOINS for the role part. And finally in Moodle 2 roles can now have name overrides on per context level which should be checked too.
Modified query would look like this:
SELECT
lo.course AS Course_ID,
co.shortname AS Course_ShortName,
co.fullname AS Course_FullName,
CASE WHEN rn.name IS NULL THEN r.name
ELSE rn.name END AS RoleName,
COUNT(r.name) AS Forum_Posts
FROM mdl_log AS lo
INNER JOIN mdl_course AS co ON (co.id = lo.course)
INNER JOIN mdl_user AS us ON (lo.userid = us.id)
INNER JOIN mdl_context AS ctxt ON (ctxt.instanceid = co.id) AND (ctxt.contextlevel = 50)
LEFT JOIN mdl_role_assignments AS ra ON (ra.userid = us.id) AND (ctxt.id = ra.contextid)
LEFT JOIN mdl_role AS r ON (r.id = ra.roleid)
LEFT JOIN mdl_role_names AS rn ON (rn.roleid = r.id) AND (rn.contextid = ctxt.id)
WHERE
lo.action LIKE '%add%'
AND lo.time > UNIX_TIMESTAMP('2013-09-01')
AND lo.time < UNIX_TIMESTAMP('2013-09-30')
AND co.id > 1
AND lo.module = 'forum'
GROUP BY Course_ID , r.name
ORDER BY co.fullname
Also in the code query table prefixes should never be hardcoded. Instead use {table} format which moodle replaces to a correct table name.
But with all this said what puzzles me is why would you do it this way? Is it not easier to just look at forum_posts table and get all that information from there?