locked
Import CSV, Edit Data, Export CSV RRS feed

  • Question

  • Hello,

    I am a powershell novice and have been given a task to complete. I need to scan through AD, find all of the servers that are supposed to be active, "ping" them and, if active, get their IP address and verify that a number of services are running. Once all of the data is collected, I need to write all of this out to a .CSV file. So far, what I have is this:

    1. Using Import-Module ActiveDirectory, I am able to get the full list of servers with appropriate information and export that to a CSV
    2. I import the CSV file above back in and create new headers (columns) for more information
    3. The full list of servers are pinged individually. If pingable, I get the IP address and service information needed

    Where I am stuck is writing all of this back out to a .CSV file. Right now, I am just writing the information back out to the screen for verification. I'm sure it's something small, but being a novice I'm not sure where to go. Any help?

    • Moved by Bill_Stewart Friday, July 4, 2014 1:56 AM Abandoned
    Thursday, February 27, 2014 1:05 PM

Answers

  • Here is a simpler version of your version getter:

                     $svc=gwmi -computername $hash.Name -class Win32_Service -Filter "Name='$($hash.AVName)'"
                     $hash.ServicePath='\\'+$hash.Name+'\'+$svc.PathName.Replace(':','$').Replace('"','')
                     $hash.AVVer =([IO.FileINfo]($hash.ServicePath)).VersionInfo.ProductVersion


    ¯\_(ツ)_/¯

    • Marked as answer by Eric Welshons Wednesday, August 9, 2017 4:03 PM
    Thursday, February 27, 2014 8:42 PM

All replies

  • Please post your script.  What is the issue with writing it out?

    Get-AdComputer |
         %{
                if(Test-Computer $_.Name -count 1 -Quiet){
                    $_
                }
    } |  Select name |
         Export-Csv -Properties Name


    ¯\_(ツ)_/¯

    Thursday, February 27, 2014 2:18 PM
  • Here is the full code. The Write-Host statements are just for me during the testing to see if the information is correct. The last statement was what I tried to write the information out, but it came up empty and I really haven't found a way to get it to work.

    Import-Module ActiveDirectory    

    Get-ADComputer -LDAPFilter "(OperatingSystem=*Server*)" -Property * |
        Select-Object Name,Created,LastLogonDate,OperatingSystem,OperatingSystemServicePack,OperatingSystemVersion,CanonicalName |
        ? {$_.CanonicalName -notlike "*Archived Servers*" -and $_.CanonicalName -notlike "*Sun Prod*"} |
        Export-CSV "c:\temp\AllADComputers4.CSV" -NoTypeInformation -Encoding UTF8
        
    #Add other headers
    $csv = get-content "c:\temp\AllADComputers4.CSV"
    $csv[0] += ",Pingable,IPAddress,SCCM Status,AV Name,AV Version,AV Status"
    $csv | out-file "c:\temp\AllADComputers4.CSV"

    $MachineInfo = Import-Csv -Path "c:\temp\AllADComputers4.CSV"
    $Processed = ForEach ($objitem in $MachineInfo)
    {
        If (Test-Connection -ComputerName $Objitem.Name -Quiet -Count 1)
        {
            If (Get-Service -name "Symantec AntiVirus" -computername $Objitem.Name)
            {
                $AVStatus = Get-Service -name "Symantec AntiVirus" -computername $Objitem.Name
                $AVName = "Symantec AntiVirus"
            }
            Elseif (Get-Service -name SepMasterService -computername $Objitem.Name)
            {
                $AVStatus = Get-Service -name SepMasterService -computername $Objitem.Name
                $AVName = SepMasterService
            }
            ElseIf (Get-Service -name "Norton AntiVirus Server" -computername $Objitem.Name)
            {
                $AVStatus = Get-Service -name "Norton AntiVirus Server"-computername $Objitem.Name
                $AVName = "Norton AntiVirus Server"
            }
            Else
            {
                $AVStatus = "Stopped"
                $AVName = "None"
            }
            $SCCMAgentStatus = Get-Service -name ccmexec -computername $Objitem.Name
            $Networks = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Objitem.Name | ? {$_.IPEnabled}
            $objitem.Pingable = "Yes"
            foreach ($Network in $Networks)
            {
                    $IPAddress  = $Network.IpAddress[0]
            }
            Write-Host $Objitem.Name,"...Success...",$IPAddress,"   SCCM Status = ",$SCCMAgentStatus.Status, "Anti-Virus = ",$AVName, "Status = ",$AVStatus.Status
            $service = gwmi -computername $objItem.Name -class Win32_Service | ? {$_.Name -eq $AVName}
            $path = ($service | Select -Expand PathName)
            $path = $path.Replace(":","$")
            $path = $path.Replace('"','')
            $FullPath = "\\"+$objitem.Name+"\"+$path
            $ver = Get-ItemProperty -Path $FullPath | select-object -expand VersionInfo | select-object -expand ProductVersion
            Write-Host $Fullpath
            Write-Host "Version = ",$ver

        }
        Else
        {
            Write-Host $Objitem.Name"...Failed"
            $objitem.Pingable = "No"
        }    
    }
    $Processed | export-csv -Path "c:\temp\AllADProcessed.CSV"

    Thursday, February 27, 2014 2:45 PM
  • This is what you are trying to do:

    Import-Module ActiveDirectory    
    $avNames='Symantec AntiVirus','SepMasterService','Norton AntiVirus Server'
    
    Get-ADComputer -LDAPFilter "(OperatingSystem=*Server*)" -Property * | 
        $hash=@{
            Name=$_.Name
            AVStatus='Stopped'
            AVName='None'
            Pingable=$false
            IPAddress=@()
        }
        ?(Test-Connection -ComputerName $_.Name -Quiet -Count 1){
            $hash=@{
                AVStatus='Stopped'
                AVName='None'
                Pingable=$true
                IPAddress=@()
    	    }
            if($svc=Get-Service -name $avNames -computername $_.Name){
                $hash.AVName=$svc.Name
                $hash.AVStatus=$svc.Status 
            }
    
            $adapters=Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $_.Name -Filter 'IPEnabled=True'
            foreach ($adapter in $adapters){
                 $hash.IPAddress+=$adapters.IpAddress
            }
        
            # fix the following to use the same method
            <#
            $SCCMAgentStatus=Get-Service -name ccmexec -computername $Objitem.Name
            Write-Host $Objitem.Name,"...Success...",$IPAddress,"   SCCM Status = ",$SCCMAgentStatus.Status, "Anti-Virus = ",$AVName, "Status = ",$AVStatus.Status
            $service = gwmi -computername $objItem.Name -class Win32_Service | ? {$_.Name -eq $AVName} 
            $path = ($service | Select -Expand PathName)
            $path = $path.Replace(":","$")
            $path = $path.Replace('"','')
            $FullPath = "\\"+$objitem.Name+"\"+$path
            $ver = Get-ItemProperty -Path $FullPath | select-object -expand VersionInfo | select-object -expand ProductVersion
            Write-Host $Fullpath
            Write-Host "Version = ",$ver
            #>
    
         }
         New-Object PsObject -Property $hash
    } | Export-Csv c:\temp\AllADProcessed.CSV -NoTypeInformation
    
    



    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, February 27, 2014 3:22 PM
    Thursday, February 27, 2014 3:19 PM
  • Thanks for that!

    I was going to modify the remaining code after testing this bit. When I try running, I get the following error:

    At C:\Users\userguy\AppData\Local\Temp\b6965999-e52c-4a14-b4cb-55bf695ff55b.ps1:6 char:2
    +     $hash=@{
    +     ~~~~~
    Expressions are only allowed as the first element of a pipeline.

    At C:\Users\userguy\AppData\Local\Temp\b6965999-e52c-4a14-b4cb-55bf695ff55b.ps1:55 char:1
    + } | Export-Csv c:\temp\AllADProcessed.CSV -NoTypeInformation
    + ~
    Unexpected token '}' in expression or statement.

    At C:\Users\userguy\AppData\Local\Temp\b6965999-e52c-4a14-b4cb-55bf695ff55b.ps1:55 char:3
    + } | Export-Csv c:\temp\AllADProcessed.CSV -NoTypeInformation
    +   ~
    An empty pipe element is not allowed.


        + CategoryInfo          : ParserError: (:) [], ParseException
        + FullyQualifiedErrorId : ExpressionsMustBeFirstInPipeline

    Thursday, February 27, 2014 3:52 PM
  • I am giving you a template for what you are trying to do.  I cannot test it because I do not have your system. 

    If you are not reasonably good with PowerShell then this may be too difficult for you.

    Start wit a very simple part and build up until you understand.

    Import-Module ActiveDirectory    
    $avNames='Symantec AntiVirus','SepMasterService','Norton AntiVirus Server'
    Get-ADComputer -LDAPFilter "(OperatingSystem=*Server*)" -Property * | 
        ForEach-Object{
            $hash=@{
                Name=$_.Name
                AVStatus='Stopped'
                AVName='None'
                Pingable=$false
                IPAddress=@()
            }
            if(Test-Connection -ComputerName $_.Name -Quiet -Count 1){
                if($svc=Get-Service -name $avNames -computername $_.Name -ea 0){
                    $hash.AVName=$svc.Name
                    $hash.AVStatus=$svc.Status 
                }
             }
             New-Object PsObject -Property $hash
        } 
    
    

    Once you understand how this works you can expand it.


    ¯\_(ツ)_/¯

    Thursday, February 27, 2014 4:01 PM
  • Thank you for the help! I got it to work for the most part. The only thing that isn't happening is that one item is not being written to the .CSV file. The code:

    Import-Module ActiveDirectory    
    $avNames='Symantec AntiVirus','SepMasterService','Norton AntiVirus Server'
    Get-ADComputer -LDAPFilter "(OperatingSystem=*Server*)" -Property * |
        ForEach-Object{
            $hash=@{
                Name=$_.Name
                AVStatus='Stopped'
                AVName='None'
                Pingable=$false
                IPAddress=@()
                OS=$_.OperatingSystem
                OSSP=$_.OperatingSystemServicePack
                OSVer=$_.OperatingSystemVersion
                Created=$_.Created
                LastOn=$_.LastLogonDate
                FullName=$_.CanonicalName
                SCCMAgentStatus='Stopped'
            }
            if(Test-Connection -ComputerName $_.Name -Quiet -Count 1){
                $hash.Pingable=$true
                if($svc=Get-Service -name $avNames -computername $_.Name -ea 0){
                    $hash.AVName=$svc.Name
                    $hash.AVStatus=$svc.Status
                    $service = gwmi -computername $hash.Name -class Win32_Service | where-object {$_.Name -eq $AVName}
                    $path = ($service | Select -Expand PathName)
                    $path = $path.Replace(":","$")
                    $path = $path.Replace('"','')
                    $FullPath = "\\"+$hash.Name+"\"+$path
                    $hash.AVVer = Get-ItemProperty -Path $FullPath | select-object -expand VersionInfo | select-object -expand ProductVersion
                }
                if($sccm=Get-Service -Name ccmexec -computername $_.Name -ea 0){
                    $hash.SCCMAgentStatus='Running'
                }
             }
             New-Object PsObject -Property $hash
        }  | Export-Csv c:\temp\AllADProcessed.CSV -NoTypeInformation

    The value for AVVer is not getting written to the file. However, if I comment out everything after the final curly bracket, AVVer will be displayed on the screen for the output. Any ideas on that?

    Thursday, February 27, 2014 7:52 PM
  • You really do need a tutorial on PowerShell.  You are making things very difficult for yourself and creating scripts that will be very slow.


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, February 27, 2014 8:12 PM
    Thursday, February 27, 2014 8:10 PM
  • Here is a simpler version of your version getter:

                     $svc=gwmi -computername $hash.Name -class Win32_Service -Filter "Name='$($hash.AVName)'"
                     $hash.ServicePath='\\'+$hash.Name+'\'+$svc.PathName.Replace(':','$').Replace('"','')
                     $hash.AVVer =([IO.FileINfo]($hash.ServicePath)).VersionInfo.ProductVersion


    ¯\_(ツ)_/¯

    • Marked as answer by Eric Welshons Wednesday, August 9, 2017 4:03 PM
    Thursday, February 27, 2014 8:42 PM
  • This should be like this:

                 if($sccm=Get-Service -Name ccmexec -computername $_.Name -ea 0){
                     $hash.SCCMAgentStatus=$sccm.Status
                 }


    ¯\_(ツ)_/¯

    Thursday, February 27, 2014 8:44 PM
  • Try to keep thing consistent.  There is actually a much easier way to do this.  I will leave you to discover that from the literature as you go forward.


    ¯\_(ツ)_/¯

    Thursday, February 27, 2014 8:46 PM