SCCM query: How to get Latest user-logon registration (if any) and display PC, IP, User, logontimestamp

Question

Hi, I'm trying to create a SQL 2005 query to retrieve and combine records from 3 SCCM Data Views. The first view contains records of valid PC's; the second contains logon-information containing: PC-id, username, timestamp, etc; the third contains PC-id, IP-address.

The 1stview only contains a single, nique record per PC; the 2nd view can contain multiple records per PC: one for each time a user logs on to a computer; the 3rd can contain multiple records per PC: one for each IP that has been registered in the database.

so it's like:

view1 (v_R_System_Valid) fields (among others)
ResourceID, NetBIOS

view2 (v_GS_SYSTEM_CONSOLE_USER) fields (among others)
id,ResourceID,SystemConsoleUser0,LastConsoleUse0 

view3 (v_RA_System_IPAddresses)  fields (among others)
ResourceID,IP_Addresses0

I WANT the query to present me a list of all PC's in the first view, and also display (is available) the IP address; the LATEST time a logon occurred and by WHOM.

The query I built sofar returns each PC, but includes EACH user that logged on to that PC and when, not the latest only. I hope anyone can help me figure this out. I'm not an experienced SQL scripter and constructed the code below using info from the Net.

My query:

Select 
SV.Netbios_Name0 AS [NetBIOS Name],
SCU.SystemConsoleUser0 AS [User Name],
CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use]

from v_R_System_Valid SV
 Left Join (Select ResourceID, SystemConsoleUser0,
                Max(LastConsoleUse0) as theLastTime 
            from v_GS_SYSTEM_CONSOLE_USER 
            group by ResourceID, SystemConsoleUser0) 
     AS SCU on SCU.ResourceID = SV.ResourceID

where (SV.Netbios_Name0 not like 'ENC-%')
and (SV.Netbios_Name0 not like 'NL%')

order by SV.Netbios_Name0

Thanks in advance, Eric

Answer 1

select SV.Netbios_Name0 AS [NetBIOS Name]
       , latest_user.SystemConsoleUser0 as [User Name]
       , CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use]
       , IP.IP_Addresses0

from   v_R_System_Valid SV 
       left outer join
                 (Select  ResourceID
                          ,Max(LastConsoleUse0) as theLastTime 
                 from     v_GS_SYSTEM_CONSOLE_USER 
                 group by ResourceID
                          ,SystemConsoleUser0) AS SCU 
       on SCU.ResourceID = SV.ResourceID

       left outer join v_GS_SYSTEM_CONSOLE_USER as latest_user
       on latest_user.LastConsoleUse0 = SCU.theLastTime
   and
       latestuser.ResourceID = SCU.ResourceID

       left outer join v_RA_System_IPAddresses as IP
       on IP.ResourceID = SV.ResourceID

where  (SV.Netbios_Name0 not like 'ENC-%') 
    and 
       (SV.Netbios_Name0 not like 'NL%')

order by SV.Netbios_Name0

Answer 2

With the help from Adam I came to this resulting query: select SV.Netbios_Name0 AS [NetBIOS Name] , latest_user.SystemConsoleUser0 as [User Name] , CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use] , IP.IP_Addresses0

from v_R_System_Valid SV left outer join (Select ResourceID ,Max(LastConsoleUse0) as theLastTime from v_GS_SYSTEM_CONSOLE_USER group by ResourceID) AS SCU on SCU.ResourceID = SV.ResourceID

    left outer join v_GS_SYSTEM_CONSOLE_USER as latest_user
        on latest_user.LastConsoleUse0 = SCU.theLastTime
             and
           latest_user.ResourceID = SCU.ResourceID

    left outer join v_RA_System_IPAddresses as IP
        on IP.ResourceID = SV.ResourceID where (SV.Netbios_Name0 not like 'ENC-%')
             and
           (SV.Netbios_Name0 not like 'NL%')
             and
           (IP.IP_Addresses0 like '10.%')
        order by SV.Netbios_Name0