Export User profiles

[as400srive99]

Hello,
To export users from AS400 server, i follow below steps.

login to as400
# dspusrprf
# UserProfile - *All
# Type of info - *Basic
# Output outfile - *outfile
# library - asam
# Member to receive output - *First
# Replace or add records _ *Replace

and then I do import profiles using excel but the problem, I see values getting concatenated for the Supplemental group profile. Since we deal
with large number of users profile, I would like to see if there is any way to export user profiles with supplemental groups so that values would not
concatenated. Thanks in advance.

Regards

[tomholden]

Re: Export User profiles

you'll have to break out the supplemental groups in your code using substring functionality and dump that into a different file for you to export into excel.

[jamief]

Re: Export User profiles

would this work for you?

     H NOMAIN EXPROPTS(*RESDECPOS)                                                                  
      * Beginning of procedure                                                                      
                                                                                                    
      /COPY QSECURESRC,USRDATA_CP                                                                   
                                                                                                    
     P usrdata         B                   EXPORT                                                   
                                                                                                    
      *------------------------------------------------                                             
      **  E N T R Y   P A R M S                      **                                             
      *------------------------------------------------                                             
                                                                                                    
     d usrdata         pi          1000                                                             
     d  inUser                       10a   const                                                    
                                                                                                    
     d $GetUsrInf      PR                  ExtPgm('QSYRUSRI')                                       
     d   RcvVar                      53                                                             
     d   RcvVarLen                   10i 0                                                          
     d   Format                       8    const                                                    
     d   UserPrf                     10    const                                                    
     d   Error                        1    const                                                    
                                                                                                    
     d QWCCVTDT        pr                  ExtPgm('QWCCVTDT')                                       
     d   infmt                       10a   const                                                    
     d   invar                       65a   const options(*varsize)                                  
     d   outfmt                      10a   const                                                    
     d   outvar                      65a   options(*varsize)                                        
     d   ErrCode                  32767a   options(*varsize)                                        
                                                                                                    
     d ErrorCode       ds                  qualified                                                
     d   BytesProv                   10i 0 inz(0)                                                   
     d   BytesAvail                  10i 0 inz(0)                                                   
                                                                                                    
     d YYMD            DS                  qualified                                                
     d   date                         8a                                                            
     d   time                         6a                                                            
     d   milli                        3a                                                            
                                                                                                    
     d count           s              2  0 inz                                                      
     d isodate         s               d                                                            
     d VarLength       s             10i 0 inz(1000)                                                
                                                                                                    
      /free                                                                                         
                                                                                                    
       clear profiledata.Suppgroups;                                                                
       $GetUsrInf(ProfileData:VarLength:'USRI0300':INUSER:' ');                                     
                                                                                                    
       // fix supplemental groups using PROFILEDATA.NUMBSUPGRPS                                     
       for count = 1 to 13;                                                                         
        if count > PROFILEDATA.NUMBSUPGRPS;                                                         
         profiledata.Suppgroups(count) = *blanks;                                                   
        endif;                                                                                      
       endfor;                                                                                      
                                                                                                    
       QWCCVTDT( '*DTS'                                                                             
                 : profiledata.PWExpDate                                                            
                 : '*YYMD'                                                                          
                 : YYMD                                                                             
                 : ErrorCode );                                                                     
                                                                                                    
       ISodate = %date(YYMD.date:*iso0);                                                            
       profiledata.PWExpDate = %editc(%dec(Isodate:*iso):'X');                                      
                                                                                                    
       QWCCVTDT( '*DTS'                                                                             
                 : profiledata.PWChageDate                                                          
                 : '*YYMD'                                                                          
                 : YYMD                                                                             
                 : ErrorCode );                                                                     
                                                                                                    
       ISodate = %date(YYMD.date:*iso0);                                                            
       profiledata.PWChageDate = %editc(%dec(Isodate:*iso):'X');                                    
                                                                                                    
       return  ProfileData;                                                                         
                                                                                                    
      /end-free                                                                                     
                                                                                                    
     P usrdata         E 

The copy book:

                                                                
 * USRDATA_CP - Get user profile information                    
                                                                
d USRDATA         pr          1000                              
d  inUser                       10a   const                     
                                                                
**-- UserProfile Data                                           
d ProfileData     ds                  Qualified                 
d  BytesReturned                10i 0                           
d  BytesAvail                   10i 0                           
d  Profile                      10a                             
d  PreviousSO                   13a                             
d  Reserved1                     1a                             
d  InvalidSO#                   10i 0                           
d  Status                       10a                             
d  PWChageDate                   8a                             
d  NoPassword                    1a                             
d  Reserved2                     1a                             
d  PWExpInt                     10i 0                           
d  PWExpDate                     8a                         
d  PWDaysToEXP                  10i 0                       
d  SetPW2Exp                     1a                         
d  UserClass                    10a                         
d  SpecialAuth                  15a                         
d  GroupProfile                 10a                         
d  Owner                        10a                         
d  GrpAuthority                 10a                         
d  AssitanceLevl                10a                         
d  CurrentLib                   10a                         
d  InitalMenu                   10a                         
d  InitalMenuLib                10a                         
d  InitalPgm                    10a                         
d  InitalPgmLib                 10a                         
d  LimitCap                     10a                         
d  TextDesc                     50a                         
d  DspSignOnInf                 10a                         
d  LimitSessions                10a                         
d  KeyBoardBuff                 10a                         
d  Reserved3                     2a                         
d  MaxStorage                   10i 0                     
d  StorageUsed                  10i 0                     
d  HighestSchd                   1a                       
d  JobDesc                      10a                       
d  JobDescLib                   10a                       
d  AccountCode                  15a                       
d  MsgQ                         10a                       
d  MsgqLib                      10a                       
d  MsgqDelv                     10a                       
d  Reserved4                     2a                       
d  MsgqSev                      10i 0                     
d  OutQ                         10a                       
d  OutqLib                      10a                       
d  PrintDev                     10a                       
d  SpecialEnv                   10a                       
d  AttnPgm                      10a                       
d  AttnPgmLib                   10a                       
d  Language                     10a                       
d  Country                      10a                       
d  CharCode                     10i 0                     
d  UserOpts                     36a               
d  SortSeqTbl                   10a               
d  SortSeqTblLib                10a               
d  ObjectAudVal                 10a               
d  UserActAudLvl                64a               
d  GrpAuthType                  10a               
d  Offset2SupGrp                10i 0             
d  NumbSupGrps                  10i 0             
d  UserID#                      10i 0             
d  GroupID#                     10i 0             
d  OffsetHomeDir                10i 0             
d  LengthHomeDir                10i 0             
d  LocalJobAtrb                 16                
d  OffsetLocPath                10i 0             
d  LengthLocPath                10i 0             
d  GroupMbrInd                   1                
d  DigitalCert                   1                
d  CharIDCntrl                  10                
d  OffsetASPUsg                 10i 0             
d  NbrASPStgUsg                 10i 0             
d  NbrASPStgUsgD                10i 0                                               
d  LenASPStgUsgD                10i 0                                               
d  Reserved5                     2a                                                 
d  MoreInfo                    500                                                  
d  Thirty32paces                32    overlay(MoreInfo:1)                           
d  Suppgroups                   10    dim(15) overlay(MoreInfo:*next) 

[tomliotta]

Re: Export User profiles

Easiest might be to create a VIEW that presents that column as a set of separate SUBSTR() values. Then export the VIEW instead the underlying PF.

[as400srive99]

Re: Export User profiles

Thanks all for your replies...

since I have no experience with as400 and i just need to export user profiles, how do we create view. is there any document that I could follow and also what rights we need to create view.
also just an fyi..i see values are separated in the as400 as below...

Supplemental Groups value1
value2
value3...etc

for when i import to the excel i see

value1 value2value3
value1value3
value1value2 value3
value1 value2 value3

Thanks again..

[tomliotta]

Re: Export User profiles

Creating a SQL VIEW is done with the SQL CREATE VIEW statement. The details of the statement would depend on the actual details needed in your export. A trivial example might be:

CREATE VIEW QTEMP/MYUSRPRF
 (UPUPRF, UPUSCL, UPPWON, SG1, SG2, SG3) AS
   SELECT UPUPRF, UPUSCL, UPPWON, substr(UPSUPG ,1,10), substr(UPSUPG ,11,10), substr(UPSUPG ,21,10)
     FROM qtemp/usrprf

That only picks up a few of the possible fields and it only separates the first three supplemental groups. Additional supplementals can be supplied by adding more SUBSTR() specs. It assumes that a TABLE named USRPRF exists in QTEMP. The VIEW is created in QTEMP and is named MYUSRPRF.

To show a more useful example, the OS release level of your server could be helpful. Also, the programming statements that create your current exported file would also be helpful.