SQL -> WQL query help please (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

bcohoon -> SQL -> WQL query help please (6/23/2008 1:01:52 PM)

I have this query working properly in SQL and want to translate it into WQL as a subselect statement in a collection.  I'm not getting anywhere with it so any help would be most appreciated.  Thanks.

SELECT      dbo.System_DISC.Name0, dbo.ClientOfferStatus.OfferID, dbo.ClientOfferStatus.LastStatus
FROM         dbo.System_DISC INNER JOIN
                   dbo.ClientOfferStatus ON dbo.System_DISC.ItemKey = dbo.ClientOfferStatus.ItemKey
WHERE       (dbo.ClientOfferStatus.OfferID = 'WC12064D') AND (dbo.ClientOfferStatus.LastStatus <> 1073751832) AND
                    (dbo.System_DISC.Name0 LIKE 'L%' OR dbo.System_DISC.Name0 LIKE 'H%')

It's basically bringing back all non L% and H% machine names whose last status message on advertisement WC12064D are not 1073751832 (10008 in the vSMS_ClientAdvertisementStatus view)



bcohoon -> RE: SQL -> WQL query help please (6/24/2008 5:06:08 PM)

I figured it out.  Here is the WQL in the Collection.


select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (SELECT SMS_R_System.ResourceID     FROM SMS_R_System INNER JOIN SMS_ClientAdvertisementStatus ON SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID     WHERE (SMS_ClientAdvertisementStatus.LastState <> 13) AND (SMS_ClientAdvertisementStatus.AdvertisementID = 'WC12064D') AND (SMS_R_System.Name0 LIKE 'H%' OR SMS_R_System.Name0 LIKE 'L%' OR SMS_R_System.Name0 LIKE 'T%'))


-Brian



jnelson993 -> RE: SQL -> WQL query help please (6/24/2008 5:33:53 PM)

Just a bit of input if you don't mind me sayin'

That subquery isn't necessary...in it you join SMS_R_SYSTEM to SMS_ClientAdvertisementStatus, but you've got SMS_R_SYSTEM in the outer query, so why not just do your join there and avoid the subquery altogether.  And instead of doing all those OR's, you can do it in one LIKE statement using a bracket wildcard [] operator to indicate a set of letters something like this:

SELECT
  SMS_R_System.ResourceID,
  SMS_R_System.ResourceType,
  SMS_R_System.Name,
  SMS_R_System.SMSUniqueIdentifier,
  SMS_R_System.ResourceDomainORWorkgroup,
  SMS_R_System.Client
FROM  
  SMS_R_System
  INNER JOIN SMS_ClientAdvertisementStatus
    ON SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID
WHERE  SMS_ClientAdvertisementStatus.LastState <> 13
      AND SMS_ClientAdvertisementStatus.AdvertisementID = 'WC12064D'
      AND SMS_R_System.Name0 LIKE '[HLT]%'



akaplan -> RE: SQL -> WQL query help please (6/25/2008 9:52:10 AM)

"you can do it in one LIKE statement using a bracket wildcard [] operator to indicate a set of letters..."

Thanks, John.  Years of SMS WQL queries, and I did not know that.

Alan



bmason505 -> RE: SQL -> WQL query help please (6/25/2008 11:54:36 AM)

Yeah, that bracket thing is cool.  I meant to mention that last night.  Does it work in SQL too?



jnelson993 -> RE: SQL -> WQL query help please (6/25/2008 12:27:57 PM)

Sure does.



jnelson993 -> RE: SQL -> WQL query help please (6/25/2008 12:30:29 PM)

In fact, I recently posted an article on WMI stability backports that used the bracked wildcards in report SQL and WQL and did a little 'splaining.

http://myitforum.com/cs2/blogs/jnelson/archive/2008/06/04/118136.aspx



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.171875