Hi, @TechUST
Sure, you can create two collections based on your needs.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi Expert,
I have requirement to create user collection for MS Visio & project based on current install to perform upgrade.
user collection, being fed by a query that way i want to capture all current installs.
Can you please help to create query-based collection for this? this would be really helpful.
@CherryZhang-MSFT You really helped me lot in previous query, i would really glad if you could help me with this.
Hi, @TechUST
Sure, you can create two collections based on your needs.
Hi, @TechUST
Thank you for posting in Microsoft Q&A forum.
You may try the query to create the user collection:
select * from SMS_R_User where SMS_R_User.UniqueUserName in
(select
SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser
from
SMS_R_System
inner join
SMS_G_System_ADD_REMOVE_PROGRAMS
on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_CONSOLE_USAGE
on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%visio%"
or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project%")
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 "Add 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.
Can i create two collections for Visio
select * from SMS_R_User where SMS_R_User.UniqueUserName in (select SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%visio%"
for project
select * from SMS_R_User where SMS_R_User.UniqueUserName in (select SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project%"
Hi @AllenLiu-MSFT Thank you so much for help,
I have two questions,
*1- I need collections for Visio and project upgrade from older version to 365 (monthly enterprise channel) Visio & project so is there any way to determine how many visio's users need to be upgraded by collection query ( i want query to get username with Visio version so that can upgrade from older version to 365)
2-**When i try to create collection with below query there is no problem, i am able to create.*
select * from SMS_R_User where SMS_R_User.UniqueUserName in
(select
SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser
from
SMS_R_System
inner join
SMS_G_System_ADD_REMOVE_PROGRAMS
on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_CONSOLE_USAGE
on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%visio%"
or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project%")
But when try to create sperate user collection for Visio or project by using below query, i am getting syntax error? please suggest me if i missed something wrong.
select * from SMS_R_User where SMS_R_User.UniqueUserName in
(select
SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser
from
SMS_R_System
inner join
SMS_G_System_ADD_REMOVE_PROGRAMS
on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_CONSOLE_USAGE
on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%visio%"
select * from SMS_R_User where SMS_R_User.UniqueUserName in
(select
SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser
from
SMS_R_System
inner join
SMS_G_System_ADD_REMOVE_PROGRAMS
on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_CONSOLE_USAGE
on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%visio%"
and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "xxx.x.xxx.xx")