I need to insert comma's into both the header and data for the following CARLa code used to generate on demand, expanded access list reports so that the report can be emailed to the end user as a .csv file which they can open in microsoft excel.

1st iteration carla:

sortlist class key acl(sort(user),resolve) acl:name acl:instadata(hor,0)

This iteration returns a column based report with headers and the data required. It is suitable for .txt file format.

2nd iteration carla:

sortlist class | "," |,

key(",Covering Profile") | "," |,

acl(sort(user),resolve) | "," |,

'"' | acl:name(",Name",22) | '"' | "," |,

'"' | acl:instdata(",Installation Data",hor,0) | '"'

This iteration 1)adds comma's between the data, 2)adds comma's before each column header, and 3) wraps free form text fields inside quotes so that potential comma's (like inside a name) are treated appropriately in a .csv file and do not offset cells inside excel.

The notable exception with the 2nd iteration is that 'acl(sort(user),resolve)' actually produces 4 space delimited columns. This does not transfer into a csv file very well and I need to replicate these 4 columns with comma separators instead of spaces.

I have tried substituting 'acl(sort(user),resolve)' with variations on

acl(sort,resolve,",ID"),

acl(sort,resolve,aclaccess,",Access"),

acl(sort,resolve,aclid,",ACL id"),

which still doesn't quite replicate the results of acl(sort(user),resolve). The conditional access under column header 'When' is missing (I haven't found a substitute in the CARLa manual yet) and the available data output isn't quite right anyway.

How can I either 1) replace the column delimiter spaces in acl(sort(user),resolve) with comma's or 2) build substitute CARLa code which would replace acl(sort(user),resolve) so that I can manually insert comma's?

Michael,

Have you tried adding HEADER=CSV or HEADER CSVT to the beginning of your code to output the data in CSV format? Here is what the the CARLa manual states on page 140 of my PDF copy:

With HEADER=CSV, output is generated in a format that is ready for most spreadsheet programs, with

commas between field values and quotes (only) when needed.

newlist type=racf header=csv

select class=facility

sortlist profile owner uacc instdata

Results in:

BPX.CONSOLE,BPXOWNER,NONE,"WRITE MESSAGES TO Z/OS CONSOLE"

BPX.DAEMON,BPXOWNER,NONE,

With HEADER=CSVT, the column headers are collected in a separate line preceding the variable lines:

Profile key,Owner,UACC,InstData

BPX.CONSOLE,BPXOWNER,NONE,"WRITE MESSAGES TO Z/OS CONSOLE"

BPX.DAEMON,BPXOWNER,NONE,

Note: Note: you should add any quotes and escapes in overriding field titles, because the headers are generated as is.

See if this helps you to get what you want.

ALAN

Alan,

Thanks for the tip. I had somehow missed that section of the manual and was unaware of the header= statement.

I tested out my code using that option and it still has the problem I describe above where 'acl(sort(user),resolve)' converts into four space delimited fields in the report. The header= option is adding commas (and quotes) everywhere else in the report but there.

Thoughts?

Sample:

Class,Covering Profile,User Access ACL id When,Name,Installation Data

DATASET,SAMPLE.DSN1,"- any - READ * ",," "

DATASET,SAMPLE.DSN2,"USERID1 ALTER USERID1 ",," "

I have come up with the following code which seems to replicate ACL(SORT(USER)) with comma separators/delimiters. It does have issues if there are conditional permissions. And it does NOT replicate the expanded access list (ACL(SORT(USER),RESOLVE) which I need for my report. But I'm hoping it might be a step in the right direction.

Again if anyone has a solution to share it would be appreciated.

ALLOC TYPE=RACF BACKUP ACTIVE

DEFINE SUB_ACL SUBSELECT ACL(EXISTS(ID))

NEWLIST TYPE=RACF ALLOWRESTRICT REQUIRED RETAIN PL=0 HEADER=CSVT

SELECT SEGMENT=BASE CLASS=DATASET MASK=SYS1.**

SORTLIST CLASS KEY,

SUB_ACL(SORT,"ACL Type",8),

SUB_ACL(SORT,"Access",ACLACCESS),

SUB_ACL(SORT,"ACL ID",ACLID),

ACL2RSVD(SORT,"WHEN)

Hi Michael,

when you read the CARLa command reference about the supported ACL formats ACLID and ACLACCESS, you will learn that these ACL formats automatically include the relevant condition details in the output when a permission is defined with a condition.

Secondly, if I understand your requirement correctly, you want to produce an (comma separated) access overview report based on the ACL resolved format. In that case, in my opinion, you do not need to add both a column for "ACL type" an "ACL ID". In the ACL resolve format, these columns will always contain one and the same user ID.

You must make sure that all ACL fields that you specify in your SORTLIST statement use the same sorting (USER) and formatting (RESOLVE) to keep things consistent.

In this code sample, I have deliberately included only 1 column that shows the permitted user ID after resolving the ACL information.

Note: that the Access column shows the permitted access level for unconditional permits, and <accesslevel, condition> for conditional permits.

The sample output from my development system contains:

Notice that when a permission is conditional, the relevant condition (the WHEN information) is automatically included in the Access column.

I am not entirely sure whether this fully fits your requirement though.

Finally, if you want your report to also include the access that users might be allowed via OPERATIONS, then you should use the EFFECTIVE acl format rather than the RESOLVE format. Also, if your RACF database includes UNIVERSAL groups, you can add the output modifier UNIVERSAL to the ACL fields in your sortlist specification. Then, your output also inlcudes all user IDs connected to UNIVERSAL group(s) that are permitted like so:

I hope this helps.

Best regards, Tom Zeehandelaar

Hi Michael,

I just realized that my sample program lacks the information from the resolved ACL that reports the ACL ID that is the actual ID that is permitted on the ACL. In display mode, ACL(resolved) shows the resolved user IDs in column "User" and the permitted ID in column "ACL id". My sample program posted earlier today does not include that information whereas you would probably want to include that information in your report.

I could not think of a way of getting your desired results with a single CARLa program, but using a batch job that first writes output to a work data set and then post processes that information to an CSV overview might solve your challenge.

Here's my sample job:

On my development system that job produces exactly what I understand what you need. Even the conditional permissions now include comma's between the column Access, When, and Value.

Hope this helps, regards Tom