This is my Script
Trace Pick up the Topsecret users, their resources and profiles, and the resources the Profiles use;
TSUserLoad:
Load
@1 as TS_User_Key,
@2 as TS_User,
@3 as TS_Name,
@4 as TS_Type,
@5 as TS_Opid,
@6 as TS_TSO_Prefix,
@7 as TS_Create_Date,
@8 as TS_Modify_Date,
@9 as TS_Last_Date,
@10 as TS_Last_CPU,
@11 as TS_Last_Facility,
@12 as TS_Attributes,
@13 as TS_Expiration_Pswd,
@14 as TS_Pswd_Duration,
if(trim(@14)='','YES','NO') as TS_No_Expired_Pswd,
FileTime() as Filetime_TS,
'EU' as TS_Environ
FROM "$(TopsPath)$(MVS)_tssacc1.txt" (ansi, txt, delimiter is '\t', no labels);
// Pull out the FileTime stamp and store it associated with the environment (left over from multiple TopSecret days)
FileTimeMVS:
load Distinct
TS_Environ
,max(Filetime_TS) as Filetime_TS
Resident TSUserLoad
Group By TS_Environ ;
TSUser:
Load Distinct
TS_User_Key,
TS_User as TS_User_Pin ,
trim(TS_User) as User_PIN,
left(TS_User,2) as TS_User_Prefix,
TS_Name as TS_User_Name,
TS_Type as TS_User_Type,
TS_Opid as TS_User_Opid,
TS_TSO_Prefix,
TS_Create_Date,
TS_Modify_Date,
TS_Last_Date,
if(TS_Last_Date>0,today()-TS_Last_Date) as TS_User_UnusedDays,
TS_Last_CPU,
TS_Last_Facility,
TS_Environ,
TS_Expiration_Pswd,
TS_Pswd_Duration,
TS_No_Expired_Pswd,
'Yes' as TS_User
Resident TSUserLoad
where TS_Type <> 'PROFILE'
and TS_Type <> 'DEPT'
and TS_Type <> 'ZONE'
and TS_Type <> 'GROUP'
and TS_Type <> 'DIVISION' ;
// The above list may change over time - These are NOT Topsecret PIN based "users"
// Break down the user attributes - there can be multiples concatenated together
TSUserAttributes:
left keep Load Distinct
TS_User_Key,
subfield(TS_Attributes,';') as TS_Attribute
Resident TSUserLoad
where len(trim(TS_Attributes)) > 0;
// Now take the non-users and build a table of Profiles
TSProfile:
Load Distinct
TS_User_Key as TS_Profile_Key,
TS_Environ as TS_Profile_Environ,
TS_User as TS_Profile ,
TS_Name as TS_Profile_Name,
TS_Type as TS_Profile_Type,
TS_Opid as TS_Profile_Opid,
TS_TSO_Prefix as TS_Profile_TSO,
TS_Create_Date as TS_Profile_Create,
TS_Modify_Date as TS_Profile_Modify,
TS_Attributes as TS_Profile_Attribs
Resident TSUserLoad
where TS_Type = 'PROFILE'
or TS_Type = 'DEPT'
or TS_Type = 'ZONE'
or TS_Type = 'GROUP'
or TS_Type = 'DIVISION' ;
Drop Table TSUserLoad;
// Now load the Profiles used by Users
TSUserProfileLoad:
Load
@1 as TS_User_Key,
@2 as TS_Profile_Key,
@3 as TS_Profile_ID
FROM "$(TopsPath)$(MVS)_tssacc5.txt" (ansi, txt, delimiter is '\t', no labels);
// Now load the Resources used by Users & Profiles - there can be multiple systems against a resource
TSResourcesLoad:
Load
@1 as TS_User_Profile_Key,
@2 as TS_Owner_Key,
@3 as TS_Resource,
@4 as TS_Resource_Name,
@5 as TS_Access,
@6 as TS_Systems,
subfield(@6,';') as TS_System
FROM "$(TopsPath)$(MVS)_tssacc9.txt" (ansi, txt, delimiter is '\t', no labels);
//Now match the resources to both the real Users (PINs) and the Profiles - using Left Keep
TSUserResources:
left keep (TSUser)
Load Distinct
TS_User_Profile_Key as TS_User_Key
,TS_Owner_Key as TS_User_Owner
,TS_Resource as TS_User_Resource
,TS_Resource_Name as TS_User_Resource_Name
,TS_Access as TS_User_Resource_Access
,TS_Systems as TS_User_Resource_System
,TS_System as TS_User_System
,if(right(trim(TS_System),4)='CICS',mid(TS_System,3,2),'YY') as TS_User_Stage
,if(right(trim(TS_System),4)='CICS',mid(TS_System,1,2),'XX') as TS_User_Region
Resident TSResourcesLoad;
TSProfileResources:
left keep (TSProfile)
Load Distinct
TS_User_Profile_Key as TS_Profile_Key
,TS_Owner_Key as TS_Profile_Owner
,TS_Resource as TS_Profile_Resource
,TS_Resource_Name as TS_Profile_Resource_Name
,TS_Access as TS_Profile_Resource_Access
,TS_Systems as TS_Profile_Resource_System
,TS_System as TS_Profile_System
,if(right(trim(TS_System),4)='CICS',mid(TS_System,3,2),'YY') as TS_Profile_Stage
,if(right(trim(TS_System),4)='CICS',mid(TS_System,1,2),'XX') as TS_Profile_Region
Resident TSResourcesLoad;
// Add TS Users to PIN_CONTROL
Concatenate (PIN_Control) Load Distinct
User_PIN
,left(User_PIN,8) as User_PIN_Key
,left(User_PIN,8) as User_PIN_Nbr
Resident TSUser;
