zailleh/Import-ADSitesToLansweeper.ps1

## Import-ADSitesToLansweeper.ps1
<###############################################################################
	Import-ADSites
################################################################################
	DEPENDENCIES: 	Powershell Module - ActiveDirectory
					SQL Access to referenced SQL server and Database

	DESCRIPTION:	Adds new AD Sites to LANSweeper and schedules a scan of
					their IP subnets.

 	CHANGE LOG:
    2017-04-19:
		- Force $ScanRangesTable to Type ARRAY instead of DataTable.
		- Performs comparison to avoid updates to DB where no changes have been made.
		- Added Parameter "BuildTSQLOnly" which will output the required SQL rather
		than executing against the SQL server.
		- Fixed Scanning timing to properly separate across a week.
	2017-10-09: Fixed errors importing credentials and importing IP Scan Ranges.

################################################################################>
param(
    [Parameter(
        Mandatory=$false,
        HelpMessage="Optional parameter to build the TSQL queries only and not execute them."
    )][Switch]$BuildTSQLOnly,

    [Parameter(
        Mandatory=$true,
        HelpMessage="hostname and instance of SQL server , eg 'sqlserver\instance'. Mandatory.",
        Position=1
    )][string]$dbServer,

    [Parameter(
        Mandatory=$false,
        HelpMessage="Name of the Lansweeper Database on the server. Default is 'lansweeperdb'",
        Position=2
    )][string]$dbName = 'lansweeperdb'

)

#$BuildTSQLOnly = $TRUE

#Region Functions
Function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=True; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
}
#EndRegion

#Region Variables
#Static
$table = "dbo.tsysIPLocations"
$scanTable = "dbo.tsysIPScanRanges"
$results = @{}
$duplicates = @()

#Calculated
$ScriptName = $MyInvocation.MyCommand.Name
$OutputFile = "$PSScriptRoot\$ScriptName" -replace ".ps1","-SQLCommand.txt"
$searchBase = (Get-ADRootDSE).configurationNamingContext
$searchBase = "CN=Subnets,CN=Sites," + $searchBase
$subnets = Get-ADObject -Filter {ObjectClass -eq 'Subnet'} -SearchBase $searchBase -Properties siteObject | Select name,siteObject
#EndRegion

#region Format Subnets for adding to LANSweeper Database
ForEach ($subnet in $subnets) {

    #Get the Subnet Start and bit mask from the Subnet Name attribute
    [System.Collections.ArrayList]$octets = @()
    $octets = $subnet.name.substring(0,$subnet.name.indexOf("/")).split(".")
    $BitMask = $subnet.name.substring($subnet.name.indexOf("/")+1)
    $endOctets = $octets.Clone()

    #region Build the end address based on the bit mask and start address
    #Get which Octet we'll be operating in.
    if ($BitMask -lt 32) {
        $octetNo = [Math]::Floor([int]$BitMask / 8)
        #Get how many bits within this octet we need to mask
        $remainder = $BitMask % 8

        #convert value to binary
        $modOctet = [Convert]::ToString($endOctets[$octetNo],2)

        #make binary value 8 bits long
        While ($modOctet.Length -lt 8){
            $modOctet = "0" + $modOctet
        }

        #convert to char array and set each bit that is masked to "1"
        $modOctet = $modOctet.ToCharArray()
        For ($i = $remainder;$i -le 8;$i++) {
            $modOctet[$i-1] = "1"
        }

        #set the last bit in the array to 0 (eg, we'll end up with 254 instead of 255)
        #$modOctet[7] = "0"

        #convert back to decimal
        $modOctet = [string]::Join("",[String[]]$modOctet)
        $modOctet = [Convert]::ToInt32($modOctet,2)
        #if ($modOctet -eq 255){$modOctet = 255}

        #store back in the octet array
        try {
            $endOctets[$octetNo] = [string]$modOctet
        } catch {
            ECHO "CAUGHT!"
        }

        #if we're not in the last octet, set the last octet to 254
        While ($octetNo -lt 3) {
            $endOctets[++$octetNo] = "255"
        }
        $endOctets[3] = "254"
    }
    #endregion

    #set the first address to 1 if it's a 0 (10.0.0.0 isn't a valid address, 10.0.0.1 is)
    if ([int]$octets[3] -eq 0) {$octets[3] = "1"}

    #Finally, store our values to be used in the database
    $RealStart = [string]::Join(".",@($octets))
    $RealEnd = [string]::Join(".",@($EndOctets))

    #prefix each octet(except the first) with 0 if less than 3 chars (formatting for key in db)
    For ($i=1;$i-le 3;$i++) {
        while ($octets[$i].length -lt 3) {
            $octets[$i] = "0" + $octets[$i]
        }

        while ($endOctets[$i].length -lt 3) {
            $endOctets[$i] = "0" + $endOctets[$i]
        }
    }

    #Save the modified octets as strings
    $startIP = [string]::Join("",@($octets))
    $endIP = [string]::Join("",@($endOctets))

    #Pull out the name of the site
    $IPLocation = $subnet.siteObject.Substring(3,$subnet.siteObject.IndexOf(",CN")-3)

    #store in object
    $propertyhash = [ordered]@{
        StartIP = $startIP
        EndIP = $endIP
        IPLocation = $IPLocation
        RealStart = $RealStart
        RealEnd = $RealEnd
    }
	[array]$arrayresults += New-Object PSObject -Property $propertyhash
	try{$results.Add($startIP,(New-Object PSObject -Property $propertyhash))}
    catch {
        ECHO "Duplicate?"
        $duplicates += $results[$startIP]
        $duplicates += New-Object PSObject -Property $propertyhash
    }
}
#endregion

IF($duplicates){
    Write-Host "Duplicate Subnets found:"
    $duplicates
}

#region Update Location Table in LANSweeper Database
$getCurrentTable = @"
SELECT *
FROM $table
"@

#region IP Locations Statements
$AddToTable = @"
INSERT INTO $table (StartIP,EndIP,IPLocation,Realstart,Realend)
VALUES ('{0}','{1}','{2}','{3}','{4}');
"@

$UpdateTable = @"
UPDATE $table
SET EndIP={1},IPLocation='{2}',Realstart='{3}',Realend='{4}'
WHERE StartIP='{0}';
"@

$RemoveFromTable = @"
DELETE FROM $table
WHERE StartIP='{0}';
"@
#endregion

#regionIP Scan Range Statements
$AddToScanTable = @"
INSERT INTO $scanTable (ServerName,Ipstart,Ipend,Enabled,PingTimeout,Day1,Day2,Day3,Day4,Day5,Day6,Day7,Minutes)
VALUES ('sdcplansweeper','{0}','{1}','1','1','1','1','1','1','1','1','1','1');
"@

$UpdateScanTable = @"
UPDATE  $scanTable
SET IpEnd='{1}',PingTimeout=1
WHERE Ipstart='{0}';
"@

$RemoveFromScanTable = @"
DELETE FROM  $scanTable
WHERE Ipstart='{0}';
"@

$GetScanTable = @"
SELECT *
FROM $scanTable
"@
#endregion

#Get current values of LANSweeper IPLocation Table
$currentTable = (Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $getCurrentTable) | Select StartIP,EndIP,IPLocation,Realstart,Realend
$ipScanRangesTable = (Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $GetScanTable) | Select Ipstart
$IPScanRangeHash = @{}
ForEach ($row in $ipScanRangesTable)
{
	$IPScanRangeHash.Add($row.Ipstart,$true)
}
Write-Host "Importing to DB"
ForEach ($row in $currentTable) {

    if ($results.ContainsKey([string]$row.StartIP))
	{
        $data = $results[[string]$row.StartIP]

        IF(Compare ($row | Out-String) ($data | Out-String))
		{
            Write-Host $data.IPLocation": Data in AD differs from Lansweeper DB. Updating DB."
            #Update Location Table
            $sql = $UpdateTable -f $data.StartIP,$data.EndIP,$data.IPLocation,$data.Realstart,$data.Realend
            try{
                IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
                ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
            } catch {ECHO Caught}
            #Update Scan Range Table
            $scanSql = $UpdateScanTable -f $data.Realstart,$data.Realend
            Try{
                IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
                ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $scansql}
            } catch {ECHO Caught}
        }

		if (!$IPScanRangeHash.Contains($row.RealStart))
		{
			$data = $results[[string]$row.StartIP]
			$sql = $AddToScanTable -f $data.RealStart,$data.RealEnd
	        IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
	        ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

		}
		$results.Remove($data.StartIP)
	}
	else {
        <#
        Remove Row From Location Table
        $sql = $RemoveFromTable -f $row.StartIP
        IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
        ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
        #>

        <#Remove Row From Scan Range Table
        $scanSql = $RemoveFromScanTable -f $row.Realstart
        IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
        ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $scansql}
		#>
    }
}

if ($results.Values.Count -gt 0){
    Write-Host "Adding New: $($Results.Values.Count)"
    ForEach ($data in $results.Values){

        #Add Row to Location Table
        $sql = $AddToTable -f $data.StartIP,$data.EndIP,$data.IPLocation,$data.Realstart,$data.Realend
        IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
        ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

        $sql = $AddToScanTable -f $data.RealStart,$data.RealEnd
        IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
        ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
    }
}
#endregion

#region stagger start times across all IP ranges
[array]$ScanRangesTable = Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand "Select * From $ScanTable"
$MinutesInWeek = 7*24*60
$minutesPerRange = $minutesInWeek / ($scanRangesTable.count)

$UpdateScanRangeTime = @"
SET DATEFORMAT ymd
UPDATE $scanTable
SET Day1='{0}',Day2='{2}',Day3='{4}',Day4='{6}',Day5='{8}',Day6='{10}',Day7='{12}',
  Day1time='{1}',Day2time='{3}',Day3time='{5}',Day4time='{7}',Day5time='{9}',Day6time='{11}',Day7time='{13}'
WHERE Ipstart='{14}'
"@

$ScanTime = Get-Date -Day 1 -Month 1 -Year 1900 -Hour 0 -Minute 0 -second 0
$defaultDate = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";
ForEach ($range in $ScanRangesTable) {
	#Base Values
    $day1=0; $day2=0; $day3=0; $day4=0; $day5=0; $day6=0; $day7=0
    $day1Time=$defaultDate; $day2Time=$defaultDate; $day3Time=$defaultDate;$day4Time=$defaultDate;$day5Time=$defaultDate;$day6Time=$defaultDate;$day7Time=$defaultDate

	#Set Active Values
	$startIP = $range.IpStart
    Switch ($ScanTime.Day) {
        1 {$day1 = 1; $day1Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        2 {$day2 = 1; $day2Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        3 {$day3 = 1; $day3Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        4 {$day4 = 1; $day4Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        5 {$day5 = 1; $day5Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        6 {$day6 = 1; $day6Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        7 {$day7 = 1; $day7Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
        default {}
    }

    #Update Database
	$sql = $UpdateScanRangeTime -f $day1,$day1Time,$day2,$day2Time,$day3,$day3Time,$day4,$day4Time,$day5,$day5Time,$day6,$day6Time,$day7,$day7time,$startIP
    IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
    ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

	#Increment Scan time for next subnet.
	$ScanTime = $ScanTime.AddMinutes($minutesPerRange)
}
#endRegion
$ErrorActionPreference = "SilentlyContinue"
#Set credentials used for scanning
$sql = @"
DECLARE @iprangeid int
DECLARE iprange CURSOR FOR
Select IPRangeID FROM tsysIPScanRanges;

OPEN iprange;
FETCH NEXT FROM iprange
INTO @iprangeid

WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO
			tsysIPRangeCredentials
		VALUES (@iprangeid, 7, 10)

		INSERT INTO
			tsysIPRangeCredentials
		VALUES (@iprangeid, 52, 11)

		FETCH NEXT FROM iprange
		INTO @iprangeid
	END;
CLOSE iprange
DEALLOCATE iprange
"@

IF($BuildTSQLOnly){$sql+"`r`n" | Out-file $OutputFile -Append}
ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

#END SCRIPT
	<###############################################################################
	Import-ADSites
	################################################################################
	DEPENDENCIES: Powershell Module - ActiveDirectory
	SQL Access to referenced SQL server and Database

	DESCRIPTION: Adds new AD Sites to LANSweeper and schedules a scan of
	their IP subnets.

	CHANGE LOG:
	2017-04-19:
	- Force $ScanRangesTable to Type ARRAY instead of DataTable.
	- Performs comparison to avoid updates to DB where no changes have been made.
	- Added Parameter "BuildTSQLOnly" which will output the required SQL rather
	than executing against the SQL server.
	- Fixed Scanning timing to properly separate across a week.
	2017-10-09: Fixed errors importing credentials and importing IP Scan Ranges.

	################################################################################>
	param(
	[Parameter(
	Mandatory=$false,
	HelpMessage="Optional parameter to build the TSQL queries only and not execute them."
	)][Switch]$BuildTSQLOnly,

	[Parameter(
	Mandatory=$true,
	HelpMessage="hostname and instance of SQL server , eg 'sqlserver\instance'. Mandatory.",
	Position=1
	)][string]$dbServer,

	[Parameter(
	Mandatory=$false,
	HelpMessage="Name of the Lansweeper Database on the server. Default is 'lansweeperdb'",
	Position=2
	)][string]$dbName = 'lansweeperdb'

	)

	#$BuildTSQLOnly = $TRUE

	#Region Functions
	Function Invoke-SQL {
	param(
	[string] $dataSource = ".\SQLEXPRESS",
	[string] $database = "MasterData",
	[string] $sqlCommand = $(throw "Please specify a query.")
	)

	$connectionString = "Data Source=$dataSource; " +
	"Integrated Security=True; " +
	"Initial Catalog=$database"

	$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
	$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
	$connection.Open()

	$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
	$dataset = New-Object System.Data.DataSet
	$adapter.Fill($dataSet) \| Out-Null

	$connection.Close()
	$dataSet.Tables
	}
	#EndRegion

	#Region Variables
	#Static
	$table = "dbo.tsysIPLocations"
	$scanTable = "dbo.tsysIPScanRanges"
	$results = @{}
	$duplicates = @()

	#Calculated
	$ScriptName = $MyInvocation.MyCommand.Name
	$OutputFile = "$PSScriptRoot\$ScriptName" -replace ".ps1","-SQLCommand.txt"
	$searchBase = (Get-ADRootDSE).configurationNamingContext
	$searchBase = "CN=Subnets,CN=Sites," + $searchBase
	$subnets = Get-ADObject -Filter {ObjectClass -eq 'Subnet'} -SearchBase $searchBase -Properties siteObject \| Select name,siteObject
	#EndRegion

	#region Format Subnets for adding to LANSweeper Database
	ForEach ($subnet in $subnets) {

	#Get the Subnet Start and bit mask from the Subnet Name attribute
	[System.Collections.ArrayList]$octets = @()
	$octets = $subnet.name.substring(0,$subnet.name.indexOf("/")).split(".")
	$BitMask = $subnet.name.substring($subnet.name.indexOf("/")+1)
	$endOctets = $octets.Clone()

	#region Build the end address based on the bit mask and start address
	#Get which Octet we'll be operating in.
	if ($BitMask -lt 32) {
	$octetNo = [Math]::Floor([int]$BitMask / 8)
	#Get how many bits within this octet we need to mask
	$remainder = $BitMask % 8

	#convert value to binary
	$modOctet = [Convert]::ToString($endOctets[$octetNo],2)

	#make binary value 8 bits long
	While ($modOctet.Length -lt 8){
	$modOctet = "0" + $modOctet
	}

	#convert to char array and set each bit that is masked to "1"
	$modOctet = $modOctet.ToCharArray()
	For ($i = $remainder;$i -le 8;$i++) {
	$modOctet[$i-1] = "1"
	}

	#set the last bit in the array to 0 (eg, we'll end up with 254 instead of 255)
	#$modOctet[7] = "0"

	#convert back to decimal
	$modOctet = [string]::Join("",[String[]]$modOctet)
	$modOctet = [Convert]::ToInt32($modOctet,2)
	#if ($modOctet -eq 255){$modOctet = 255}

	#store back in the octet array
	try {
	$endOctets[$octetNo] = [string]$modOctet
	} catch {
	ECHO "CAUGHT!"
	}

	#if we're not in the last octet, set the last octet to 254
	While ($octetNo -lt 3) {
	$endOctets[++$octetNo] = "255"
	}
	$endOctets[3] = "254"
	}
	#endregion

	#set the first address to 1 if it's a 0 (10.0.0.0 isn't a valid address, 10.0.0.1 is)
	if ([int]$octets[3] -eq 0) {$octets[3] = "1"}

	#Finally, store our values to be used in the database
	$RealStart = [string]::Join(".",@($octets))
	$RealEnd = [string]::Join(".",@($EndOctets))

	#prefix each octet(except the first) with 0 if less than 3 chars (formatting for key in db)
	For ($i=1;$i-le 3;$i++) {
	while ($octets[$i].length -lt 3) {
	$octets[$i] = "0" + $octets[$i]
	}

	while ($endOctets[$i].length -lt 3) {
	$endOctets[$i] = "0" + $endOctets[$i]
	}
	}

	#Save the modified octets as strings
	$startIP = [string]::Join("",@($octets))
	$endIP = [string]::Join("",@($endOctets))

	#Pull out the name of the site
	$IPLocation = $subnet.siteObject.Substring(3,$subnet.siteObject.IndexOf(",CN")-3)

	#store in object
	$propertyhash = [ordered]@{
	StartIP = $startIP
	EndIP = $endIP
	IPLocation = $IPLocation
	RealStart = $RealStart
	RealEnd = $RealEnd
	}
	[array]$arrayresults += New-Object PSObject -Property $propertyhash
	try{$results.Add($startIP,(New-Object PSObject -Property $propertyhash))}
	catch {
	ECHO "Duplicate?"
	$duplicates += $results[$startIP]
	$duplicates += New-Object PSObject -Property $propertyhash
	}
	}
	#endregion

	IF($duplicates){
	Write-Host "Duplicate Subnets found:"
	$duplicates
	}

	#region Update Location Table in LANSweeper Database
	$getCurrentTable = @"
	SELECT *
	FROM $table
	"@

	#region IP Locations Statements
	$AddToTable = @"
	INSERT INTO $table (StartIP,EndIP,IPLocation,Realstart,Realend)
	VALUES ('{0}','{1}','{2}','{3}','{4}');
	"@

	$UpdateTable = @"
	UPDATE $table
	SET EndIP={1},IPLocation='{2}',Realstart='{3}',Realend='{4}'
	WHERE StartIP='{0}';
	"@

	$RemoveFromTable = @"
	DELETE FROM $table
	WHERE StartIP='{0}';
	"@
	#endregion

	#regionIP Scan Range Statements
	$AddToScanTable = @"
	INSERT INTO $scanTable (ServerName,Ipstart,Ipend,Enabled,PingTimeout,Day1,Day2,Day3,Day4,Day5,Day6,Day7,Minutes)
	VALUES ('sdcplansweeper','{0}','{1}','1','1','1','1','1','1','1','1','1','1');
	"@

	$UpdateScanTable = @"
	UPDATE $scanTable
	SET IpEnd='{1}',PingTimeout=1
	WHERE Ipstart='{0}';
	"@

	$RemoveFromScanTable = @"
	DELETE FROM $scanTable
	WHERE Ipstart='{0}';
	"@

	$GetScanTable = @"
	SELECT *
	FROM $scanTable
	"@
	#endregion

	#Get current values of LANSweeper IPLocation Table
	$currentTable = (Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $getCurrentTable) \| Select StartIP,EndIP,IPLocation,Realstart,Realend
	$ipScanRangesTable = (Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $GetScanTable) \| Select Ipstart
	$IPScanRangeHash = @{}
	ForEach ($row in $ipScanRangesTable)
	{
	$IPScanRangeHash.Add($row.Ipstart,$true)
	}
	Write-Host "Importing to DB"
	ForEach ($row in $currentTable) {

	if ($results.ContainsKey([string]$row.StartIP))
	{
	$data = $results[[string]$row.StartIP]

	IF(Compare ($row \| Out-String) ($data \| Out-String))
	{
	Write-Host $data.IPLocation": Data in AD differs from Lansweeper DB. Updating DB."
	#Update Location Table
	$sql = $UpdateTable -f $data.StartIP,$data.EndIP,$data.IPLocation,$data.Realstart,$data.Realend
	try{
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
	} catch {ECHO Caught}
	#Update Scan Range Table
	$scanSql = $UpdateScanTable -f $data.Realstart,$data.Realend
	Try{
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $scansql}
	} catch {ECHO Caught}
	}

	if (!$IPScanRangeHash.Contains($row.RealStart))
	{
	$data = $results[[string]$row.StartIP]
	$sql = $AddToScanTable -f $data.RealStart,$data.RealEnd
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

	}
	$results.Remove($data.StartIP)
	}
	else {
	<#
	Remove Row From Location Table
	$sql = $RemoveFromTable -f $row.StartIP
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
	#>

	<#Remove Row From Scan Range Table
	$scanSql = $RemoveFromScanTable -f $row.Realstart
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $scansql}
	#>
	}
	}

	if ($results.Values.Count -gt 0){
	Write-Host "Adding New: $($Results.Values.Count)"
	ForEach ($data in $results.Values){

	#Add Row to Location Table
	$sql = $AddToTable -f $data.StartIP,$data.EndIP,$data.IPLocation,$data.Realstart,$data.Realend
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

	$sql = $AddToScanTable -f $data.RealStart,$data.RealEnd
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}
	}
	}
	#endregion

	#region stagger start times across all IP ranges
	[array]$ScanRangesTable = Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand "Select * From $ScanTable"
	$MinutesInWeek = 72460
	$minutesPerRange = $minutesInWeek / ($scanRangesTable.count)

	$UpdateScanRangeTime = @"
	SET DATEFORMAT ymd
	UPDATE $scanTable
	SET Day1='{0}',Day2='{2}',Day3='{4}',Day4='{6}',Day5='{8}',Day6='{10}',Day7='{12}',
	Day1time='{1}',Day2time='{3}',Day3time='{5}',Day4time='{7}',Day5time='{9}',Day6time='{11}',Day7time='{13}'
	WHERE Ipstart='{14}'
	"@

	$ScanTime = Get-Date -Day 1 -Month 1 -Year 1900 -Hour 0 -Minute 0 -second 0
	$defaultDate = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";
	ForEach ($range in $ScanRangesTable) {
	#Base Values
	$day1=0; $day2=0; $day3=0; $day4=0; $day5=0; $day6=0; $day7=0
	$day1Time=$defaultDate; $day2Time=$defaultDate; $day3Time=$defaultDate;$day4Time=$defaultDate;$day5Time=$defaultDate;$day6Time=$defaultDate;$day7Time=$defaultDate

	#Set Active Values
	$startIP = $range.IpStart
	Switch ($ScanTime.Day) {
	1 {$day1 = 1; $day1Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	2 {$day2 = 1; $day2Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	3 {$day3 = 1; $day3Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	4 {$day4 = 1; $day4Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	5 {$day5 = 1; $day5Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	6 {$day6 = 1; $day6Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	7 {$day7 = 1; $day7Time = Get-Date $ScanTime -Format "yyyy-MM-dd HH:mm:00.000";break}
	default {}
	}

	#Update Database
	$sql = $UpdateScanRangeTime -f $day1,$day1Time,$day2,$day2Time,$day3,$day3Time,$day4,$day4Time,$day5,$day5Time,$day6,$day6Time,$day7,$day7time,$startIP
	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

	#Increment Scan time for next subnet.
	$ScanTime = $ScanTime.AddMinutes($minutesPerRange)
	}
	#endRegion
	$ErrorActionPreference = "SilentlyContinue"
	#Set credentials used for scanning
	$sql = @"
	DECLARE @iprangeid int
	DECLARE iprange CURSOR FOR
	Select IPRangeID FROM tsysIPScanRanges;

	OPEN iprange;
	FETCH NEXT FROM iprange
	INTO @iprangeid

	WHILE @@FETCH_STATUS = 0
	BEGIN
	INSERT INTO
	tsysIPRangeCredentials
	VALUES (@iprangeid, 7, 10)

	INSERT INTO
	tsysIPRangeCredentials
	VALUES (@iprangeid, 52, 11)

	FETCH NEXT FROM iprange
	INTO @iprangeid
	END;
	CLOSE iprange
	DEALLOCATE iprange
	"@

	IF($BuildTSQLOnly){$sql+"`r`n" \| Out-file $OutputFile -Append}
	ELSE{Invoke-SQL -dataSource $dbServer -database $dbName -sqlCommand $sql}

	#END SCRIPT