Ping Sweep of Active Directory with Results in SQL

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()
}

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

@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.

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}
}

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

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.

Can you share the code you have now?

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
           }
      #  }
    }
}

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!

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

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()

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.

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)

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()

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

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 :slight_smile: 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{}
 }
}

@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.

Are you writing your own scom alternative?

http://www.networksteve.com/?p=4846

Jamie, tell me what I’m doing wrong ? it just works :slight_smile: (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

@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