Creating basic sql/SCCM query

Question

I’m trying to create a SQL query that will give me some basic information that includes:

System Name (from v_R_System)

TopConsoleUser (from v_GS_System_Console_Usage_Maxgroup)

User office location (from v_R_User)

The query looks like this:

Select v_R_System.Name0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_R_User.l0 From v_R_System Inner Join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP On v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System.ResourceID Inner Join v_R_User On v_R_User.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID But when I execute the query, it returns no results and does so in seconds(Done in 0.6942996 seconds.) I’m new to query writing, and I’m trying to figure it out. Where I get lost is when it comes to “JOINS” confusing as hell! Any help would be appreciated!

solved 0
Astin Martin 2020-05-06T18:28:28+05:30 3 Answers 285 views Beginner
0

Answers ( 3 )

  2. karthikeyan
    3
    2020-05-06T21:34:51+05:30
    Reply

    Hi,

    Try this below SQL Query to get the Computer Name along with respective Topconsoleuser information.
    ——————————————————————–
    SELECT DISTINCT Vrs.Name0 AS [Computer Name],
    Vrs.User_Name0 AS [Last Logged on User],
    Con.TopConsoleUser0 AS [Top User]
    FROM v_r_system Vrs
    LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE Con ON Vrs.ResourceID = Con.ResourceID ORDER BY Vrs.Name0
    —————————————————————-

    To add the office location column in above existing query. First you need to add the office attribute in User discovery settings then only information will be collected from Active directory user to your SCCM database.

    then using left join you can add that column name followed by v_R_users view.

    Thanks
    Karthikeyan

    Best answer
  4. Anoop C Nair
    0
    2020-05-06T20:50:16+05:30
    Reply

    Try this I found a typo “v_R_User.l0 From v_R_System Inner Join”

    v_R_User.l0 instead of v_R_User.Mail0 ?

    Select v_R_System.Name0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_R_User.Mail0 From v_R_System
    Inner Join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP On v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System.ResourceID
    Inner Join v_R_User On v_R_User.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID

  6. Jitesh Kumar
    0
    2020-05-06T19:23:28+05:30
    Reply

    Hello Astin, If you would able to provide the output | Error with screenshots or make other understandable with scenarios along with queries. That will help others to easily identify what you trying to get and what’s the issue you encountering!!

Leave an answer

Sorry, you do not have permission to answer to this question .