Here is some MS Sql code that I run against the CM database to get a list of all CM objects along with package names, paths and owners. Owners is tricky and for my CM it mostly returns Unknown unless the report was created recently. I attribute this to the fact that we have a very mature system. Most reports were written by people who are no longer with the company also we have gone through many different domain management systems (LDAP, AD, etc). My clients tell me that from within Cognos they see the owner as Linda Smith, but my report shows Unknown. I am guessing that when the report is written the owner is inserted into table cmobjprops33. But once there is a change of ownership or maybe change of domain system that it is then stored somewhere else. If you find out please post here.
CREATE TABLE [dbo].[cm_Cognos_Inventory](
[CMID] [int] NOT NULL,
[PCMID] [int] NOT NULL,
[CLASS] [varchar](50) NULL,
[NAME] [varchar](256) NOT NULL,
[PATH] [varchar](1000) NULL,
[DESCRIPTION] [varchar](4000) NULL,
[PACKAGE] [varchar](255) NULL,
[VERSION] [int] NOT NULL,
[CREATED] [datetime] NOT NULL,
[MODIFIED] [datetime] NOT NULL,
[OWNER] [varchar](100) NULL,
[CLASSID] [int] NULL,
[ISHIDDEN] [int] NULL,
[DISABLED] [int] NULL,
[INVENTORYNote] [varchar](512) NULL,
[INVENTORYSetCount] [int] NULL,
[INVENTORYDateTime] [datetime] NOT NULL,
[INVENTORYServerName] [varchar](128) NULL,
[INVENTORYDataBaseName] [varchar](128) NULL,
PRIMARY KEY CLUSTERED
(
[CMID] ASC,
[INVENTORYDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[cm_Create_Inventory]
/******************************************************************
*** RUN in context of CM database
cm_Create_Inventory - inserts into table cm_Cognos_Inventory a list of
the following objects
0 -- root folder
1 -- folders
5 -- account
6 -- job definition
10 -- reports
18 -- packages
26 -- role
33 -- content
37 -- query
38 -- model
54 -- group
85 -- agent definition
178 -- documents
209 -- dashboards
Example
=======
-- run the sproc to create the inventory and pass a note
declare @datetime datetime
exec cm_Create_Inventory '2018_02 some change for 2019_01', @datetime = @datetime output
print convert(varchar, @datetime, 121)
-- view the results of the last inventory
select * from cm_Cognos_Inventory
where inventorysetcount = (select max(inventorysetcount) from cm_Cognos_Inventory)
order by class
-- view single CONTENT record from each of the different inventories
select class, name, created, modified, inventorynote, inventorydatetime, inventorysetcount from cm_Cognos_Inventory where class = 'content' order by inventorydatetime
-- Show non-matches for the two selected inventories. Exclude My FOlders
select i1.class, i1.name, i1.path, i2.CLASS, i2.NAME, i2.path, i1.inventorynote, i2.inventorynote, i1.inventorysetcount, i2.inventorysetcount
from (select * from cm_Cognos_Inventory where inventorysetcount = 1) i1 -- CHANGE THIS - InventorySetCount
full outer join
(select * from cm_Cognos_Inventory where inventorysetcount = 2) i2 -- CHANGE THIS - InventorySetCount
on i2.CLASS = i1.class and i2.NAME = i1.name and i1.path = i2.path
where (i1.PATH not like 'my folders%' or i2.PATH not like 'my folders%')
and (i1.name is null or i2.name is null)
CHANGES1
=======
2019_01 - bxg - general release
*******************************************************************/
@Note varchar(256) = '',
@InsertIntoTable int = 1,
@ShowResultSet int = 1,
@DateTime datetime = NULL output
AS
BEGIN
IF OBJECT_ID('CMOBJECTS') IS NULL
BEGIN
PRINT 'Create Stored Procedure ''cm_Create_Inventory'' in Cognos CM Database and then run from context of CM Database.'
RETURN (1)
END
SET NOCOUNT ON;
DECLARE @Servername varchar(128) = (select @@SERVERNAME)
DECLARE @Databasename varchar(128) = (select db_name())
DECLARE @SetCount int
DECLARE @SQL nvarchar(max)
SET @DateTime = getdate()
-- insert basic data
INSERT INTO cm_Cognos_Inventory_Temp([CMID],
[NAME],
[PCMID],
[VERSION],
[CREATED],
[MODIFIED],
[CLASS],
[PATH],
[CLASSID],
[ISHIDDEN],
[DISABLED],
[INVENTORYDATETIME],
[INVENTORYSERVERNAME],
[INVENTORYDATABASENAME],
[INVENTORYNOTE],
[INVENTORYSetCount])
SELECT [N].[CMID],
[N].[NAME],
[O].[PCMID],
[O].[VERSION],
[O].[CREATED],
[O].[MODIFIED],
UPPER([C].[NAME]),
NULL AS [PATH],
[O].[CLASSID],
[o5].[HIDDEN],
[o].[DISABLED],
@DATETIME,
@SERVERNAME,
@DATABASENAME,
@Note,
@SetCount
FROM
CMOBJNAMES AS N JOIN CMOBJECTS AS O
ON N.CMID = O.CMID
/* can limit to objects that you want
AND O.CLASSID IN (
0 -- root folder
,1 -- folders
--,5 -- account
--,6 -- job definition
,10 -- reports
,18 -- packages
--,26 -- role
,33 -- content
,37 -- query
,38 -- model
--,54 -- group
,85 -- agent definition
,178 -- documents
,209 -- dashboards
) */
JOIN CMCLASSES AS C
ON O.CLASSID = C.CLASSID
AND N.ISDEFAULT = 1
LEFT JOIN CMOBJPROPS55 AS o5
ON o.cmid = o5.cmid
ORDER BY N.CMID;
-- insert descriptions
UPDATE cm_Cognos_Inventory_Temp
SET [DESCRIPTION] = [D].[VALUE]
FROM [CMOBJPROPS3] [D] JOIN [cm_Cognos_Inventory_Temp] [R]
ON [D].[CMID] = [R].[CMID]
AND [D].[ISDEFAULT] = 1
WHERE INVENTORYDATETIME = @DateTime;
--- insert package names
UPDATE cm_Cognos_Inventory_Temp
SET [PACKAGE] = [C].[NAME]
FROM [CMREFNOORD1] [A] JOIN [CMOBJECTS] [B]
ON [A].[REFCMID] = [B].[CMID]
JOIN [CMOBJNAMES] [C]
ON [B].[CMID] = [C].[CMID]
AND [C].[ISDEFAULT] = 1
JOIN [cm_Cognos_Inventory_Temp] [R]
ON [R].[CMID] = [A].[CMID]
WHERE [A].[PROPID] = 31 and INVENTORYDATETIME = @DateTime;
--- update missing package names set to null
UPDATE cm_Cognos_Inventory_Temp
SET [PACKAGE] = NULL
WHERE ISNUMERIC([PACKAGE]) = 1 and INVENTORYDATETIME = @DateTime;
--- insert path names
UPDATE cm_Cognos_Inventory_Temp
SET [PATH] = [X].[PATH]
FROM(SELECT [dbo].[cm_GETPARENTPATH]([R].[CMID], @DateTime )AS [PATH],
[R].[CMID]
FROM [cm_Cognos_Inventory_Temp] AS [R])[X]
WHERE [cm_Cognos_Inventory_Temp].[CMID] = [X].[CMID] and INVENTORYDATETIME = @DateTime ;
-- insert owner if available - usually is not available
UPDATE cr
SET [owner] = [a].[name]
-- select a.name, b.cmid
FROM [cmobjprops33] [a], [cmobjnames] [b], [cmrefnoord2] [c], [cmobjects] [d], [cmclasses] [e], [cm_Cognos_Inventory_Temp] [cr]
WHERE [b].[cmid] = [c].[cmid]
AND [a].[cmid] = [c].[refcmid]
AND [b].[cmid] = [d].[cmid]
AND [d].[classid] = [e].[classid]
-- and (e.name = 'report' or e.NAME = 'query')
AND [cr].[CMID] = [b].[cmid]
AND INVENTORYDATETIME = @DateTime ;
IF @InsertIntoTable = 1
BEGIN
SET @SetCount = ISNULL((Select MAX(InventorySetCount) from cm_Cognos_Inventory), 0) + 1
INSERT INTO cm_Cognos_Inventory([CMID],
[NAME],
[PCMID],
[VERSION],
[CREATED],
[MODIFIED],
[OWNER],
[CLASS],
[PATH],
[CLASSID],
[ISHIDDEN],
[DISABLED],
[INVENTORYDATETIME],
[INVENTORYSERVERNAME],
[INVENTORYDATABASENAME],
[INVENTORYNOTE],
[INVENTORYSetCount])
SELECT [CMID],
[NAME],
[PCMID],
[VERSION],
[CREATED],
[MODIFIED],
[OWNER],
[CLASS],
[PATH],
[CLASSID],
[ISHIDDEN],
[DISABLED],
[INVENTORYDATETIME],
[INVENTORYSERVERNAME],
[INVENTORYDATABASENAME],
[INVENTORYNOTE],
@SetCount
FROM
cm_Cognos_Inventory_Temp
WHERE INVENTORYDATETIME = @DateTime
END
IF @ShowResultSet = 1
IF @InsertIntoTable = 1
SELECT [CMID],[NAME], [PCMID], [VERSION], [CREATED], [MODIFIED], [OWNER], [CLASS], [PATH], [CLASSID], [ISHIDDEN], [DISABLED],
[INVENTORYDATETIME], [INVENTORYSERVERNAME], [INVENTORYDATABASENAME], [INVENTORYNOTE], [INVENTORYSetCount]
FROM cm_Cognos_Inventory
WHERE INVENTORYDATETIME = @DateTime
ELSE
SELECT [CMID],[NAME], [PCMID], [VERSION], [CREATED], [MODIFIED], [OWNER], [CLASS], [PATH], [CLASSID], [ISHIDDEN], [DISABLED],
[INVENTORYDATETIME], [INVENTORYSERVERNAME], [INVENTORYDATABASENAME], [INVENTORYNOTE]
FROM cm_Cognos_Inventory_Temp
WHERE INVENTORYDATETIME = @DateTime
-- clean up records older than 1 day
DELETE cm_Cognos_Inventory_Temp where INVENTORYDATETIME <= DATEADD(day, -1, GETDATE())
END;
GO
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------
Original Message:
Sent: Fri June 12, 2020 03:06 PM
From: andy ellis
Subject: Content Store tables
Hi all
I need to find out which Content Store table contains the folder path for the report and owner. Anyone found it?
thanks
------------------------------
andy
------------------------------
#CognosAnalyticswithWatson