1.    13 Apr 2017 #1
    Join Date : Dec 2015
    Athens, Greece
    Posts : 141
    Windows 10 Pro x64

    Export All Administrative Events to Excel


    To analyze events, from the Windows Event Viewer, there is a simple way to export all Administrative Events to Excel, with PowerShell.

    Exporting all Administrative Events to Excel is a simple two Step process, as described here:

    Step 1 - Create the Administrative Events View .xml file
    1. Open Eventviewer (%windir%\system32\eventvwr.msc)
    2. Navigate to: Event Viewer (Local) > Custom Views > Administrative Events
    3. In the “Actions” pane select “Filter Current Custom View”.
    4. Select the the XML tab.
    5. Press Ctrl+A to select all the XML code of the Custom View.
    6. Open a notepad, paste the selected code and save the file to your Desktop as AdmEvtView.xml


    Step 2 - Create the csv file with the events
    1. Download the ExportEvtCSV.zip file, which contains the script ExportEvtCSV.ps1 and unzip it, on your Desktop.
      It's not a fancy script, just basic PowerShell commands to create a csv file on the Desktop.
    2. In Windows Search, type “ISE” (without the quotes) to open “Windows PowerShell ISE” and Run as administrator
    3. To allow running the script, change the ExecutionPolicy, for this session. To do that, in the Console pane type:
      Code:
      Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -force
    4. In the Windows PowerShell ISE, open and run the script: ExportEvtCSV.ps1
      The script will create a csv file with a name YYYYMMDD.HHMM.csv on the Desktop
    5. When done, open the newly created .csv file, format the columns as needed and optionally save it as .xlsx, if you wish.

    That’s it! You now have all the Administrative Events in Excel for filtering and further analysis.

    Now to the more technical hard stuff...

    There is a reason for running the script from within PowerShell ISE!

    It would be great if everything was also working perfectly, when running the script from an elevated PowerShell too.

    We can run it from an elevated PowerShell, which means that you just follow the Step 1, as above but for the Step 2 instead of the ISE you run the script from an elevated PowerShell.

    The problem is that it will work only for anybody who has en-US format for the dates. Everyone else, who has another format (i.e. en-GB, fr-FR, el-GR etc.), the dates are not translated properly by Excel (although the script uses the –UseCulture switch) and remain as text in the en-US format.

    I'm not sure if this a bug of the "export-csv" cmdlet, but although it runs the way it supposed to from within the ISE, from PowerShell there is a problem with the dates format.
    As I haven’t found a way to overcome this obstacle, any suggestion from the PowerShell gurus of the forum (like my good friend Shawn @Brink, for instance), is welcome.
      My ComputerSystem Spec
  2.    14 Apr 2017 #2
    Join Date : Mar 2015
    Sydney, NSW
    Posts : 93
    Windows 10

    I can’t replicate this. The date formats in the output CSV of your script from both the PowerShell console and the ISE are similar for me.

    Could you test around with the following line and see if you can still draw a difference.
    Code:
    [pscustomobject]@{Foo='Bar';Date=Get-Date} | Export-Csv "$env:UserProfile\Desktop\csv.csv" -NoTypeInfo
      My ComputerSystem Spec
  3.    14 Apr 2017 #3
    Join Date : Dec 2015
    Athens, Greece
    Posts : 141
    Windows 10 Pro x64
    Thread Starter

    To replicate:
    Run the script form an elevated PowerShell in whatever regional formats you have. In your case, I would guess English (Australian).
    Open the csv produced, in Excel. According to what I get, you should have something like this –my Regional settings are English (Europe)- :

    English (Europe)
    Click image for larger version. 

Name:	en-EU.png 
Views:	0 
Size:	133.3 KB 
ID:	130111

    Now go to Control Panel > Languages > Change date, time, or number formats and change the format to English (United States).
    Open again the same csv in Excel. This time dates should be OK and you should have something like this:

    English (United States)
    Click image for larger version. 

Name:	en-US.png 
Views:	0 
Size:	132.6 KB 
ID:	130113

    The dates of the log, range from 11-Apr-2017 to 14-Apr-2017. Notice the difference in the format?
    In the English (Europe) settings, the dates with day up to 12 are translated as December dates, whereas the days with date beyond 12 are not translated at all.


    Change back to your Default Regional Settings. If you now run the script from an elevated ISE, when opened in Excel the dates are correct, regardless of the current regional settings.

    Now regarding your recommendation, I ran the command from both ISE and PowerShell. As expected this is the output in Excel:

    Click image for larger version. 

Name:	csv.png 
Views:	0 
Size:	55.5 KB 
ID:	130123
      My ComputerSystem Spec
  4.    15 Apr 2017 #4
    Join Date : Mar 2015
    Sydney, NSW
    Posts : 93
    Windows 10

    Hm, dunno, I think you’re crazy. I followed the steps as closely as possible and I didn’t notice anything out of the ordinary.

    Compare the $Host variable from both the PowerShell console and ISE and make sure the CurrentCulture property matches between them. This is the only variable I can think of that could cause a difference.
      My ComputerSystem Spec
  5.    15 Apr 2017 #5
    Join Date : Dec 2015
    Athens, Greece
    Posts : 141
    Windows 10 Pro x64
    Thread Starter

    cultureinfo in ISE and PowerShell don't match... I'm not getting crazy, I just state facts!

    Click image for larger version. 

Name:	Get-Culture.png 
Views:	0 
Size:	72.5 KB 
ID:	130308

    Click image for larger version. 

Name:	Get-Culture.png 
Views:	0 
Size:	150.9 KB 
ID:	130300

    The only way I have found so far, is to change the date format to the Default culture, so PowerShell does not use its own.

    That changes the command to:

    Code:
    Get-WinEvent -FilterXml ([xml](Get-Content "AdmEvtView.xml")) | Select-Object Level, LevelDisplayName, @{n='TimeCreated';e={Get-Date ($_.TimeCreated) -Format 'dd/MM/yyyy HH:mm:ss'}}, ProviderName, Id, Task, Message, LogName, UserId, TaskDisplayName, Version, Qualifiers, Opcode, OpcodeDisplayName, RecordId, ProcessId, ProviderId, ThreadId, ActivityId, RelatedActivityId, MachineName, Keywords, KeywordsDisplayNames, MatchedQueryIds, Bookmark, Properties | export-csv "$FileDate.csv" -UseCulture –NoTypeInformation
    Last edited by ddelo; 15 Apr 2017 at 13:35.
      My ComputerSystem Spec
  6.    18 Apr 2017 #6
    Join Date : Mar 2015
    Sydney, NSW
    Posts : 93
    Windows 10

    The CultureInfo of your elevated PowerShell console isn’t correct if “English (Europe)” is your current locale. I wouldn’t have a reason why this is the case though.

    If I change my locale either through the GUI or Set-Culture, the CultureInfo of both PowerShell hosts will match after restarting the program.
      My ComputerSystem Spec
  7.    18 Apr 2017 #7
    Join Date : Dec 2015
    Athens, Greece
    Posts : 141
    Windows 10 Pro x64
    Thread Starter

    That's exactly what I'm saying. The English (Europe) [en-150] locale is correct, because this the locale I have chosen. ISE displays it correctly.
    It's PowerShell that erroneously reports en-us. en-us is the installation language, not my locale.
    Now why this discrepancy.... I have no clue!
      My ComputerSystem Spec

 


Similar Threads
Thread Forum
Attached file "Administrative Events" shows many problems
My Windows 10 was installed on top of Windows 7 and, since then, I have been trying to correct the many little glitches I've run across. Not always successful. This view of my Admi119945nistrative Events is typical and not normal - but I don't...
General Support
no export favorites button in EDGE. How do I export my favorites?
no export favorites button in EDGE. How do I export my favorites including the favorites line in the top?
Browsers and Email
Administrative Right Issues Win 10
Hello guys, I've been having a mayor issue with my system so far, I'm getting Access Declined or that I'm not signed into an Administrative account. Now I only seem to get this issue in to cases as far as I know, One is when I attempt to use my...
User Accounts and Family Safety
Solved How can I force an export to a certain version of Excel?
I have both Excel 2000 and Excel 2013 on my Windows 10 machine for reasons that don't need enumeration here. I am working with a program now that will export/import to Excel, but it looks like it might work only with Excel 2013. However, when I do...
Software and Apps
Numerous "Administrative Events" Errors after upgrade
I finished upgrading to Windows 10 today. I looked at various features for a while. Then I Looked at the Event Viewer--Administrative Events. The following errors were logged: Event ID = 513, 24583, 8229, 1, 3041, 17207, 3041, 17204, 131, 69, 131,...
General Support
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 15:35.
Find Us
Twitter Facebook Google+ Ten Forums iOS App Ten Forums Android App



Windows 10 Forums