SQL Abfragen auf Cognos ContentStore
Abfrage nach Berichtseigentümer:
SELECT
o.cmid AS Berichtsid,
Names.NAME AS Berichtsname,
n2.REFCMID AS EigentümerID,
Pr33.NAME AS Eigentümername,
Pr33.USERID AS EigentümerKennung
FROM
[Cognos_CS_CMOBJECTS] o
LEFT JOIN [Cognos_CS_CMREFNOORD2] n2 on o.cmid= n2.CMID
LEFT JOIN [Cognos_CS_CMOBJPROPS33] Pr33 on n2.REFCMID=Pr33.CMID
LEFT JOIN [Cognos_CS_CMOBJNAMES] Names on o.CMID = Names.CMID
where o.CLASSID = 10
User in Lizenzgruppen:
Select
[CMOBJECTS].CMID AS CMID_Inhaltsordner,
[CMOBJNAMES].NAME AS Name_Inhaltsordner,
[CMREFORD1].REFCMID AS UserInhaltsordner,
[CMOBJPROPS33].USERID,
[CMOBJPROPS33].Lastlogin
FROM Cognos_CS_CMOBJECTS [CMOBJECTS]
LEFT JOIN [dbo].[Cognos_CS_CMREFORD1] [CMREFORD1]ON [CMOBJECTS].CMID = [CMREFORD1].CMID
LEFT JOIN [dbo].[Cognos_CS_CMOBJNAMES] [CMOBJNAMES] ON [CMOBJECTS].CMID=[CMOBJNAMES].CMID
LEFT JOIN [dbo].[Cognos_CS_CMOBJPROPS33] [CMOBJPROPS33] ON [CMREFORD1].REFCMID=[CMOBJPROPS33].CMID
where [CMOBJPROPS33].USERID is not null and [CMOBJECTS].CLASSID =54