# Exporting server info to excel document



## Earl003 (Jun 6, 2010)

Hi,

I need to automate the checking of multiple servers(Server 2003) and then exporting the info into an Excel spreadsheet.

I firstly need to filter only the application and system errors that occur on a server during a specified time and export this to an excel spreadsheet.

Next I would need to record the hard disk size and free space available of all hard drives on each server then export this info to the same exce document.

Lastly the page file memory maximum and amount used as displayed in the task manager exported to the excel document as well.

I need to have this automated requiring minimal interaction from me but this does not have to only be exported to Excel - Can be exported to HTML as long as info is seperated according to the relevant server as the info needs to be printed everyday.


----------



## digitalsatori (Apr 28, 2010)

I would post this question in the Software Development forum, as what you are asking will require some scripting.

I am not a programmer, I usually just play around with VB scripting. But, I did piece together some bits of code that will pull the information you want in to a .CVS file which you can then import in to Excel. Because I'm limited in my programming know-how, I'm not entirely sure how to have the script prompt you for specific times/dates for the event logs. As it stands now, it will pull the event logs from the last 5 days. You can change this by modifying the first line of the eventlogs.vbs script:


```
strEventDate = FormatDateTime(DateAdd("d",-5,Now()),2)
```
Change the -5 to -10 for 10 days or -1 for one day, etc.

You will need to run these scripts from a batch file or the command prompt so you can output the data to a .csv file. Copy these two sections in to Notepad and save them with a .vbs extension. Then, open a command prompt and call the scripts using the following command:

*cscript c:\eventlogs.vbs > eventlogs.csv*
*cscript c:\HDandPF.vbs > HSandPF.csv*

This will create two .csv files that you can then import in to Excel and format as needed. If you're feeling up to it, you are more than welcome to play with the code and adjust it as needed. The *> *switch will overwrite the existing .csv file. You can use two >> symbols to append the .csv file (*cscript c:\HDandPF.vbs >> HSandPF.csv*)

Here's the code:

*eventlog.vbs*

```
strEventDate = FormatDateTime(DateAdd("d",-5,Now()),2)
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_NTLogEvent Where Logfile = 'System' and " _
    & "TimeWritten > '" & strEventDate & "'") 
For Each objEvent in colLoggedEvents
    Wscript.Echo "System Log,"
    Wscript.Echo "EventCategory," & objEvent.Category
    Wscript.Echo "EventComputerName," & objEvent.ComputerName
    Wscript.Echo "EventCode," & objEvent.EventCode
    Wscript.Echo "EventMessage," & objEvent.Message
    Wscript.Echo "EventRecord," & objEvent.RecordNumber
    Wscript.Echo "EventSource," & objEvent.SourceName
    Wscript.Echo "EventTimeWritten," & objEvent.TimeWritten
    Wscript.Echo "EventType," & objEvent.Type
    Wscript.Echo "EventUser," & objEvent.User
Next
strEventDate = FormatDateTime(DateAdd("d",-5,Now()),2)
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_NTLogEvent Where Logfile = 'Application' and " _
    & "TimeWritten > '" & strEventDate & "'") 
For Each objEvent in colLoggedEvents
    Wscript.Echo "Application Log,"
    Wscript.Echo "EventCategory," & objEvent.Category
    Wscript.Echo "EventComputerName," & objEvent.ComputerName
    Wscript.Echo "EventCode," & objEvent.EventCode
    Wscript.Echo "EventMessage," & objEvent.Message
    Wscript.Echo "EventRecord," & objEvent.RecordNumber
    Wscript.Echo "EventSource," & objEvent.SourceName
    Wscript.Echo "EventTimeWritten," & objEvent.TimeWritten
    Wscript.Echo "EventType," & objEvent.Type
    Wscript.Echo "EventUser," & objEvent.User
Next
```
*HDandPF.vbs*

```
const HARD_DISK=3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")
For Each objDisk in colDisks
    Wscript.Echo "HDDeviceID," & objDisk.DeviceID
    Wscript.Echo "HDTotalDiskSpace," & objDisk.Size
    Wscript.Echo "HDFreeDiskSpace," & objDisk.FreeSpace
Next 
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colPageFiles = objWMIService.ExecQuery("Select * from Win32_PageFile")
For Each objPageFile in colPageFiles
    Wscript.Echo "PFCreationDate," & objPageFile.CreationDate
    Wscript.Echo "PFFileName," & objPageFile.FileName  
    Wscript.Echo "PFFileSizeK," & objPageFile.FileSize  
    Wscript.Echo "PFInitialSizeMB," & objPageFile.InitialSize
    Wscript.Echo "PFMaximumSizeMB," & objPageFile.MaximumSize
Next
```


----------

