tags:

views:

83

answers:

2

Arrrg!I am running into what i feel is a dumb issue with a simple script i'm writing in powershell. I am invoking a sql command that is calling a stored proc, with the results i put it a array. The results look something like this:

Status                                       ProcessStartTime                            ProcessEndTime                             
------                                       ----------------                            --------------                             
Expired                                      May 22 2010  8:31PM                         May 22 2010  8:32PM

What i'm trying to do is if($s.Contains("Expired")) , report failed. Simple...? :( Problem i'm running into is it looks like Contains method is not being loaded as i get an error like this:

Method invocation failed because [System.Object[]] doesn't contain a method named 'Contains'. At line:1 char:12 + $s.Contains <<<< ("Expired") + CategoryInfo : InvalidOperation: (Contains:String) [], RuntimeException + FullyQualifiedErrorId : MethodNotFound

So, what can i do to stop powershell from unrolling it to string? Actual ps script below -

$s = @(Invoke-Sqlcmd -Query "USE DB
       GO
       exec Monitor_TEST_ps 'EXPORT_RUN',NULL,20" `
       -ServerInstance testdb002\testdb_002
      )

if ($s.Contains("Expired"))   
{
    Write-Host "Expired found, FAIL."
}
else 
{
    Write-Host "Not found, OK." 
}
A: 

The reason you see the method is Get-Members is powershell is trying to be helpful and unrolling the collection. If you have an array with multiple types of items, it shows you the members for each type (like if you ‘ls’ (Get-ChildItem) and there are FileInfos and DirectoryInfos in the directory you are in, and you pipe ls | gm, it will show you members of FileInfos and also another group of members of DirectoryInfos):

(7) C:\ -» ls


    Directory: C:\


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----         5/28/2010   8:19 AM            .hg
d----         5/13/2010   3:37 PM            Build
…
-a---         4/22/2010  11:21 AM       2603 TODO.org


(8) C:\ -» ls | gm


   TypeName: System.IO.DirectoryInfo

Name                      MemberType     Definition
----                      ----------     ----------
Mode                      CodeProperty   System.String Mode{get=Mode;}
Create                    Method         System.Void Create(System.Security.AccessControl.Direct...
…

   TypeName: System.IO.FileInfo

Name                      MemberType     Definition
----                      ----------     ----------
Mode                      CodeProperty   System.String Mode{get=Mode;}
AppendText                Method         System.IO.StreamWriter AppendText()
…

What I usually do, to make sure I am not looking at unrolled members, is try “$s.GetType().Name” first to see what I am dealing with. In your case, it’s clearly and array, since you initialized it like “$s = @(Invo” (the @ = it’s an array.)

To find out if an array contains an item, you can use the -contains operator:

(9) C:\ -» @(1,2,3) -contains 1
True

I think you’ve got an array of strings, so you can use a string literal, like:

(10) C:\ -» @("Stuff","you've","got","might have","Expired") -contains "Expired"
True

But if the expired isn’t an exact match (you were looking for an element that contains expired, like “Connection Expired 1/1/2010”) you need to find matches and check the count, I think:

(23) C:\ -» @("Stuff","you've","got","might have","Connection Expired 1/1/2010") -contains "Expired"
False
(24) C:\ -» @("Stuff","you've","got","might have","Connection Expired 1/1/2010") | ?{$_.Contains("Expired")}
Connection Expired 1/1/2010
(33) C:\ -» $xs = @("Stuff","you've","got","might have","Connection Expired 1/1/2010") | ?{$_.Contains("Expired")}
(34) C:\ -» $xs.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object


(35) C:\ -» $xs
Connection Expired 1/1/2010

In this case, there is only one match, so powershell unrolled it to a string. Jerk. If there are more than 1 matches, however:

(36) C:\ -» $xs = @("Stuff","you've","got","might Expired  have","Connection Expired 1/1/2010") | ?{$_.Contains("Expired")}
(37) C:\ -» $xs.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array


(38) C:\ -» $xs
might Expired  have
Connection Expired 1/1/2010

Now it’s an array.

Luckily, both string and array have a length property:

(39) C:\ -» $xs.Length
2
(40) C:\ -» "Bob".Length
3

So you can check the length of your results that contain “Expired” to see if there are any expired’s:

(41) C:\ -» $xs = @("Stuff","you've","got","might Expired  have","Connection Expired 1/1/2010") | ?{$_.Contains("Expired")} 
(42) C:\ -» if ($xs.Length -gt 0) { Write-Host "Whoas, stuff's expired, dog." }
Whoas, shit's expired, dog.

(maybe someone has a better way to check if a collection contains an item that satisifies some predicate (like LINQ's Any)?)

Chris Bilson
Thanks Chris! This definitely got me in the right direction. Working script: $s = @(Invoke-Sqlcmd -Query "USE DBGOexec Monitor_TEST_ps 'EXPORT_RUN',NULL,20 " ` -ServerInstance "testdb002\testdb_002")if ($s | ?{ $_['Status'] -eq 'OK'}) {Write-Host "Expired found, FAIL."}else {Write-Host "Not found, OK." }
xbnevan
+1  A: 

Simply put, $s is a .NET array (you're use of @() ensures that) and you're attempting to invoke a method (Contains) which doesn't exist on this .NET type.

The simplest way to resolve your problem is to use the PowerShell's -contains operator which has the benefits of working against arrays directly and doing case-insenstive compares e.g.:

if ($s -contains 'expired') { ... }
Keith Hill
Wow, i could of swore i tired that. I feel silly now, Thanks Keith that worked as well. w00t.
xbnevan