Solved

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

Posted on 2016-09-28
2
38 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now