Author Topic: SQL for reports  (Read 1248 times)

chuckalugk

  • Jr. Member
  • **
  • Posts: 23
    • View Profile
SQL for reports
« on: December 02, 2020, 12:48:07 PM »
Does anyone have SQL they can share for Annual Access Reviews?   SQL showing users access to what reports and groups....or who has access to a group....We'd like to create a report so everyone can see it.   I know the summarize way for admins, but we are trying to create reports that will load into CMOD and can be viewed by all.

Mehmet S Yersel

  • Jr. Member
  • **
  • Posts: 56
    • View Profile
    • LinkedIn Profile
Re: SQL for reports
« Reply #1 on: December 02, 2020, 02:16:15 PM »
Just to get you started, here is a sample query that will provide you list of  [User x Group x Folder] in a situation where access is controlled by Folder.

SELECT                     
       U.USERID AS USER         
     , G.NAME AS GROUP           
     , F.NAME AS FOLDER         
  FROM CCSYSAD.ARSUSER U         
     , owner.ARSUSRGRP UG     
     , owner.ARSFOLPERMS FP   
     , owner.ARSFOL F         
     , owner.ARSGROUP G                 
 WHERE U.UID = UG.UID           
   AND UG.GID = FP.ID           
   AND FP.FID = F.FID           
   AND UG.GID = G.GID           
WITH UR;                         

In your specific situation, you need to know how access is provided (directly to user id,  via AG and/or App or via folder only etc..) and build different queries to obtain your list.

I hope this helps you start from a step ahead. You will need to look at CMOD system tables and build the E-R Diagram for your queries. You may need multiple queries and join their results in some creative ways.

Good luck
-Mehmet
« Last Edit: December 03, 2020, 05:58:00 AM by myersel »
#zOS #Multiplatforms
#DB2 #OAM
#AFP #RiCOH AFP2PDF #SnowBound
#Finance #Telecom #Airlines
#ICN #IHS #WAS ND #Cert and Key Management
#Migrations #Data Modeling #RACF-2-CMOD Synch
#FileTek AMMO #ABI #RMDS #RADAR

chuckalugk

  • Jr. Member
  • **
  • Posts: 23
    • View Profile
Re: SQL for reports
« Reply #2 on: December 02, 2020, 03:09:45 PM »
Thank you very much.  I am not too familiar with SQL, and many customers are asking about AAR, so this is helpful.