Query Cognos 8/10 Content Store

I came across these queries that can give you good insight on the data and maintenance of Content Store in Cognos 8/10. You can also leverage the CMTools utility that comes with cognos install.

..\Cognos\bin\utilities\cm\CMTools\CMTools.exe


select count(*) as "total number of objects" from CMOBJECTS;

select c.NAME as "class name", count(*) as "number of objects"
from CMOBJECTS o, CMCLASSES cgroup by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "number of secured objects"
from CMOBJECTS o, CMCLASSES c, CMPOLICIES pwhere o.CMID=p.CMID and p.POLICIES is not null
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select count(*) as "Total number of outputs",
SUM(cast(coalesce(DATALENGTH(DATAPROP),0) as bigint)) as "Total size of outputs" from CMDATA
where DATAPROP is not NULL;

select count(*) as "Outputs under 10KB" from CMDATA
where DATAPROP is not NULL and DATALENGTH(DATAPROP) < 10240;

select count(*) as "Outputs 10KB - 100KB" from CMDATA
where DATALENGTH(DATAPROP) >= 10240 and DATALENGTH(DATAPROP) < 102400;

select count(*) as "Outputs 100KB - 1MB" from CMDATA
where DATALENGTH(DATAPROP) >= 102400 and DATALENGTH(DATAPROP) < 1048576;

select count(*) as "Outputs 1MB - 10MB" from CMDATA
where DATALENGTH(DATAPROP) >= 1048576 and DATALENGTH(DATAPROP) < 10485760;

select count(*) as "Outputs 10MB-100MB" from CMDATA
where DATALENGTH(DATAPROP) >= 10485760 and DATALENGTH(DATAPROP) < 104857600;

select count(*) as "Outputs 100MB-1GB" from CMDATA
where DATALENGTH(DATAPROP) >= 104857600 and DATALENGTH(DATAPROP) < 1073741824;

select count(*) as "Outputs > 1GB" from CMDATA
where DATALENGTH(DATAPROP) >= 1073741824;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS7 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select count(*) as "Specifications < 10KB" from CMOBJPROPS7
where SPEC is not NULL and DATALENGTH(SPEC) < 10240;

select count(*) as "Specifications 10KB - 100KB" from CMOBJPROPS7
where DATALENGTH(SPEC) >= 10240 and DATALENGTH(SPEC) < 102400;

select count(*) as "Specifications 100KB - 1MB" from CMOBJPROPS7
where DATALENGTH(SPEC) >= 102400 and DATALENGTH(SPEC) < 1048576;

select count(*) as "Specifications > 1MB" from CMOBJPROPS7where DATALENGTH(SPEC) >= 1048576;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS43 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS43 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

--select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"--from CMOBJECTS o, CMCLASSES c, CMOBJPROPS66 p
--where o.CMID=p.CMID and p.SPEC is not NULL
--group by o.CLASSID, c.CLASSID, C.NAME
--having c.CLASSID=o.CLASSID
--order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.RELATED),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS14 p
where o.CMID=p.CMID and p.RELATED is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS63 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS42 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

--select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"--from CMOBJECTS o, CMCLASSES c, CMOBJPROPS68 p
--where o.CMID=p.CMID and p.SPEC is not NULL
--group by o.CLASSID, c.CLASSID, C.NAME
--having c.CLASSID=o.CLASSID
--order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"from CMOBJECTS o, CMCLASSES c, CMOBJPROPS56 p
where o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

--select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications"--from CMOBJECTS o, CMCLASSES c, CMOBJPROPS57 p
--where o.CMID=p.CMID and p.SPEC is not NULL
--group by o.CLASSID, c.CLASSID, C.NAME
--having c.CLASSID=o.CLASSID
--order by c.NAME;

select count(*) as "Total number of models" from CMOBJPROPS7
where CMODEL is not NULL;

select count(*) as "Models < 10KB" from CMOBJPROPS7
where DATALENGTH(CMODEL) < 10240;

select count(*) as "Models 10KB - 100KB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 10240 and DATALENGTH(CMODEL) < 102400;

select count(*) as "Models 100KB - 1MB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 102400 and DATALENGTH(CMODEL) < 1048576;

select count(*) as "Models > 1MB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 1048576;

create table #CMPROFILE (CMID int, CLASSID int, NOBJECTS bigint)

insert into #CMPROFILE (CMID, CLASSID, NOBJECTS)SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 1 or p.CLASSID=17 or p.CLASSID=12 or p.CLASSID=9
group by o.PCMID, p.CLASSID

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from #CMPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME, pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

select TYPE as "schedule type", count(*) "number of schedules"
from CMOBJPROPS2 where ACTIVE=1 and TYPE is not NULLgroup by TYPE;

insert into #CMPROFILE (CMID, CLASSID, NOBJECTS)SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 55 and o.CLASSID = 57 or o.CLASSID=17
group by o.PCMID, p.CLASSID

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from #CMPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME, pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

select count(*) as "Number of empty policies" from CMPOLICIES
where DATALENGTH(POLICIES 

Comments

  1. Thank you for the huge repository...
    -- Sandeep P

    ReplyDelete
  2. Where do we need to fire all these queries??

    ReplyDelete
  3. Depends on the database you have the content store/audit tables on. For SQL server you can run them in Management studio, for Oracle in Toad/SQL Plus.....

    ReplyDelete
  4. Hi Santhosh,
    My requirement is to get the below details
    All users reports under Myfolder,along with Report Creation and Last Accessed date

    ReplyDelete
  5. My Folder data is not easy to get from audit tables, they are always associated with the CAM ID. How many users My folders do you want to query? the easiest way i can think of is use a SDK script to extract each users my Folders reports to a common place in public folder and then create a report based on it, this is for the list of reports in each users My Folder. If you want the last access date it can be queried as is from your Audit table if auditing is enabled, just create a report with User ID,Report Name,Run time Stamp(Aggregation set to maximum so it will always give the latest run timestamp), Search path. Group by User ID and Report name and u can filter on serch path specific to my folders(may have to use substr kind of func). Hope this helps. I will take a look today to see if there is an easy way.

    ReplyDelete
  6. Hi Nimmy,
    I just checked something and you can query the reports of any users My Folders from audit package,but the catch here is the run log has to be on the table atleast once and you will have get the folder name from report search patch(see my other post on how to do this)...I hope you got the message.

    ReplyDelete
  7. Hi Santhosh,
    Thanks for the info..
    Thats the issue..I managed to get the report data from My Folders for the users.But it will fetch only the details of the reports which got executed in the past:(
    Thats why I thought from content store if we can get this info using some query...
    Im not familiar with SDK as well:(
    If you can provide some insight tht wud be a great help

    Thanks in advance

    ReplyDelete
  8. Hi Santhosh

    Thanks for your work.
    I am looking for an SQL query from Content Store tables to show the reports which has Native SQL Query Subjects (Data Source which has tables and columns)defined in FM not in Report Studio.

    Appreciate your response,

    ReplyDelete
  9. From my understanding the information you are looking for is always in the model xml, since the objects are defined in FM as native. I doubt if this will be marked and saved somewhere in content store apart from model xml or package xml. I will browse the content store and see if anything is available.

    Regards,
    Santosh

    ReplyDelete
  10. A quick look at the audit tables and I see some native SQl being saved from report runs, but i think you need to have higher levels of logging set to record this data that will affect performance on your environment.
    Take a look, I will keep looking and see if I can find anything straight forward.

    Select * from [Cognos_audit_DB].COGIPF_NATIVEQUERY

    ReplyDelete
  11. Santhosh

    Appreciate your early reply.

    Interestingly our Cognos env. has no Audit DB created and I don't want the Admin team to do this (It's a long process).

    As per your suggestion I have gone through model.xml file and noticed SQL statements for the reports, and need to know how to pull model.xml file from ContentStore.

    Thanks in advance

    ReplyDelete
  12. Here is the table which has the XML of model and reports. But they are BLOB and CLOB fields, you might have to convert them to XML output and search.

    SELECT *
    FROM cmobjprops7

    Hope this helps.

    ReplyDelete
  13. how to check which object is occupying what size in the content store database. Also, how can we check the size that the saved outputs are occupying.

    ReplyDelete
    Replies
    1. Sorry for the late response...have been busy with personal life.
      CMDATA is the table that has all the outputs saved in Cognos repository.

      select count(*) as "Outputs under 10KB" from CMDATA
      where DATAPROP is not NULL and DATALENGTH(DATAPROP) < 10240;

      Delete
  14. Hi Santhosh, where can i find the DATABASE CONNECTIONS for the Each package in cognos in the content store database using the SQL.

    Please let me know.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Sorry for the delay in replying back.
      You can only see it in model.xml, when you open in text editor. Once you publish i'm not sure where that XML is saved in the repository tables as a package and I'm not sure if it will be encrypted, I will have to research. Please post if you find it...

      Delete
  15. Hey Santosh,
    Do you know if it's possible to find out using the CCS what users have which TAB? In our organisation we have many tabs and I want to see what users have which tab...

    Great blogpost by the way.. :)
    Alex

    ReplyDelete
    Replies
    1. @Alex, I'm not sure what table has this data in content store. Did you check with cognos support on this? let me browse through the CS tables and see if I find anything.

      -Santosh

      Delete
  16. Hi,
    This is an excellent post. Thanks for sharing this.
    Is there a way to find out the trending reports?
    Reports most run/downloaded? and by which user or group?

    Thanks in advance,
    Chitresh

    ReplyDelete
    Replies
    1. Hi Chitresh,
      This is easy to do in Cognos, all you need to do is leverage data in Audit tables.
      You can start with the report I have in my other pst:
      http://mybyeexperience.blogspot.com/2011/02/cognos-audit-report-that-shows-reports.html
      On the final query of this report you can put count on number of executions for a report name and pick the top 5 reports and users.
      Hope i'm clear enough.

      Thanks,
      Santosh.

      Delete
  17. HI santosh,

    Ur Posts are very Helpfull.

    How we can find list of reports in 'PUBLIC FOLDER' by quering content store which in DB2
    plz suggest.

    Thnks
    Nilk

    ReplyDelete
    Replies
    1. You can use the Audit package that comes with Cognos Install, for this you need to have the Auditing enabled in Cognos Configuration.

      Delete
    2. Hi Santosh,
      Auditing is enabled in our installation.
      We need the list of report names, its path, which package they are based on (these 3 cols) for all the reports lying in a particular folder for eg: Public folder --> NewsIndex -->Sample_folder.
      Can this be possible with Audit database or do we need to access content store database?

      Delete
    3. Hi Santosh ,
      Auditing is enabled in our project. Can you let me know how can we get the list of reports using audit database. Till now I thought auditing will have only the data about previously ran reports.

      Delete
    4. Sorry for the late response.
      If Auditing is enabled make sure that tables in audit schema are being populated. Publish the Cognos Audit package and you will be able to build the reports you are looking for.
      http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cra.8.4.0.doc/ug_cra_id3354SampleAuditReports.html

      Delete
  18. Hi

    Now my question is I have done Folder structuring in Public Folder.
    For example

    Public Folder->Folder A->Folder B->Folder C
    Again Folder B->Folder B1

    I have Created dffrent roles and roles are asign to folder from set properties of folder
    and those roles are visible in Permissions tab of set properties.

    So how to get which are the Roles are assign at folder level from Content Store by Query table.

    PLz suggest.

    ReplyDelete
    Replies
    1. I am looking for similar solution. can you please share your experience how did you resolve the issue.

      Delete
  19. Hi Santosh,

    Do you know which table in content store database stores prompt selections of a report. we wanted to see what parameters users are selecting when they are running a particular report.
    Thanks in advance

    Thanks,
    Praveen

    ReplyDelete
    Replies
    1. User selections are from LOV's available from databases, they are not logged or stored in Cognos Content Store or Audit tables.

      Delete
  20. Impressive post. Thanks, very helpful.
    Do you have queries about the job step details as well?

    Thanks,
    Dennis

    ReplyDelete
  21. Helpful post, thanks!
    Any chance there are queries about the job step details?

    Thanks,
    Dennis

    ReplyDelete

Post a Comment

Popular posts from this blog

Cognos 8/10 CAMID-User ID/LDAP ID report

How about an Online help forum?