In PowerShell, combine these two scripts where if the hostname IP matches the DHCP scope, append the csv output

Question

I have two scripts. I would like to take the 1st script and add a condition that if the hostname IP matches a ScopeIP to add the name of that scope to Results.csv

This is my current output

This is what I want my output to look like

I have tried Importing the two output csv files, then comparing, but I don't want to output more than one csv file. It also seems too complicated to reimport the two csv files, then compare the two the ScopeID columns and then get the corresponding scope name from a separate column.

Thank you.

My two scripts:

Gets Client info using a list of hostnames that outputs to Results.csv


$list = Get-Content C:\script\HostNames.txt #Defines content it pulls as list

$Output = foreach ($hostname in $list) #Calls each item in list a hostname and sends to output
{
    if (test-connection -count 1 -computername $hostname -quiet)  #checking if hostname is on line with 1 ping, If online run the following
    {
        $System = Get-WmiObject Win32_ComputerSystem -ComputerName $hostname | Select-Object -Property Name,Model 
        $BIOS = Get-WmiObject Win32_BIOS -ComputerName $hostname | Select-Object -Property SerialNumber
        $User = get-childitem "C:\Users" | Sort-Object LastWriteTime -Descending | Select-Object -first 1
        $mac = invoke-command -computername $hostname {(gwmi -class win32_networkadapterconfiguration).MacAddress | select -first 1}
        $IpV = (test-connection -ComputerName $hostname -count 1 | select -expandproperty IPV4Address).IPaddresstostring
        $parts = $IpV.Split(".")  #converts the last octet into a zero
        $parts[3] = "0"
        $ip2 = [String]::Join(".", $parts)
    }
    
    
    else #statement if hostname is not online
    { 
        write-host $hostname not online
    }

[PSCustomObject]@{ #Rename varibles in data pull for output file
        ComputerName = $hostname
        Model = $System.Model
        SerialNumber = $BIOS.SerialNumber
        LastUser = $User
        MacAddress = $mac
        IpAddress = $IpV
        IpScope = $ip2}
    

}
$Output

$Output | Export-Csv -Path C:\script\Result.csv -NoTypeInformation

Gets DHCP scope names and ranges outputs to a ServerScopes.csv


$DHServers = Get-DhcpServerInDC
foreach ($Server in $DHServers)
{

$scopes = Get-DHCPServerv4Scope -ComputerName $Server.DnsName | Select-Object Name, ScopeID #only getting the Name and ScopeID

ForEach ($Address in $scopes) 
    {
$DHCPServer = $Server.DnsName

$Address | Export-Csv "C:\script\Results\ServerScopes.csv" -Append -NoTypeInformation
    
    }
 }

Here is what my DHCP scope names output looks like

So, in fact, you want to join two objects. Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)), it will probably come down to: `$Output |Join $DHServers -On ScopeID` — iRon, Mar 31 '21 at 10:50

score 1 · Answer 1 · answered Mar 30 '21 at 22:17

1

One way you could handle this is to take your second CSV data and turn it into a hash table. Of course I would work on the data prior to exporting it (or still have it available) but working from your examples..

# Create a lookup table where the key is the ScopeID
$lookup = Import-Csv -Path "C:\script\Results\ServerScopes.csv" | Group-Object -Property ScopeID -AsHashTable

Then in your first script just add a line to your object creation like this

[PSCustomObject]@{ #Rename varibles in data pull for output file
    ComputerName = $hostname
    Model = $System.Model
    SerialNumber = $BIOS.SerialNumber
    LastUser = $User
    MacAddress = $mac
    IpAddress = $IpV
    IpScope = $ip2
    Name = $lookup[$ip2].name
}

answered Mar 30 '21 at 22:17

Doug Maurer

8,090
3
12
13

Thank you, I really like this option. I've been tinkering with it, but adding that key to my CustomObject gives: Index operation failed; the array index evaluated to null. What about the hashtable would cause that disconnect ? – feelsgood Mar 31 '21 at 13:41
You have to make the lookup table first. – Doug Maurer Mar 31 '21 at 13:59
1

@BryceHoward Also `$ip2` must be valid and contained in `$lookup`. – Cpt.Whale Mar 31 '21 at 14:25
@BryceHoward makes a good point. You could change it to be an if statement – Doug Maurer Mar 31 '21 at 14:35
I edit the IP address so the last octet matches the ScopeID, which I call $ip2. I do this so it matches the ScopeID exactly which is my the key. Am I doing/saying that right? – feelsgood Mar 31 '21 at 14:37
@DougMaurer I see. Thanks, I'm New to hash tables and PowerShell. $lookup is not importing the .csv as a lookup table? T – feelsgood Mar 31 '21 at 14:39
1

@DougMaurer I get rid Of the Null! It was a syntax error lol. Thank you so much, I appreciate it greatly. – feelsgood Mar 31 '21 at 14:54

score 0 · Accepted Answer · answered Apr 15 '21 at 16:43

$DHServers = Get-DhcpServerInDC #get DHCP info
$hashtable = @{} #create hash table

foreach ($server in $DHServers){
$scopes = Get-DHCPServerv4Scope -ComputerName $server.dnsname #get all scopes in DHCP   
    
    foreach ($_ in (Import-Csv C:\script\Asset_List.csv | Select-Object -ExpandProperty asset)){ #get hostnames from list          
        
        foreach ($scope in $scopes){            
            if($scope | Get-DhcpServerV4Lease -ComputerName $server.dnsname | Where-Object HostName -like "$_*" ){ #compares the hostname to find which lease it is in
                $scopename=$scope.name #matches give scope name
                $hashtable[$_] = $scopename #when a match is found, add keys and values to table
            } 
        }
    }
}

In PowerShell, combine these two scripts where if the hostname IP matches the DHCP scope, append the csv output

2 Answers2