Need help with powershell script to add information to a CSV file RRS feed

  • Question

  • Hi 

    I have a powershell script that used to create an html file but is now being converted to a csv file.

    Most of it is working. There is a part in the script where it finds the last logon for the user to be 0 or greater than 30 days and it used to highlight these users in red in the html file.

    I am trying to work out a way to do a similar thing in the csv file. I realise you can't change colours in the csv file but I wanted to see if an extra column with say yes could be added next to the users who have a logon of 0 or greater than 30 days.

    Below is the script

    Import-Module ActiveDirectory
    . .\ConvertTo-DN.ps1
    $UserOUDN = ConvertFrom-Canonical -canoincal "contoso.local/MyBusiness/Users/SBSUsers"
    # Format the number column
    $count = 0
    $rownum = @{label="Num";e={[int]}}
    # Get the list of users
    $users = Get-ADUser -filter * -searchbase $UserOUDN -Properties SAMAccountName, GivenName, Surname, EmailAddress, Description, LastLogonDate, Enabled | Select-Object $rownum, @{e={$_.SAMAccountName};n="Username"}, @{e={$_.GivenName};n="First Name"}, @{e={$_.Surname};n="Last Name"}, @{e={$_.EmailAddress};n="Email"}, @{e={$_.Description};n="Description"}, @{e={$_.LastLogonDate.tostring("dd/MM/yyyy")};n="Last Logon"}, @{e={$_.Enabled};n="Enabled"} | Sort-Object "Last Name" 
    # Increment the number column and append to table
    foreach ($user in $users) { $user.Num = $count += 1 } 
    $UserCount = $users.count

    if ($user.'Last Logon' -eq $null) {
            write-host "yes"
        } else {
            $now = Get-Date
            $LogonTime = [datetime]::ParseExact($user.'Last Logon',"dd/MM/yyyy",$null)
            $span = $now - $LogonTime
            if ($span.days -gt 30) {
                write-host "yes"

    $users | Export-Csv -Path tt.csv -NoTypeInformation

    Any help appreciated


    Monday, August 29, 2016 5:44 AM


  • Hi

    I use something like the below to short in color if there is a problem with a DAG node, maybe it helps:

    $line = Get-MailboxDatabaseCopyStatus -Identity "DAG*" | Format-Table -AutoSize > "C:\Scripts\DAGStatus.txt"
    $split = get-content "C:\Scripts\DAGStatus.txt"
    foreach ($line in $split) {
    #append some space after the line
    $params=@{Object=" "}
    Write-Host "$line " -NoNewline
    #look at the line and add a parameter based on the results
    #of a regular expression match
    switch -Regex ($line) {
    "Dismounted" { $params.BackgroundColor = "Red" }
    "Running" { $params.BackgroundColor = "DarkGreen" }
    "Healthy" { $params.BackgroundColor = "DarkGreen" }
    "FailedandSuspended" { $params.BackgroundColor = "Red" }
    "Failed" { $params.BackgroundColor = "Red" }
    "Suspended" { $params.BackgroundColor = "Yellow" }
    "DisconnectedAndHealthy" { $params.BackgroundColor = "Red" }

    #write the line with the splatted parameters
    Write-Host @params
    } #close foreach

    Edward van Biljon - Exchange MVP

    Tuesday, August 30, 2016 10:40 AM