User collection for Visio & project

TechUST 416 Reputation points
2023-01-13T13:57:51.9533333+00:00

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.

Microsoft Configuration Manager Application
Microsoft Configuration Manager Application
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Application: A computer program designed to carry out a specific task other than one relating to the operation of the computer itself, typically to be used by end users.
459 questions
0 comments
Accepted answer
  1. AllenLiu-MSFT 40,551 Reputation points Microsoft Vendor
    2023-01-17T01:42:46.7166667+00:00

    Hi, @TechUST

    Sure, you can create two collections based on your needs.

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 40,551 Reputation points Microsoft Vendor
    2023-01-16T06:17:24.8733333+00:00

    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.

    0 comments
  2. TechUST 416 Reputation points
    2023-01-16T16:38:59.0766667+00:00

    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%"

    0 comments
  3. TechUST 416 Reputation points
    2023-01-18T13:15:37.3933333+00:00

    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%"

    0 comments
  4. AllenLiu-MSFT 40,551 Reputation points Microsoft Vendor
    2023-01-19T06:18:48.1466667+00:00
    1. Yes, we just need to add a condition to filter the version, for example:

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

    1. Because you missed a ")" in the end of the query.