Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Extracting WQL "count" results

by rod (Novice)
on Jul 05, 2006 at 07:34 UTC ( [id://559261]=perlquestion: print w/replies, xml ) Need Help??

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");
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};
...$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);
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 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?


    Free your mind

      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.
        Unfortunately if forced into WMI, this would mean the problem is most unlikely to be resolvable within the scope of Perl.
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?

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 );


    Free your mind

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


        Free your mind

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?

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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://559261]
Approved by Corion
Front-paged by planetscape
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2024-05-30 13:42 GMT
Find Nodes?
    Voting Booth?

    No recent polls found