How to parse GlobalProtect usernames from the System log for something like emailing a list of users
- On the PA’s Monitor Tab click on System
- Enter this search string
- ( description contains 'authenticated for user') and ( description contains 'LDAP-sAM-attribute' ) -substitute LDAP-sAM-attribute with your auth profile used specifically for the GlobalProtect authentications or use another unique contains string to limit your results to only GP users
- Click the Excel icon to begin the export
- Download the file when completed
- Open Excel and create a new spreadsheet
- Select Data | Get External Data | Import from Text File
- Browse to your downloaded PA firewall export CSV log file and Get Data
- On the Text Import Wizard select Delimited and Start import at row 2
- Click Next
- Be sure that no delimeters are selected
- Check the Other: box and enter an apostrophe ‘
- Now you will see a preview of the data to be imported
- For the Column data format select (Skip) for all columns except for the column that has the usernames in it.
- Now click Finish and OK to import in the existing sheet at the cell you would like to start with, typically =$A$1
- Optionally you could just import ‘as is’ and delete the columns you don’t need.
- Now we must remove any duplicates
- Select your column of usernames
- Click on Data and Remove Duplicates | Remove Duplicates
- Now you may copy that column and paste it into an email recipients list
- Click “Check Names” to verify the users match existing address book users and eliminate any that were not found.
- Draft your message and SEND!