PowerShell - How do I pass the current iteration to a sql query from select object hashtable?

I don't know if I am even asking this correctly. I am using Get-ADComputer to return computers that have not logged in for so long. I am also pulling some other information from this object as well as a test-communication to see if it is currently online. Lastly, I am trying to query a database that will show me if the computer is marked as retired in our inventory system. However, the Inventory query is using the full array of objects in the select rather than the current computer object being used in the reset of the hash table. Below is my current iteration of the script:
$sql= invoke-sqlcmd -query "Select 
a.Retired as retired
FROM [Inventory].[dbo].[Asset] a
JOIN [Inventory].[dbo].[AssetHistory] ah
ON a.AssetID=ah.AssetID
WHERE Description like '$($_.Name)'
and a.Retired = 0
" -database INVENTORY -serverinstance SERVER 

$DaysInactive = 120  
$time = (Get-Date).Adddays(-($DaysInactive)) 

    Get-ADComputer -Filter {LastLogonTimeStamp -lt $time} -Properties LastLogonTimeStamp | 
    select @{Name="Computer";Expression={$_.Name}},
    @{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp)}},
    @{Name='OU1';expression={$_.distinguishedName.split(",")[1].substring(3)}},
    @{Name='OU2';expression={$_.distinguishedName.split(",")[2].substring(3)}},
    @{Name="Pingable";Expression={"$($(Test-Connection $_.Name -count 1 -quiet).ToString())"}},
    @{Name="InInventory";expression={$sql.Retired}}; 

Open in new window


And here is the output:

Computer    : RIPHOPERAOLD
Stamp       : 3/12/2016 1:32:10 AM
OU1         : Servers
OU2         : phr
Pingable    : False
InInventory : {False, False, False, False...}

Notice the InIventory shows an array of results rather than only the current computer results.
LVL 1
0PaulieAsked:
Who is Participating?
 
SubsunConnect With a Mentor Commented:
WHERE Description like '$($_.Name)', I guess here you are trying to query the computer name returned from Get-ADComputer

$sql.Retired wont work as a expression inside the custom property. You can use the SQL query as a function to query against each computer name.
Example..
Function SQLQuery ($Name) {
Invoke-Sqlcmd -query "Select 
a.Retired as retired
FROM [Inventory].[dbo].[Asset] a
JOIN [Inventory].[dbo].[AssetHistory] ah
ON a.AssetID=ah.AssetID
WHERE Description like '$Name'
and a.Retired = 0
" -database INVENTORY -serverinstance SERVER 
}
$DaysInactive = 120  
$time = (Get-Date).Adddays(-($DaysInactive)) 

    Get-ADComputer -Filter {LastLogonTimeStamp -lt $time} -Properties LastLogonTimeStamp | 
    select @{Name="Computer";Expression={$_.Name}},
    @{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp)}},
    @{Name='OU1';expression={$_.distinguishedName.split(",")[1].substring(3)}},
    @{Name='OU2';expression={$_.distinguishedName.split(",")[2].substring(3)}},
    @{Name="Pingable";Expression={"$($(Test-Connection $_.Name -count 1 -quiet).ToString())"}},
    @{Name="InInventory";expression={(SQLQuery $_.Name).Retired}}

Open in new window

0
 
0PaulieAuthor Commented:
This worked perfectly. Thanks.
0
All Courses

From novice to tech pro — start learning today.