Video Screencast Help
Security Community Blog

PGP WDE Reporting w/ usable dates

Created: 25 Feb 2013
Jonathan Brain's picture
0 0 Votes
Login to vote

If someone knows an easier way to accomplish this, feel free to post and make my life even easier.

The "Export WDE Activity" report from the Universal Server is useful if you need to do things like measuring compliance.  Unfortunately none of the date fields that are exported in the CSV are sortable by MS Excel.  It has been reported that this will be corrected in a future release.  Until that time I needed to come up with a way to use the dates that were there.  I have been trying to use this report to measure compliance within our environment.  Since our client base has grown so has the data within the export and without any purging, finding the right data is challenging. This is what I have come up with to extract usable dates from the Universal Server export.

  1. Export the WDE Event data from the Universal Server & open it
  2. Create a new tab called "Month"
  3. Input the following information on this sheet:

 

Month

Numeric Value

Jan

1

Feb

2

Mar

3

Apr

4

May

5

Jun

6

Jul

7

Aug

8

Sep

9

Oct

10

Nov

11

Dec

12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(We will use this table to convert the alpha month abbreviation to the numerical format)

  1. Select all the cells and name the range "Month"
  2. To get the most current record for a computer, I use "Computer last seen" which is column K
  3. Label column "V" as "Extracted date"
  4. In cell V2 input the following formula:
  5. DATE(RIGHT(K2,4),VLOOKUP(MID(K2,5,3),Month,2,FALSE),MID(K2,9,2))
  6. Format the cell in the date format of your preference

The date in column K2 is "Thu Sep 22 15:05:29 CDT 2011" and the extracted date is "9/22/2011" which can now be sorted by excel.  By using all of the columns and sorting and filtering you can get to the data you want.

Hope this is of some use for some other PGP admin out there.