7 Replies Latest reply on Mar 3, 2015 4:01 PM by John Siegel

    Seeing more than 1,000 entries in Pharos Admin

    John Siegel Guide

      We have just updated our script to add a second email account to all our 27,000 users in the Pharos DB. I want to review the all user accounts to verify that  they have been added. Is there a way to view all of the accounts at once so I can scroll through, or am I stuck with the 1,000 results that the system kicks back?

       

      Thanks,

       

                    John

        • Re: Seeing more than 1,000 entries in Pharos Admin
          Steven English Guide

          John,

           

          I would do a SQL query in Management Studio to check.

           

          USE [pharos]
          GO
          
          SELECT
              id
             ,last_name
             ,first_names
             ,email
          FROM users
          ORDER BY email
          

           

          Regards,

          Steven

           

           

          Not sure if the syntax highlighting is going to work so here it is again...

          ---------------------------------------------------------------------------------------------------------------------

          USE [pharos]

          GO

           

          SELECT

               id

               ,last_name

               ,first_names

               ,email

          FROM users

          ORDER BY email

          2 of 2 people found this helpful
            • Re: Seeing more than 1,000 entries in Pharos Admin
              John Siegel Guide

              Good idea, I hadn't thought of that, thanks! 

               

              ~John

                • Re: Seeing more than 1,000 entries in Pharos Admin
                  Jeff Geller Guide

                  Hi John,

                   

                  There is also a log file that gets generated when you use the userload.exe command (8.4 and above) from a command line or you use the Batch Load Users from within the Pharos Administrator.  The log file is generated in the directory that the LogSetter is configured to write the log files to. From the log file you can see the results of the inserts/updates of the users.

                   

                  This will give you a quick way to search through the log file for failures or easily check the results from the load.  Below is a sample of what you will see.

                   

                  BatchLoader] User record summary:

                  BatchLoader] ====================

                  BatchLoader]   Inserted: 4

                  BatchLoader]   Updated:  0

                  BatchLoader]   Deleted:  0

                  BatchLoader]   Errors:   0

                   

                  For any errors you can find these in the log file for the particular user and the information is helpful in understanding why a particular user failed.

                   

                  For example a failure when you do not have enough arguments in one of the rows of data you will see the below error.

                  [202894] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader] User record summary:

                  [202895] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader] ====================

                  [202896] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader]   Inserted: 0

                  [202897] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader]   Updated:  3

                  [202898] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader]   Deleted:  0

                  [202899] :: [2015/01/22 14:27:37.969 P143C T680 i BatchLoader]   Errors:   1

                   

                  Then you can find:

                  [202890] :: [2015/01/22 14:27:37.969 P143C T680 d BatchLoader] Too many fields in the line

                  [202891] :: [2015/01/22 14:27:37.969 P143C T680 w BatchLoader] Incorrect # of args on line:

                   

                  Thanks,

                  Jeff Geller

                  Pharos Systems Technical Support

                  1 of 1 people found this helpful
                    • Re: Seeing more than 1,000 entries in Pharos Admin
                      John Siegel Guide

                      Thanks Jeff, Good to know.

                       

                                                                     We don't run any batch files. Up until we added Mobileprint, we didn't keep any user data in the Pharos DB.  So we have never used the batch-loading capabilities. Instead, we are using a script that captures the user data at the omega and populates the Pharos DB. We only use the Pharos DB to authenticate emails for the Mobileprint users. All the banking is done on the CBORD side, which is run by University Housing.  

                       

                      Regards,

                       

                                     John                       

                  • Re: Seeing more than 1,000 entries in Pharos Admin
                    John Siegel Guide

                    FYI - worked like a charm,

                     

                    Thanks,

                     

                                 John

                    • Re: Seeing more than 1,000 entries in Pharos Admin
                      Steven English Guide

                      An improved query to filter to only show users without multiple email addresses is as follows (only posting it because I still had the unsaved query in MSSSMS).  If the email address field is empty (NULL), or the field does not have a comma (indicating more than one address - comma separated and all), it will be included in the list.

                       

                      USE [pharos]  
                      GO
                      
                      SELECT
                           id
                           ,last_name  
                           ,first_names  
                           ,email  
                      FROM users  
                      WHERE email NOT LIKE '%,%'
                           OR email IS NULL
                      ORDER BY email
                      

                       

                       

                      Not sure if the syntax highlighting is going to work so here it is again...

                      ---------------------------------------------------------------------------------------------------------------------

                      USE [pharos] 

                      GO

                       

                      SELECT

                           id

                           ,last_name 

                           ,first_names 

                           ,email 

                      FROM users 

                      WHERE email NOT LIKE '%,%'

                           OR email IS NULL

                      ORDER BY email

                      1 of 1 people found this helpful