SQL query need to edit

Question

Hi Expert,

Can you please help to edit below SQL query since i need username instead of device name.

select v_r_system.name0, Add_Remove_Programs_64_DATA.DisplayName00

 

from Add_Remove_Programs_64_DATA

 

inner join v_r_system on v_r_system.resourceid=Add_Remove_Programs_64_DATA.machineid

 

where

 

--Add_Remove_Programs_64_DATA.Publisher00 like '%microsoft%' and

 

Add_Remove_Programs_64_DATA.DisplayName00='Microsoft Visio - en-us'

Answer 1

Hi, @TechUST

Thank you for posting in Microsoft Q&A forum.

Sorry I just saw this post, I edit the sql query for you:

select v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0, Add_Remove_Programs_64_DATA.DisplayName00

from Add_Remove_Programs_64_DATA

inner join v_r_system on v_r_system.resourceid=Add_Remove_Programs_64_DATA.machineid

inner join v_GS_SYSTEM_CONSOLE_USAGE on v_GS_SYSTEM_CONSOLE_USAGE.ResourceID=v_r_system.resourceid

where

--Add_Remove_Programs_64_DATA.Publisher00 like '%microsoft%' and

Add_Remove_Programs_64_DATA.DisplayName00='Microsoft Visio - en-us'

---

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.

Answer 2

Keep in mind it is not supported to query the sql tables directly as you have posted. Only use the supported sql views.

[https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

Answer 3

Hi @TechUST

select v_R_System.User_Name0, v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 
from v_R_System join v_GS_ADD_REMOVE_PROGRAMS_64 on v_R_System.ResourceID=v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like '%Microsoft visio%'

Answer 4

Hi, @TechUST

I replaced the table(Add_Remove_Programs_64_DATA) with view(v_Add_Remove_Programs) followed Garth's suggestion.

If there any assistance we can provide, feel free to let us know.

select v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0, v_Add_Remove_Programs.DisplayName0

from v_Add_Remove_Programs

inner join v_r_system on v_r_system.resourceid=v_Add_Remove_Programs.ResourceID

inner join v_GS_SYSTEM_CONSOLE_USAGE on v_GS_SYSTEM_CONSOLE_USAGE.ResourceID=v_r_system.resourceid

where

--Add_Remove_Programs_64_DATA.Publisher00 like '%microsoft%' and

v_Add_Remove_Programs.DisplayName0='Microsoft Visio - en-us'