Ping Sweep of Active Directory with Results in SQL

This topic contains 20 replies, has 6 voices, and was last updated by Profile photo of Peter Jurgens Peter Jurgens 1 year, 2 months ago.

  • Author
    Posts
  • #29475
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    I am working on a PowerShell script that will query AD and then place the results of the Test-Connection cmdlet into SQL. It works but the time it takes in order to run it across all of the environment is way to long, especially since I would like for this to run on an interval of four hours. With thousands of computer objects I need something more efficient. I have tried a couple of different of methods using a START-JOB and even Invoke-command (which doesn't help since the machine is powered off.) Any guidance is appreciated.

    #Created: 09.04.15 by Me
    #Function: This script is used to ping computer accounts in Active Directory
    
    
    Measure-Command{
    $PCList=get-adcomputer -searchbase "DC=DOMAINNAME,DC=SUFFIX" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;uid=ACCOUNTNAME;pwd=PASSWORD;pooling=true;"
    $conn.open()
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    
    $ArrComputers =  $PCList
    
    
    
    foreach ($Computer in $ArrComputers) {
    
    	
            If (Test-Connection -ComputerName $Computer -count 1 -quiet) 
            {
                $Msg = "Ping successful!"
            }
            ELSE
            {
                $Msg = "OFFLINE!"
            }
    
    	$cmd.commandtext = "INSERT INTO DB TABLE (HostName,Status,DateRetrieved)
    	VALUES('{0}','{1}',getdate())" -f
    	$Computer,$Msg
    	$cmd.executenonquery()
    	}
    $conn.close()
    }
    
    
  • #29490
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Look into using a Workflow, you can run your pings in parallel.

    http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/26/powershell-workflows-the-basics.aspx

    You should also consider using the "trottlelimit" property if you do this.

    http://www.happysysadm.com/2013/06/how-to-throttle-workflow-activites-in.html

  • #29498
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    @Curtis Smith
    Thanks for that suggestion but it appears that WorkFlow doesn't like how I call the method of connecting to the SQL server without it being an InlineScript. I'm not sure how to make that work since I need it to record each transaction into SQL after it pings or not.

  • #29517
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    This is expected as PowerShell Workflows are built on the .NET Windows Workflow Foundation. You need to look at commands in a workflow as 'activities' and not scripted methods like in a PowerShell function. One simple option would be to encapsulate your entire command into an inline script inside of a ForEach -Parallel, but my personal suggestion would be to look into PowerShell Runspaces.

    One helpful module is the RSJob module created by Boe Prox

    Sorry I've not personally used the RSJob but you should be able to do something like this:

    $PCList=get-adcomputer -searchbase "DC=DOMAINNAME,DC=SUFFIX" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    ForEach($PC in $PCList){
    Start-RSJob -Name $PC -ScriptBlock {Do-Something $PC}
    }

  • #29523
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    You can test-connection in parallel and save results to db after it
    like following pseudocode

    workflow test {
    param(
      $computers
    )
      foreach -parallel ($computer in $computers) {
        test-connection -computername $computer
      }
    }
    function savetodb {
    param(
      [Parameter(ValueFromPipeline)]
      $data
    )
    PROCESS {
      Save-ValueToDB $data
    }
    }
    $computers = [...]
    test -computers $computers | savetodb
    
  • #29539
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    I am unable to get it to pass the parameters of my machine list. I tried the InLineScript but it $Computer is null. Right now it is just inputting blank records.

  • #29584
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    Can you share the code you have now?

  • #29741
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    This works from a foreach standpoint but I had to remove the InLineScript. I couldnt get it to work with it but really I didn't need it in this case but I will need it for the SQL.

    $PCList=get-adcomputer -searchbase "OU=Something,OU=Something,DC=Something,DC=Something" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    
    WorkFlow PingAD {
     param(
        [string[]]$Computers
        )
        
    
        foreach -parallel($Device in $Computers) {
            #InLineScript {
               if (Test-Connection -ComputerName $Device -Count 1 -Quiet -errorAction SilentlyContinue) {
               $msg = " online"
               #Write-host $Device, $Msg
               $Device, $Msg
               }
               Else {
               $msg = " NOT ONLINE!"
               #Write-host $Device, $Msg
               $Device, $Msg
               }
          #  }
        }
    }
    
  • #29744
    Profile photo of Warren Frame
    Warren Frame
    Participant

    Hi Jamie!

    Workflows are great for certain scenarios, but might not be the best fit here.

    You might consider looking at existing tools like Boe Prox's fantastic PoshRSJob to run everything in parallel (Max suggested this above).

    If you want an even-more-purpose-built solution, I borrowed work from Boe and a few others and cobbled together Invoke-Ping, which you can use to test connectivity very quickly.

    Cheers!

  • #29745
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    I think its possible to use it here but something is missing with the InLineScript that I am not doing correctly. I found another blog where someone used it.
    http://blogs.technet.com/b/heyscriptingguy/archive/2012/11/20/use-powershell-workflow-to-ping-computers-in-parallel.aspx

  • #29747
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    This is the latest thing I tried today. It ran through and ping'd every device but the value it put in the DB was one row that was empty aside from the current date.
    This is along the lines of what Max suggested.

    $PCList=get-adcomputer -searchbase "OU=Something,OU=Something,DC=Something,DC=Something" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    
    WorkFlow PingAD {
        param(
        [string[]]$Computers
        )
    
           foreach -parallel($Device in $Computers) {
                If (Test-Connection -ComputerName $Device -count 1 -quiet) 
                {
                    $Msg = "Ping Successful!"
                }
                ELSE
                {
                    $Msg = "OFFLINE!"
                }
                $Device, $Msg
            }        
    }
    
    Function OpenDB {
    param(
      [Parameter(ValueFromPipeline)]
      $data
    )
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=DBServer;Initial Catalog=DB;uid=DBUser;pwd=DBPassword;pooling=true;"
    $conn.open()
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    $cmd.commandtext = "INSERT INTO X_Ping (HostName,Status,DateRetrieved)
    VALUES('{0}','{1}',getdate())" -f
    $Device,$Msg
    $cmd.executenonquery()
    }
    
    PingAD -computers $PCList | OpenDB
    
    $conn.close()
    
  • #29748
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    The main problem is that $Device and $Msg are defined within the scope of their respective functions. Which means $Device and $Msg from PingAD function are different from the $Device and $Msg which exist in the OpenDB function.

    The way I would handle this is in your PingAD function, create a new PSObject with two named properties "Device" and "Msg" and have that as your output. Then create parameters with the same name in the OpenDB function and change the "valuefrompipeline" to "valuefrompipelinebypropertyname". This will be the simplest solution I believe.

    What will happen is when you run PingAD, it will output a collection of PSObjects with properties "Device" and "Msg". Passing each object down the pipeline, the "valuefrompipelinebypropertyname" parameter attribute will automatically bind the properties of the object in the pipeline to the parameters of the next function in the pipeline.

  • #29751
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    Jamie, In my pseudocode workflow return object- result of test-connection, and in your – array of device name and msg. Its a different thing.
    Your variant of workflow return two objects instead of one for every host you test.
    And as already said Peter, in current realization OpenDB function await only one object – $data, but you try to use $device and $msg instead of it
    You can change function parameters to two with [valuefrompipelinebypropertyname]
    or just use $data as object.

    if you change workflow returning values to object
    $Device, $Msg
    to [PSCustomObject]@{Device=$Device; Message=$Msg}
    and properly use this object in OpenDB as

    $cmd.commandtext = "INSERT INTO X_Ping (HostName,Status,DateRetrieved)
    VALUES('{0}','{1}',getdate())" -f
    $data.Device,$data.Message

    you probably get what you want.

    But I strongly recommend you to change your SQL INSERT to proper sql parameters usage to avoid sql injections.
    Something like

    $cmd.commandtext = "INSERT INTO X_Ping (HostName,Status,DateRetrieved)
    VALUES(@HostName,@Status',getdate())"
    $cmd.Parameters.AddWithValues('@HostName',$data.Device)
    $cmd.Parameters.AddWithValues('@Status',$data.Message)

  • #29778
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    Still no dice. Only a blank record gets entered in.

    #Created: 09.04.15 by Us
    #Function: This script is used to ping computer accounts in Active Directory and store in a SQL DB
    
    $PCList=get-adcomputer -searchbase "OU=Something,OU=Something,DC=Something,DC=Something" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    
    WorkFlow PingAD {
        param(
        [string[]]$Computers
        )
    
           foreach -parallel($Device in $Computers) {
                If (Test-Connection -ComputerName $Device -count 1 -quiet) 
                {
                    $Msg = "Ping Successful!"
                }
                ELSE
                {
                    $Msg = "OFFLINE!"
                }
                $Data = New-Object PSObject -property @{Device=$Device; Msg=$Msg}
            }        
    }
    
    Function OpenDB {
    param(
      [Parameter(ValueFromPipelineByPropertyName)]
      $Device, $Msg
    )
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=DBServer;Initial Catalog=DB:uid=DBUser;pwd=DBPassword;pooling=true;"
    $conn.open()
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    $cmd.commandtext = "INSERT INTO X_Ping (HostName,Status,DateRetrieved)
    VALUES(@HostName,@Status',getdate())"
    $cmd.Parameters.AddWithValues('@HostName',$Device)
    $cmd.Parameters.AddWithValues('@Status',$Msg)
    $cmd.executenonquery()
    }
    
    PingAD -computers $PCList | OpenDB
    
    $conn.close()
    
  • #29790
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Jamie, you are not returning your object from your PingAD Function. You are placing that object in a variable called $Data, and then doing nothing with it.

    $Data = New-Object PSObject -property @{Device=$Device; Msg=$Msg}
    

    To return it to standard output, just remove the $Data =

    New-Object PSObject -property @{Device=$Device; Msg=$Msg}
    

    Or output your variable after setting it.

    $Data = New-Object PSObject -property @{Device=$Device; Msg=$Msg}
    $Data
    
  • #29796
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    well, in my example I do not write "$data = ...." and Peter do not write such things.. but you write it

    Jamie, if you use functions and workflows, forget about global visibility of variables, just forget it forever 🙂 each variable is unique for each code block
    for example

    $a=1
    $a
    function a1{
      $a=2
      $a
    }
    function a2{
      $a = 3
      $a
    }
    a1
    a2
    $a
    

    which result you await ?
    1 2 3 3 ?
    NO !, you get
    1 2 3 1

    ...and

    attribute [Parameter(ValueFromPipelineByPropertyName)]
    is need for all parameters, not one

    param(
      [Parameter(ValueFromPipelineByPropertyName)]
      $Device,
      [Parameter(ValueFromPipelineByPropertyName)]
      $Msg
    )
    

    ....and as I already said earlier

    if you use pipeline, you MUST code it with PROCESS{} block

    Function OpenDB {
     param(
      [Parameter(ValueFromPipelineByPropertyName)]
      $Device,
      [Parameter(ValueFromPipelineByPropertyName)]
      $Msg
     )
     PROCESS {
      # all of your code for function MUST be there because pipeline processed thru PROCESS{}
     }
    }
    
  • #29819
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    @Peter and @Max: I appreciate you both for your assistance but it just doesn't work with what you guys are suggesting.
    I think we are pretty close but some other part is missing.

    @Peter: I have to assign a variable to "New-Object..." or the script will not even compile. It PowerShell ISE tells me that the "Output must be assigned a variable."
    I experimented leaving that entire line out and just having $Device, $Msg but it inputs it into my DB table wrong. I tried it with just one computer and it put the computer name in there for hostname and status and then put another row with the status listed for hostname and status. So for one computer that got check my table looked like this;
    computername,computername,09-16-2015
    ping successful!,ping successful!,09-16-2015

    @Max: I had noticed neither of you put $data but the ISE told me I had to assign a OUTPUT variable or it wouldn't even compile.
    Same thing with the Parameter in the OpenDB function. If I just left it to ValueFromPipelineByPropertyName I get this message;
    OpenDB : The input object cannot be bound to any parameters for the command either because the command
    does not take pipeline input or the input and its properties do not match any of the parameters that
    take pipeline input.
    If I add ValueFromPipeline then I do not get that message.
    I did leave off the Process block but even with adding it the results haven't changed much.

  • #29825
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Are you writing your own scom alternative?

  • #29849
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    Jamie, tell me what I'm doing wrong ? it just works 🙂 (I do not test sql part)

    $PCList=get-adcomputer -searchbase "OU=WORKSTATIONS,OU=SERVICE,DC=domain,DC=local" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    
    WorkFlow PingAD {
        param(
        [string[]]$Computers
        )
    
           foreach -parallel($Device in $Computers) {
                If (Test-Connection -ComputerName $Device -count 1 -quiet) 
                {
                    $Msg = "Ping Successful!"
                }
                ELSE
                {
                    $Msg = "OFFLINE!"
                }
                $Data = New-Object PSObject -property @{Device=$Device; Msg=$Msg}
    			$Data
            }        
    }
    
    Function OpenDB {
    param(
      [Parameter(ValueFromPipelineByPropertyName)]
      $Device,
      [Parameter(ValueFromPipelineByPropertyName)]
      $Msg
    )
    PROCESS {
    		
    		'Input: {0}, {1}, {2}' -f $Device, $Msg, (Get-Date)
    }
    }
    
    PingAD -computers $PCList | OpenDB
    

    and Output:

    Input: LDHOST-1, Ping Successful!, 17.09.2015 9:52:10
    Input: LD-HOST, Ping Successful!, 17.09.2015 9:52:10
    Input: RATING7, Ping Successful!, 17.09.2015 9:52:10
    Input: SEVEN-7, Ping Successful!, 17.09.2015 9:52:10
    Input: NETOP, Ping Successful!, 17.09.2015 9:52:10
    Input: DEPX140S10, OFFLINE!, 17.09.2015 9:52:12
    Input: DEPX140S9, OFFLINE!, 17.09.2015 9:52:12
    Input: RATING, OFFLINE!, 17.09.2015 9:52:12
    Input: LDHOST-2, OFFLINE!, 17.09.2015 9:52:13

    Yes, I forget that in workflows values need to be assigned to variables, but Curtis told about solution.
    and the rest is right parameter attributes and PROCESS{}
    that's all

  • #29886
    Profile photo of Jamie Davenport
    Jamie Davenport
    Participant

    @Max: Thanks for your last reply but I did not get to try it out to see if it works. I did get it however to work the way I wanted to.

    @Everyone Else: Thanks for posting to help me out; especially Max and Peter because I got it to work based of some things you guys suggested.

    There was one weird thing in which on the script it returned a "1" after inputing it into the SQL table but that's ok I can live with that.

    For anyone who wants to reuse this code feel free. I will say that it went from running 6 hours to 45 mins across over 16K computer accounts.
    You may also see that I had it write to a text file as well. That's for archiving purposes because I plan on deleting some rows from the SQL table and probably only keeping the last 30 days or something.

    Here is the code hope it helps someone else.

    #Created: 09.15.15 by Us
    #Function: This script is used to ping computer accounts in Active Directory
    
    #Query AD for workstations. Wasn't focusing on servers at the moment.
    $PCList=get-adcomputer -searchbase "DC=Something,DC=Something" -Filter {OperatingSystem -notlike "*server*"} | select -expand Name
    
    #Test using individual computers with this line before using AD
    # $PCList = "COMPUTER1", "COMPUTER2","COMPUTER3" 
    
    #Workflow block
    WorkFlow PingAD {
        param(
        [string[]]$Computers
        #If you were going to use a Throttle command you would need this
        # [int]$ThrottleLimit=20
        )
    
           #foreach -parallel ($Device in $Computers) {
           foreach -parallel ($Device in $Computers) {
                If (Test-Connection -ComputerName $Device -count 1 -quiet) 
                {
                    $IP = Test-Connection -ComputerName $Device -count 1 | Select -expandProperty IPV4Address
                    $IP = $IP.IPAddressToString
                    $Msg = "Ping Successful!"
                }
                ELSE
                {
                    $IP= "N/A"
                    $Msg = "OFFLINE!"
                }
                $data = New-Object PSObject -property @{Device=$Device; Msg=$Msg; IP=$IP}
                $data
            }        
    }
    
    #Function for writing it to the DB.
    Function OpenDB {
    Param(
    [Parameter(ValueFromPipeline=$True)]$Data
    #[Parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]$Device, 
    #[Parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]$Msg
    )
        PROCESS{
            $Device = $Data.Device
            $Msg = $Data.Msg
            $IP = $Data.IP
            $conn = New-Object System.Data.SqlClient.SqlConnection
            $conn.ConnectionString = "Data Source=DBServer;Initial Catalog=DB;uid=DBUser;pwd=DBPassword;"
            $conn.open()
            $cmd = New-Object System.Data.SqlClient.SqlCommand
            $cmd.connection = $conn
            $cmd.commandtext = "INSERT INTO X_Ping (HostName,Status,IPV4Address,DateRetrieved)
            VALUES('{0}','{1}','{2}',getdate())" -f
            $Device,$Msg,$IP
            $cmd.executenonquery()
            $date = get-date
            $Device + "," + $Msg + "," + $IP + "," + $date | out-file "C:\PingResults\PingResults.txt" -append
            $conn.close()
        }
        
    }
    
    PingAD -computers $PCList | OpenDB
    
    
    
  • #29887
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

    The executenonquery method of the sqlcommand class returns an int value of the number of rows affected. You could pipe the results of that command to out-null to eliminate this output. Just add | out-null after the executenonquery command.

You must be logged in to reply to this topic.