Whilst trying to automate some reporting automation and analysis , I overcome a requirement to reformat a csv ( loosest sense of the term) so that PowerShell will process it.  The following is a useful demonstration of how to manipulate text data and reformat it, also the use of regex (Regular Expression) in PowerShell to identify the current days file.

This has been tested against PowerShell 1.0 on Microsoft Windows Server 2003

##
# A csv Files report is created but you are unable to load it as a csv File due to irregular  formatting of the report.
# this will serve as a good example of string manipulation.
#
# Issues with csv file
# remove top 6 lines, header information not required.
# headers contain , but are not quoted, so you now have more columns than data
# trailing , stop PowerShell loading csv as you can not have a blank header
# the data is includes a % sign.  this then stop it being a number when imported into SQL / Excel
#———————————————————–
#
#
$tmpfile    = “temp.csv”
$mydat        = (get-date ((get-date).adddays(-1))-uformat “%Y%m%d”)    # get yesterdays date in the correct format
$filter        = [regex] “^Daily-View.*$mydat[0-9]{6}\.csv$”            # Use regex to find today’s file
$csvfile    = Get-ChildItem -Path “c:\Joe\test\*.csv” | Where-Object {$_.Name -match $filter} # return 1 filename

#              use PowerShell to drop the first 6 lines of unwanted text at the beginning of the file
#
$csvContent = get-content $csvfile

$csvContent | select -last ($csvContent.count -6) | Out-File $tmpfile

#             replace “, %”   with  ” %” 
#
$csvContent = “”
$csvContent = get-content $tmpfile
$csvContent[0] = $csvContent[0].replace(“, %”,” %”)

#             remove trailing , on header line
#
$csvContent[0] = $csvContent[0].substring(0,($csvContent[0].length)-1)     # remove the last char on the line

#             remove all the “%” signs from the data  ( but not the header) 
#
$counter = 0
foreach ($line in $csvContent)
{
    if ($counter -ne 0)     # skip the header line
    {
    $csvContent[$counter] =$csvContent[$counter].replace(“%”,”")
    }
$counter++
}

#            output the plain txt file
#
$csvContent | out-file $tmpfile

#             now you can import the file as a csv with no errors
#
$csvdata = import-csv $tmpfile
$csvdata

#        ————————————–
#         now you can do stuff with the data here
#        ————————————–

#         Remove old temp file
#
if (test-path $tmpfile)
    {
        remove-item -path $tmpfile -force
    }

As part of a recent engagement I was asked to implement a solution to automatically export & archive System and Security logs from servers to a central location, the requirements were:

• Nightly time stamped archive of Security and System event logs to a central location
• Clear the local log once the archive has been successfully taken

I put together the following PowerShell script to achieve the above:

$locallocation = "c:\logs\"
$remotelocation = "\\fileserver\EventLogs\"
$localmachine = $env:computername

$evtlgs = Get-WMIObject -Class Win32_NTEventLogFile -Computer $localmachine
foreach ($log in $evtlgs)
    {
    if ($log.LogFileName -eq "System" -or $log.LogFileName -eq "Security")
        {
        $timestamp = get-date -f yyyyMMddHHmmss
        $path = $log.LogFileName + $timestamp
        $store = $locallocation+$path+".evt"
        $backup = ($log.backupeventlog($store)).ReturnValue
        if($backup -eq 0)
            {
            $log.ClearEventLog() | out-null
            }     
        move-item $locallocation* $remotelocation$localmachine\
        }
    }

The above script is executed by a Scheduled Task (which on another note are brilliant on Server 2008), the lines you’re interested in are the top 2 lines which configure a local location to write the log out to and the remote location to move the log to once it has been written.  I ran this script using a service account which has permission to write to the local and remote locations. 

If you wanted a different selection of logs to be archived you would adjust the

if ($log.LogFileName -eq "System" -or $log.LogFileName -eq "Security")

line to suit your requirements.

In our requirement the logs had to be archived daily, this was simply achieved by configuring task scheduler to run once per day at the desired time, no code changes are required. 

The requirement for only clearing the local log if the export was successful is met by checking the exit code form the backup, if this wasn’t 0 then the log wont be cleared.