none
Having Problems with Importing / Exporting to a CSV RRS feed

  • Question

  • I am in need of a Powershell script that will check a list of machines for 2 specific values in an XML file.  I am not sure where I am going wrong ( a bit new to Powershell) and could use an extra set of eyes on my code.

    Code:

    ## Import data from CSV
    $ImportFile = Import-Csv "C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv"
    foreach ($Computer in $ImportFile){
    ## Set the location of the XML file on the Remote Machines
    $path = "\\$Computer\c$\Epic\bin\7.9.2\Epic Print Service\"
    ## Select XML node and determine if ArchiveHours = 12 and DeleteHours =120
    $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
        if ($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120') {
        $Compliance = $True
        }Else{
        $Compliance = $False
        }
    ##Computer and Complaince (True or False) out to CSV file
    } "$Computer","$Compliance" | Export-Csv "C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv"


    Results:

    select-xml : Cannot find path '\\@{SW0908-2364=SW1409-15995}\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml' because it does not exist.
    At C:\Users\Username\Desktop\Scripts\Powershell\Epic\SCCM CI\Check_PullServiceXML.ps1:4 char:8
    + $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullServ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\@{SW0908-2364...vice.config.xml:String) [Select-Xml], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SelectXmlCommand
     
    select-xml : Cannot find path '\\@{SW0908-2364=SW1409-16373}\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml' because it does not exist.
    At C:\Users\Username\Desktop\Scripts\Powershell\Epic\SCCM CI\Check_PullServiceXML.ps1:4 char:8
    + $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullServ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\@{SW0908-2364...vice.config.xml:String) [Select-Xml], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SelectXmlCommand
     
    select-xml : Cannot find path '\\@{SW0908-2364=SW1412-16985}\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml' because it does not exist.
    At C:\Users\Username\Desktop\Scripts\Powershell\Epic\SCCM CI\Check_PullServiceXML.ps1:4 char:8
    + $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullServ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\@{SW0908-2364...vice.config.xml:String) [Select-Xml], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SelectXmlCommand

    Wednesday, September 17, 2014 2:54 PM

Answers

  • function Check-Compliance{
        Param(
            $computer=$env:COMPUTERNAME,
            $filelocation='c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml'
        )
        
        Begin{}
        
        Process{
            
            $p=@{
                Computer=$computer
                IsAlive=$false
                Compliance=$false
                IsFound=$false
            }
            
            If(Test-Connection $computer -quiet -count 1){
                $p.IsAlive=$true
                $path='\\{0}{1}' -f $computer, $filelocation
                if(Test-Path $path){
                    $p.IsFound=$true
                    $xml= select-xml -path $path -xpath '//EpicPullService//Cleanup' | Select -ExpandProperty Node
                    $p.Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
                }
            }
            New-Object PsObject -Property $p
        }
        
        End{}
    }
    
    $computers | %{Check-Compliance -computer $_ }


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, September 17, 2014 7:23 PM
    • Proposed as answer by Mike Laughlin Wednesday, September 17, 2014 11:47 PM
    • Marked as answer by ch4db Thursday, September 18, 2014 1:40 AM
    Wednesday, September 17, 2014 7:23 PM

All replies

  • What is the column title from your CSV file? You need to specify it in the variable, if the column is titled "Name" you would need to do this:

    ## Import data from CSV
    $ImportFile = Import-Csv "C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv"
    foreach ($Computer in $ImportFile){
    ## Set the location of the XML file on the Remote Machines
    $path = "\\$Computer.Name\c$\Epic\bin\7.9.2\Epic Print Service\"
    ## Select XML node and determine if ArchiveHours = 12 and DeleteHours =120
    $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
        if ($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120') {
        $Compliance = $True
        }Else{
        $Compliance = $False
        }
    ##Computer and Complaince (True or False) out to CSV file

    Wednesday, September 17, 2014 3:18 PM
  • Added header to CSV and to Code

    $ImportFile = Import-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv" -Header Computer
    foreach ($Computer in $ImportFile){
    $path = "\\$Computer\c$\Epic\bin\7.9.2\Epic Print Service"
    $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
        if ($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120') {
        $Compliance = $True
        }Else{
        $Compliance = $False
        }
    } "$Computer","$Compliance" | Export-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv"

    Results:

    select-xml : Cannot find path '\\@{Computer=SW1412-16985}\c$\Epic\bin\7.9.2\Epic Print Service\EpicPullService.config.xml' because it does not exist.
    At C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Check_PullServiceXML.ps1:4 char:8
    + $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullServ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\@{Computer=SW...vice.config.xml:String) [Select-Xml], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SelectXmlCommand



    • Edited by ch4db Wednesday, September 17, 2014 3:42 PM Added image of XML on remote machine
    Wednesday, September 17, 2014 3:34 PM
  • This is more likely to work if you use the correct column name:

    Import-Csv C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv |
        ForEach-Object{
            $computer=$_.computername
            $path ="\\$computer\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml"
            $xml= select-xml -path $path -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
            New-Object PsObject -Property @{
                    Computer=$computer
                    Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
    	        }
     } | 
    Export-Csv C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv
    
    

    Try to not overload you code with pointless comments like:

    #Export to a CSV file
    Export-Csv file.csv

    It is obvious that it is an export.

    foreach() cannot be piped.

    You don't need an "IF" wen you want the truthiness of an expression.  Just assign the expression.

    Its a computer.  Let is do the work like  a computer.


    ¯\_(ツ)_/¯

    • Marked as answer by ch4db Wednesday, September 17, 2014 3:58 PM
    • Unmarked as answer by ch4db Wednesday, September 17, 2014 6:46 PM
    • Proposed as answer by Mike Laughlin Wednesday, September 17, 2014 11:47 PM
    Wednesday, September 17, 2014 3:41 PM
  • Added header to CSV and to Code

    $ImportFile = Import-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv" -Header Computer
    foreach ($Computer in $ImportFile){
    $path = "\\$Computer\c$\Epic\bin\7.9.2\Epic Print Service"
    $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
        if ($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120') {
        $Compliance = $True
        }Else{
        $Compliance = $False
        }
    } "$Computer","$Compliance" | Export-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv"

    Results:

    select-xml : Cannot find path '\\@{Computer=SW1412-16985}\c$\Epic\bin\7.9.2\Epic Print Service\EpicPullService.config.xml' because it does not exist.
    At C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Check_PullServiceXML.ps1:4 char:8
    + $xml = select-xml -path "$path\EpicPullService.config.xml" -xpath //EpicPullServ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\@{Computer=SW...vice.config.xml:String) [Select-Xml], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SelectXmlCommand


    If it is not a CSV file then just get it with Get-Content

    Get-Content C:\Users\UserName\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.csv |
        ForEach-Object{
            $computer=$_
            $path ="\\$computer\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml"


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 3:44 PM
  • Made the changes

    Get-Content -path "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.txt" |
        ForEach-Object{
            $computer=$_
            $path ="\\$computer\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml"
            $xml= select-xml -path $path -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
            New-Object PsObject -Property @{
                    Computer=$computer
                    Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
    	        }
     } | 
    Export-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv" -NoTypeInformation
    Looks like it is working.  Thank you very much!

    Wednesday, September 17, 2014 3:58 PM
  • You are welcome.  Pay close attention to how we did that.  It is one of the hardest lessons to get new users to understand and it is critical.


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 4:06 PM
  • I have run into another hurdle.  The script works great on machines that have the XML file present but returns "True" for machines that do not have the file.
    Get-Content -path "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.txt" |
        ForEach-Object{
            $computer=$_
            $pingstatus =  Get-WmiObject Win32_PingStatus -Filter "Address = '$computer'" | Select-Object StatusCode
                If($pingstatus.StatusCode -eq 0){        
                    $path ="\\$computer\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml"
                    $xml= select-xml -path $path -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
                    New-Object PsObject -Property @{
                        Computer=$computer
                        Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
    	            }
                 }Else{    
                    New-Object PsObject -Property @{
                        Computer=$computer
                        Compliance="Offline"
                    }
                }      
     } | 
    Export-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv" -NoTypeInformation

    Wednesday, September 17, 2014 6:44 PM
  • That should not really be possible so something else is wrong with your design.


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 6:52 PM
  • You added a line. WhY?

           $pingstatus =  Get-WmiObject Win32_PingStatus -Filter "Address = '$computer'" | Select-Object StatusCode
     You should use Test-Connection


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 6:54 PM
  • function Check-Compliance{
        Param(
            $computer=$env:COMPUTERNAME,
            $filelocation='c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml'
        )
        
        Begin{}
        
        Process{
            
            $p=@{
                Computer=$computer
                IsAlive=$false
                Compliance=$false
                IsFound=$false
            }
            
            If(Test-Connection $computer -quiet -count 1){
                $p.IsAlive=$true
                $path='\\{0}{1}' -f $computer, $filelocation
                if(Test-Path $path){
                    $p.IsFound=$true
                    $xml= select-xml -path $path -xpath '//EpicPullService//Cleanup' | Select -ExpandProperty Node
                    $p.Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
                }
            }
            New-Object PsObject -Property $p
        }
        
        End{}
    }
    
    $computers | %{Check-Compliance -computer $_ }


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, September 17, 2014 7:23 PM
    • Proposed as answer by Mike Laughlin Wednesday, September 17, 2014 11:47 PM
    • Marked as answer by ch4db Thursday, September 18, 2014 1:40 AM
    Wednesday, September 17, 2014 7:23 PM
  • Finally got all of the code worked out.  Looks like i had some misplaced brackets.  Here is the final script.  It may not be pretty, but it works.  Thanks for the pointers.
    Get-Content -path "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Tags.txt" |
        ForEach-Object{
            $computer= $_
                If(Test-Connection -ComputerName $computer -Quiet -Count 1){        
                    $path ="\\$Computer\c$\Epic\bin\7.9.2\Epic Print Service\\EpicPullService.config.xml"
                        if (Test-Path $path)
                            {$xml= select-xml -path $path -xpath //EpicPullService//Cleanup | Select -ExpandProperty Node
                                    New-Object PsObject -Property @{
                                        Computer=$computer
                                        Compliance=($xml.ArchiveHours -eq '12' -and $xml.DeleteHours -eq '120')
    	                                }
                            }Else{
                            
                            New-Object PsObject -Property @{
                                Computer=$computer
                                Compliance="Missing"
                                }
                           }
               }Else{
                    New-Object PsObject -Property @{
                        Computer=$computer
                        Compliance="Offline"
                        }
                    } 
                            
     } | 
    Export-Csv "C:\Users\username\Desktop\Scripts\Powershell\Epic\SCCM CI\Results.csv" -NoTypeInformation




    • Edited by ch4db Wednesday, September 17, 2014 10:00 PM typo
    • Marked as answer by ch4db Wednesday, September 17, 2014 11:26 PM
    • Unmarked as answer by ch4db Thursday, September 18, 2014 1:40 AM
    Wednesday, September 17, 2014 9:57 PM
  • Wow! After all of that work you are claiming you solved the problem yourself.  Sheesh.

    Anyway.  Have fun.


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 11:38 PM
  • I was really just kidding but the last script I posted is actually the best solution of the bunch if you can understand why.

    Good luck.


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 11:50 PM
  • Sorry man.. You threw me with the hole function thing.  I was hoping to have my code dissected to see what I had done wrong... Ya know.. For a learning experience since I am fairly new to Powershell.  I appreciate the help very much.
    Thursday, September 18, 2014 1:47 AM
  • The point of teaching is to rattle cages.  Consider your "self" rattled.

    Yes - there is a hole in the function.  It has no robust error management.  It is just a raw example of how it might be done.

    You need to think structured...you need to learn what structure really is...

    Cheers .. .. .. ..


    ¯\_(ツ)_/¯

    Thursday, September 18, 2014 1:51 AM