Output to hash to CSV???

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of Rocky Cabral Rocky Cabral 1 year, 10 months ago.

  • Author
    Posts
  • #22055
    Profile photo of Rocky Cabral
    Rocky Cabral
    Participant

    I'm a Powershell novice and I've been working on putting a script together that I can't seem to get what I want.
    I tweaked a script I found online that gives queries Active Directory servers, pings boxes to make sure they are alive and enumerates all "alive" boxes for all services with non-system service accounts. I\'m cleaning up stale or not used service accounts in Active Directory and this script would be a great help.

    The issue is, I'm stuck in how to get the output to a hash variable to create a nice CSV report I can email the team.
    I've included the script. Hopefully you can help me out finalize the script. Thanks!

    # Get the list of servers from Active Directory
    $servers = Get-ADComputer -Filter {OperatingSystem -Like \"Windows *Server*\"} -Properties *

    # Set the live computers array
    $livecomputers = @()

    # Create a object to store results
    $Report = @()

    # Check each server to make sure it is up.
    foreach ($x in $servers) {
    $up = Test-Connection -Count 1 -ComputerName $x.Name -ErrorAction SilentlyContinue
    if ( $up -ne $null){ $livecomputers += $x.Name }
    }

    # Main Function – Using WMI, check each service and find any service that is using a non-standard account
    foreach ($machine in $livecomputers){

    gwmi win32_service -computer $machine | where {$_.StartName -ne “LocalSystem”}|where {$_.StartName -ne “NT AUTHORITY\\LocalService”} | where {$_.StartName -ne “NT AUTHORITY\\NetworkService”} | `
    where {$_.StartName -ne “Local System”}|where {$_.StartName -ne “NT AUTHORITY\\Local Service”} |where {$_.StartName -ne “NT AUTHORITY\\Network Service”}
    }

    foreach ($winserv in $getserv) {
    # For each service – build a hash containing information
    $hash = @{
    ComputerName = $winserv.PSComputerName
    Service = $winserv.Name
    ServiceAccount = $winserv.StartName

    }
    # Add the hash to a new object
    $winservinfo = new-object PSObject -Property $hash
    # Store our new object within the report array
    $Report += $winservinfo
    }

    # Export our report array to CSV and store as our dynamic file name
    $Report | Export-Csv D:\\ServiceAccounts.csv -Append -NoTypeInformation

  • #22056
    Profile photo of Sam Boutros
    Sam Boutros
    Participant

    try:

    # Get the list of servers from Active Directory
    $Servers = Get-ADComputer -Filter { OperatingSystem -Like 'Windows *Server*' } -Properties *
    
    # Check each server to make sure it is up.
    $LiveComputers = @()
    Write-Host 'Checking which computers are online' -NoNewline
    $Servers | % {
        Write-Host '.' -NoNewline
        if (Test-Connection -Count 1 -ComputerName $_.Name -EA 0) { $LiveComputers += $_.Name }
    }
    Write-Host '.'
    
    # Find any service that is using a non-standard account
    $Services = @()
    $LiveComputers | % {
        $Services += Get-WmiObject win32_service -ComputerName $_ | 
            where { $_.StartName -notmatch 'nt authority' -and $_.StartName -notmatch 'LocalSystem' } 
    }
    $Services | Select SystemName,Name,StartName | FT -a 
    
    # Export to CSV
    $Services | Select SystemName,Name,StartName | Export-Csv .\ServiceAccounts.csv -Append -NoTypeInformation
    
  • #22061
    Profile photo of Rocky Cabral
    Rocky Cabral
    Participant

    Thanks Sam! I'll give that a shot!

  • #22062
    Profile photo of Rocky Cabral
    Rocky Cabral
    Participant

    Sam...thanks again. That worked great! But do have a question.

    Why didn't you have to create a hash and then create an object with hash to format the CSV?
    I've seen scripts where a hash is created then added to a object to format data in a CSV.
    Your script looks a lot more clean and simple. It totally changes my notion of using this
    technique in outputting queried data to CSV without needing to create a hash.

  • #22078
    Profile photo of Sam Boutros
    Sam Boutros
    Participant

    You're welcome.

    I didn't create a hash because the properties I needed are already there in the returned object from Get-WmiObject, and I did not need to manipulate them.

    Let's take this one step at a time:

    $Serv = Get-WmiObject win32_service
    

    Let's see what we got in $Serv

    $Serv.GetType()
    

    This shows that we got an array
    Let's look at its first element:

    $Serv[0].GetType()
    

    This shows that each element in the array is an object (ManagementObject)
    Let's look at its properties:

    $Serv[0] | Get-Member -MemberType Properties
    

    Let's look at how the data looks like in the first array element:

    $Serv[0] | select *
    

    I can then tell that the information I'm looking for is right there:
    ComputerName (that I want) is already there under the label 'SystemName'
    Service (that I want) is already there under the label 'Name'
    ServiceAccount (that I want) is already there under the label 'StartName'

    So, I opted to output the information with the current labels, like this one-liner:

    Get-WmiObject win32_service | Select SystemName,Name,StartName | sort Name | FT -a 
    

    Now, if you're not happy with the labels, or you must return them as ComputerName, Service, ServiceAccount, we can do:

    Get-WmiObject win32_service | Select @{
        Name='ComputerName';  Expression={$_.SystemName}}, @{
        Name='Service';       Expression={$_.Name}}, @{
        Name='ServiceAccount';Expression={$_.StartName}} | sort Name | FT -a 
    

    In this one-liner, the returned objects contains the same information, except that the 3 properties have been renamed as desired.

    Alternatively, you can use a hash to recreate the desired object as in:

    $Output = @()
    Get-WmiObject win32_service | % {
        $Props = [ordered]@{
            ComputerName   = $_.SystemName
            Service        = $_.Name
            ServiceAccount = $_.StartName
        }
        $Output += New-Object -TypeName psobject -Property $Props
    }
    $Output | sort Name | FT -a 
    

    So, the script above can be updated like:

    # Get the list of servers from Active Directory
    $Servers = Get-ADComputer -Filter { OperatingSystem -Like 'Windows *Server*' } -Properties *
     
    # Check each server to make sure it is up.
    $LiveComputers = @()
    Write-Host 'Checking which computers are online' -NoNewline
    $Servers | % {
        Write-Host '.' -NoNewline
        if (Test-Connection -Count 1 -ComputerName $_.Name -EA 0) { $LiveComputers += $_.Name }
    }
    Write-Host '.'
     
    # Find any service that is using a non-standard account
    $Services = @()
    $LiveComputers | % {
        $Services += Get-WmiObject win32_service -ComputerName $_ | 
            where { $_.StartName -notmatch 'nt authority' -and $_.StartName -notmatch 'LocalSystem' } |
                Select @{   Name='ComputerName';  Expression={$_.SystemName}}, @{
                            Name='Service';       Expression={$_.Name}}, @{
                            Name='ServiceAccount';Expression={$_.StartName}} | sort Name
    }
    $Services | FT -a 
     
    # Export to CSV
    $Services | Export-Csv .\ServiceAccounts.csv -Append -NoTypeInformation
    
  • #22083
    Profile photo of Rocky Cabral
    Rocky Cabral
    Participant

    Sam, thank you for the in depth explanation. I really appreciate it. Still learning Powershell as I go...

You must be logged in to reply to this topic.