i have now written this sql..

with cte1 as (
SELECT  
 cmo.[CMID] as object_id
,cmo.[PCMID] as parent_object_id
,cmo.[VERSION] as object_version
,cmo.[CREATED] as created_datetime
,cmo.[MODIFIED] as modified_datetime
,cmo.[DISABLED] as disabled
,cmo.[CLASSID] as class_id
,cmc.name as class_description
,cmo.[DISPSEQ] as display_sequence

-- report name --
,CMOBJNAMES.NAME

-- self join to get parent_class_id
, cmo2.CLASSID as parent_class_id

-- parent_class_desription
, cmc2.NAME as parent_class_description

,cmobjnames2.name as parent_object_name

, cmref2.REFCMID as owner_id

, props33.name as owner_name
, props33.userid as owner_user_id
, props33.LASTLOGIN as owner_last_login
, props33.license as owner_license_code


FROM CMOBJECTS cmo

-- get classid description
left join CMCLASSES cmc on
cmo.CLASSID=cmc.CLASSID

-- get objectname
left join CMOBJNAMES on
cmo.cmid=CMOBJNAMES.cmid
and CMOBJNAMES.isdefault=1


left join [CMOBJECTS] cmo2 on
cmo.PCMID=cmo2.CMID

left join CMCLASSES cmc2 on
cmo2.CLASSID=cmc2.CLASSID

--get parent object name
left join CMOBJNAMES cmobjnames2 on
cmo.pcmid=cmobjnames2.cmid
--and cmobjnames2.LOCALEID=92
and cmobjnames2.isdefault=1


-- get ownerid of report
left join CMREFNOORD2 cmref2 on
cmo.CMID=cmref2.CMID

-- gte owner attributes

left join CMOBJPROPS33 props33 on
cmref2.REFCMID=props33.cmid


WHERE 1=1
--and (cmo.disabled=0
--or cmo.disabled is null
--)
and cmc.name = 'report'

)

select * from cte1

which returns this output.. (transposed into record format for easier viewing here)

I'm looking to add in when the reports werre accessed / run etc next to see if we can filter out any not used for a while. Does anyone know what tables i could use for this?

Thanks,

Rob.