http://qs1969.pair.com?node_id=559261

rod has asked for the wisdom of the Perl Monks concerning the following question:

I use Perl to interrogate our SMS database via WMI quite a bit. On this occasion, I want to extract the count of items in a table but can't get Perl to reveal the results! We start with the normal connection to WMI... 
# Connect to WMI on reporting server
#
my $SWbemLocator = Win32::OLE->new("WbemScripting.SWbemLocator") or 
        FatalCOMError("Can't open WbemScripting object");
my $SWbemServices = $SWbemLocator->ConnectServer($Server, "root/sms/si
+te_$SiteCode") or 
        FatalCOMError("Can't connect to server: $Server");

[download]
If I then submit a "straight" query... 
    # Get count of clients in collection
    my $WQL = q/select * from SMS_CM_RES_COLL_CEN01562/;
    $Colln = $SWbemServices->ExecQuery($WQL,"WQL",16) or FatalErr("Exe
+cQuery for Clients failed",1);
    $count = $Colln->{Count};

[download]
...$count contains the count of items in the collection. Unfortunately, on a large collection, this can be a bit slow. Ideally, I'd use the count(*) WQL command like so; 
    # Get count of clients in collection
    my $WQL = q/select count(*) from SMS_CM_RES_COLL_CEN01562/;
    $Colln = $SWbemServices->ExecQuery($WQL,"WQL",16) or FatalErr("Exe
+cQuery for Clients failed",1);

[download]
The problem that I have with this code is that, no matter what I try, I can't get to the actual result!

If I try the same query on WbemTest, I get a single-line answer that shows the correct answer. How do I get to it in Perl?

Can anyone help?

Replies are listed 'Best First'.
Re: Extracting WQL "count" results
by john_oshea (Priest) on Jul 05, 2006 at 17:02 UTC

    What does $Colln end up holding? Is it possible that an (untrapped?) error is being raised by the ExecQuery call?

[reply]
Re: Extracting WQL "count" results
by Moron (Curate) on Jul 05, 2006 at 15:06 UTC
    It is unclear to me from the OP what the DBMS is, where the server lives and why WMI is necessary to access it. Is there any reason for not using DBI?

    -M

    Free your mind

[reply]

      I'm guessing the SMS in 'SMS database' refers to Microsoft's Systems Management Server. While based on SQL Server, it appears that accessing the underlying tables without going through the 'official channels is somewhat frowned upon:

      You should never attempt to obtain SMS data by directly accessing the SQL Server tables on the SMS site database system. Always access SMS data through the SMS Provider, using the appropriate WMI libraries distributed with the WBEM SDK or on the SMS 2.0 CD.
[reply]
        Unfortunately if forced into WMI, this would mean the problem is most unlikely to be resolvable within the scope of Perl.
[reply]
Re: Extracting WQL "count" results
by Moron (Curate) on Jul 06, 2006 at 09:35 UTC
    It might be revealing to examine what does come out, e.g.
    use Data::Dumper;
# your query code
# ...
print Dumper( $Colln );

    [download]

    -M

    Free your mind

[reply]
[d/l]
      It'll probably just tell you it's a Win32::OLE object.
[reply]
        No. If it is then Dumper would traverse it not just print the class name.

        -M

        Free your mind

[reply]
Re: Extracting WQL "count" results
by mjg (Scribe) on Jul 06, 2006 at 17:14 UTC

    I might be missing something, but I don't see any references to a count() function in Microsoft's online WQL documentation. (WQL != SQL)

    It's possible that WbemTest uses the WMI ODBC adapter. Maybe you could try using that instead?

[reply]
      That's because .Count is a property of a Collection object. It is not a "function".
[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom