Report needs to be limited to one record per computer (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

khoffman432 -> Report needs to be limited to one record per computer (10/14/2008 12:16:15 PM)

The following code returns multiple records for each computer because there are multiple users on the computer.  I want to limit the record to the last user who logged on.  I included the v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 not because I need to see that field but because I want to limit the records to the MAX in this field.  Can someone help?

SELECT DISTINCT
v_R_System.Name0,
v_gs_environment.VariableValue0 as "Warranty Expiration",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 as "User",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 as "Time Logged On",
gsSystem.Manufacturer0 as Make,
gsSystem.Model0,
gsbios.SerialNumber0

FROM v_R_System
INNER JOIN v_gs_environment on
v_GS_environment.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_SYSTEM_CONSOLE_USER on
v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_PC_BIOS gsbios on
v_R_System.ResourceID = gsbios.ResourceID
INNER JOIN V_GS_PROCESSOR gsProcessor on
v_R_System.ResourceID = gsProcessor.ResourceID
INNER JOIN V_GS_COMPUTER_SYSTEM gsSystem on
v_R_System.ResourceID = gsSystem.ResourceID

WHERE v_gs_environment.Name0 LIKE 'War%'



okis14 -> RE: Report needs to be limited to one record per computer (10/14/2008 12:54:37 PM)

Does the max function not work? select max( v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0) 

I can't run this to verify it but you should also be able to do a select top 1 and sort the v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 as "Time Logged On".  You'll have to either use DESC or ASC depending on how the results are returned.  This will allow you to not display the time.



jnelson993 -> RE: Report needs to be limited to one record per computer (10/14/2008 2:56:21 PM)

If you have SQL 2005 or higher, you can use the ROW_NUMBER() function in a derived table to pull the last login for each machine instead of pulling directly from dbo.v_GS_SYSTEM_CONSOLE_USER:

SELECT DISTINCT
  v_R_System.Name0,
  v_gs_environment.VariableValue0             AS "Warranty Expiration",
  usr.SystemConsoleUser0 AS "User",
  usr.LastConsoleUse0    AS "Time Logged On",
  gsSystem.Manufacturer0                      AS Make,
  gsSystem.Model0,
  gsbios.SerialNumber0
FROM  
  v_R_System
  INNER JOIN v_gs_environment
    ON v_GS_environment.ResourceID = v_R_System.ResourceID
   INNER JOIN (SELECT
                 ResourceID,
                 SystemConsoleUser0,
                 LastConsoleUse0,
                 ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Position
              FROM
                 dbo.v_GS_SYSTEM_CONSOLE_USER) usr
    ON usr.ResourceID = v_R_System.ResourceID
   AND usr.Position = 1
  INNER JOIN v_GS_PC_BIOS gsbios
    ON v_R_System.ResourceID = gsbios.ResourceID
  INNER JOIN V_GS_PROCESSOR gsProcessor
    ON v_R_System.ResourceID = gsProcessor.ResourceID
  INNER JOIN V_GS_COMPUTER_SYSTEM gsSystem
    ON v_R_System.ResourceID = gsSystem.ResourceID
WHERE  v_gs_environment.Name0 LIKE 'War%'



aparrott -> RE: Report needs to be limited to one record per computer (10/14/2008 5:04:54 PM)

Can't look at an SMS Db right this moment but isn't there a TopConsoleUser entry that tracks the most frequent user?  I would think that value would make the most sense here. 



jnelson993 -> RE: Report needs to be limited to one record per computer (10/14/2008 5:13:53 PM)

Yes, there is, and I thought about adding that as an option, the problem becomes though, they asked for the LAST logged on user, not the person who's logged on the most. 

And not everyone is enamored with TopConsoleUser.  It doesn't work well for vast swaths of machines in our environment because of the constrains they have on them.  We have many machines that don't have a top console user, and I assume many others don't as well.  Out of close to 200K machines, I only see 56K records in the V_GS_SYSTEM_CONSOLE_USAGE view.  That rules that out for us anyway.



khoffman432 -> RE: Report needs to be limited to one record per computer (10/16/2008 11:44:14 AM)

Thanks to everyone who contributed.  Each reply was valuable.
I am using the code supplied by jnelson993 because it was inclusive.
I appreciated the remarks about TopConsoleUser - I didn't know it existed - now I know there may be issues with it.
Thank you for the insight & expertise.



aparrott -> RE: Report needs to be limited to one record per computer (10/16/2008 1:43:14 PM)

Re: jnelson993

I'm curious if you can expand on your issues with TopConsoleUser.  We're looking to rely on this data heavily for various processes and I'm always on the lookout for potential issues.  At least in our environment we have a 99% success rate with TopConsoleUser (45K+ clients). 

Are you saying that in your environment most console usage data is reported (TotalConsoleUsers0, TotalSecurityLogTime, etc.) is reported but TopConsoleUser is blank?



MeenEnta -> RE: Report needs to be limited to one record per computer (10/16/2008 1:58:36 PM)

Is this for SCCM only? do you have the SMS2003 SP3 version ..i.e. the appropriate mof?




khoffman432 -> RE: Report needs to be limited to one record per computer (10/16/2008 3:38:07 PM)

btw, I am getting 45 fewer records using the above code in red than with my orginal code.

Is that because there were possibly no system console users on those 45 computers?





jnelson993 -> RE: Report needs to be limited to one record per computer (10/21/2008 11:37:08 PM)

What I'm saying is, in our environment, many records don't exist for system console user AND system console usage and we just can't use it.  The problem is, when you're as large as we are, and you have as little clout as our team does, you can't get the prerequisites turned on everywhere to make it reliable so I use a combination of data from v_R_System, v_GS_COMPUTER_SYSTEM, V_HS_COMPUTER_SYSTEM, v_GS_SYSTEM_CONSOLE_USER, v_HS_SYSTEM_CONSOLE_USER.

But if you have 99% success rate at your place, then great!  But for us, we're stuck because of politics, not because of technical problems on Microsoft's part.

Yes, if you're getting 45 fewer records using that code it would be because there are no records in v_GS_SYSTEM_CONSOLE_USER for those resourceIDs.  Which is the source of our problem, but on a much larger scale.




Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.203125