locked
Using a variable and wildcard in a where-object not returning results RRS feed

  • Question

  • Hi, guys. Working on a script to filter output from a sql query, probably an easy question but I'm not great at scripting yet. 

    $Users = Get-ADGroupmember -identity "UserGroup" | select name

    $Query = invoke-sqlcmd -query "select  
    all SMS_UserMachineIntelligence.LastLoginTime,SMS_UserMachineIntelligence.
    NumberOfLogins,SMS_UserMachineIntelligence.MachineResourceName,SMS_UserMachineIntelligence.UniqueUserName 
    from v_UserMachineIntelligence AS SMS_UserMachineIntelligence" -Database cm_SCM | where-Object {$_.UniqueUserName -like "*$Users"}

    Nothing is being output. If use an actual username instead of the variable after the wildcard, i get data. Any help?

    Thanks

    Monday, December 5, 2016 6:34 PM

Answers

  • You cannot use "like" to wildcard an array.

    $sql = @'
    SELECT  
     	LastLoginTime,
    	NumberOfLogins,
    	MachineResourceName
    	UniqueUserName 
    FROM 
    	v_UserMachineIntelligence
    '@
    $Users = Get-ADGroupmember -identity "UserGroup" | select -Expand name invoke-sqlcmd -query $sql -Database cm_SCM | where-Object { $Users -contains $_.UniqueUserName }

    You do not need or want all of that decoration for this SQL statement.

    Note how we get th user name array


    \_(ツ)_/


    • Edited by jrv Monday, December 5, 2016 6:57 PM
    • Proposed as answer by Vincent Karunaidas Monday, December 5, 2016 7:01 PM
    • Unproposed as answer by somebodycool Monday, December 5, 2016 7:03 PM
    • Marked as answer by somebodycool Monday, December 5, 2016 9:14 PM
    Monday, December 5, 2016 6:56 PM

All replies

  • Sorry, this is what I'm doing

    $Users = Get-ADGroupmember -identity "UserGroup" | select name

    invoke-sqlcmd -query "select  
    all SMS_UserMachineIntelligence.LastLoginTime,SMS_UserMachineIntelligence.
    NumberOfLogins,SMS_UserMachineIntelligence.MachineResourceName,SMS_UserMachineIntelligence.UniqueUserName 
    from v_UserMachineIntelligence AS SMS_UserMachineIntelligence" -Database cm_SCM | where-Object {$_.UniqueUserName -like "*$Users"}

    Thank you

    Monday, December 5, 2016 6:35 PM
  • $_.UniqueUserName -like "*$($Users.Name)"
    Monday, December 5, 2016 6:41 PM
  • It still didn't return anything :/
    Monday, December 5, 2016 6:50 PM
  • $Users = Get-ADGroupmember -identity "UserGroup" | select -ExpandProperty name

    {$_.UniqueUserName -like "*$Users"}

    Monday, December 5, 2016 6:53 PM
  • You cannot use "like" to wildcard an array.

    $sql = @'
    SELECT  
     	LastLoginTime,
    	NumberOfLogins,
    	MachineResourceName
    	UniqueUserName 
    FROM 
    	v_UserMachineIntelligence
    '@
    $Users = Get-ADGroupmember -identity "UserGroup" | select -Expand name invoke-sqlcmd -query $sql -Database cm_SCM | where-Object { $Users -contains $_.UniqueUserName }

    You do not need or want all of that decoration for this SQL statement.

    Note how we get th user name array


    \_(ツ)_/


    • Edited by jrv Monday, December 5, 2016 6:57 PM
    • Proposed as answer by Vincent Karunaidas Monday, December 5, 2016 7:01 PM
    • Unproposed as answer by somebodycool Monday, December 5, 2016 7:03 PM
    • Marked as answer by somebodycool Monday, December 5, 2016 9:14 PM
    Monday, December 5, 2016 6:56 PM
  • You cannot use "like" to wildcard an array.

    $sql = @'
    SELECT  
     	LastLoginTime,
    	NumberOfLogins,
    	MachineResourceName
    	UniqueUserName 
    FROM 
    	v_UserMachineIntelligence
    '@
    $Users = Get-ADGroupmember -identity "UserGroup" | select -Expand name invoke-sqlcmd -query $sql -Database cm_SCM | where-Object { $Users -contains $_.UniqueUserName }

    You do not need or want all of that decoration for this SQL statement.

    Note how we get th user name array


    \_(ツ)_/


    This did not work for me. I still did not get any output
    Monday, December 5, 2016 7:04 PM
  • The most likely cause is that the names do not match or the query is returning nothing.

    Start with the query only.

    invoke-sqlcmd -query $sql -Database cm_SCM

    Does it return data?


    \_(ツ)_/


    • Edited by jrv Monday, December 5, 2016 7:22 PM
    Monday, December 5, 2016 7:22 PM
  • The most likely cause is that the names do not match or the query is returning nothing.

    Start with the query only.

    invoke-sqlcmd -query $sql -Database cm_SCM

    Does it return data?


    \_(ツ)_/


    Yes that does. however, the uniqueusername column is now showing the computer names. And there is no column for user names. 

    Thanks

    Monday, December 5, 2016 7:29 PM
  • Then do this and look for the names:

    invoke-sqlcmd -query 'select * from v_UserMachineIntelligence' -Database cm_SCM

    The names in views can be obfuscated.  You have t discover them and use the correct name.


    \_(ツ)_/

    Monday, December 5, 2016 8:22 PM