question

ArnaudDURAND-5662 avatar image
0 Votes"
ArnaudDURAND-5662 asked BertZhoumsft-7490 commented

SCCM - WQL fuse 2 query

First, sorry for my English ;)
I don't know a thing of WQL... But I need to fuse two queries in a single one.

1)

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_G_System_DCMDeploymentState on SMS_G_System_DCMDeploymentState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_DCMDeploymentState.BaselineID = "ScopeId_B3CE35F3-4C3B-4B65-8791-1B03FABE9F99/Baseline_20b206e9-3bed-471d-8ca9-7efa3bd8d7b9"
and SMS_G_System_DCMDeploymentState.ComplianceState = "1"

2)

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,
SYS.ResourceDomainORWorkgroup,SYS.Client
from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as
offer on sys.ResourceID=offer.ResourceID
WHERE AdvertisementID = 'PR12019D' and LastStateName = 'No Status'


Can you help me?
Thank you.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ArnaudDURAND-5662
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Bert zhou

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

If you want to combine the two result sets into one, then you can use a UNION (ALL), see
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15 => Examples

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
1 Vote"
BertZhoumsft-7490 answered

Hi,@ArnaudDURAND-5662
Welcome to Microsoft T-SQL Q&A Forum!

Yes, as olaf said , you can use union to find all records . I see that the fields queried in your table above are the same as the ones below , and I prefer to link the three tables first , and in the search field, maybe you can try this:

 select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,
        SYS.ResourceDomainORWorkgroup,SYS.Client
 from   SMS_R_System sys
 inner join SMS_G_System_DCMDeploymentState as DCM
        on DCM.ResourceID = sys.ResourceId 
 left  join SMS_ClientAdvertisementStatus as offer 
        on  sys.ResourceID=offer.ResourceID
 where  AdvertisementID = 'PR12019D' and LastStateName = 'No Status' 
        AND DCM.BaselineID = "ScopeId_B3CE35F3-4C3B-4B65-8791-1B03FABE9F99/Baseline_20b206e9-3bed-471d-8ca9-7efa3bd8d7b9"
        AND DCM.ComplianceState = "1"

Reminder: It is recommended to alias the table, it will help you read the code, the name is too long and easy to forget.

Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArnaudDURAND-5662 avatar image
0 Votes"
ArnaudDURAND-5662 answered BertZhoumsft-7490 commented

@BertZhoumsft-7490

Thanks for your reply ;)
When a try your query in SCCM I've got an error "The query statement that you entered is not valid"

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ArnaudDURAND-5662
This is because our statement is a sql query, not a sccm query. Please refer to this link for explanation.
Bert Zhou

0 Votes 0 ·