SQL -> WQL query help please

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

SQL -> WQL query help please

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> SQL -> WQL query help please

Page: [1]

Message

<< Older Topic Newer Topic >>

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

bcohoon

Posts: 2
Score: 1
Joined: 6/23/2008
Status: offline

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)

Post #: 1

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

bcohoon

Posts: 2
Score: 1
Joined: 6/23/2008
Status: offline

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

(in reply to bcohoon)

Post #: 2

RE: SQL -> WQL query help please - 6/24/2008 5:33:53 PM 2 votes

jnelson993

Posts: 801
Score: 111
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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]%'

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to bcohoon)

Post #: 3

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

akaplan

Posts: 172
Score: 21
Joined: 4/22/2003
From: North Carolina
Status: offline

"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

(in reply to jnelson993)

Post #: 4

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

bmason505

Posts: 1981
Score: 104
Joined: 1/23/2003
From: Minneapolis, MN
Status: offline

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

_____________________________

Brian Mason
MCSA\MCSE\MS MVP - SCCM
Wells Fargo
http://www.miscusergroup.org/

(in reply to akaplan)

Post #: 5

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

jnelson993

Posts: 801
Score: 111
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Sure does.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to bmason505)

Post #: 6

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

jnelson993

Posts: 801
Score: 111
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 7