Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-09-28
2
Medium Priority
?
89 Views
Last Modified: 2016-09-28
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.
0
Comment
Question by:0Paulie
2 Comments
 
LVL 40

Accepted Solution

by:
Subsun earned 2000 total points
ID: 41819686
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
 
LVL 1

Author Closing Comment

by:0Paulie
ID: 41819833
This worked perfectly. Thanks.
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question