Try this out:

select

agentmoduleconfigurationproducthostname as 'Master Server',
case
when agentmoduleeventeventnumber='1' then 'Job'
when agentmoduleeventeventnumber='3' then 'Policy'
when agentmoduleeventeventnumber='7' then 'Tape Drive usage'
when agentmoduleeventeventnumber='8' then 'Media'
when agentmoduleeventeventnumber='13' then 'Image'
end as 'Event',
MAX(agentmoduleeventlasttime) as 'Last Event Success Time'

from

agentmoduleevent,
agentmodule,
agentproduct,
agentmoduleconfiguration

where

agentmoduleid=agentmoduleconfigurationagentmoduleid and
agentmoduleconfigurationid=agentmoduleeventagentmoduleconfigurationid and
agentmoduleid=agentproductagentmoduleid and
(agentmoduleeventeventnumber='1' or
agentmoduleeventeventnumber='3' or
agentmoduleeventeventnumber='7' or
agentmoduleeventeventnumber='8' or
agentmoduleeventeventnumber='13')

group by

agentmoduleconfigurationproducthostname,
agentmoduleeventeventnumber

order by

agentmoduleconfigurationproducthostname