myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


Report needs to be limited to one record per computer

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Report needs to be limited to one record per computer Page: [1]
Login
Message << Older Topic   Newer Topic >>
Report needs to be limited to one record per computer - 10/14/2008 12:16:15 PM   
khoffman432

 

Posts: 6
Score: 0
Joined: 5/23/2008
Status: offline 		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%'
Post #: 1
RE: Report needs to be limited to one record per computer - 10/14/2008 12:54:37 PM   
okis14

 

Posts: 29
Score: 3
Joined: 12/28/2006
Status: offline 		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.

< Message edited by okis14 -- 10/14/2008 12:58:46 PM >

(in reply to khoffman432)
Post #: 2
RE: Report needs to be limited to one record per computer - 10/14/2008 2:56:21 PM   
jnelson993


Posts: 894
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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%'


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to okis14)
Post #: 3
RE: Report needs to be limited to one record per computer - 10/14/2008 5:04:54 PM   
aparrott

 

Posts: 36
Score: 3
Joined: 12/1/2005
Status: offline 		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. 

(in reply to jnelson993)
Post #: 4
RE: Report needs to be limited to one record per computer - 10/14/2008 5:13:53 PM   
jnelson993


Posts: 894
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to aparrott)
Post #: 5
RE: Report needs to be limited to one record per computer - 10/16/2008 11:44:14 AM   
khoffman432

 

Posts: 6
Score: 0
Joined: 5/23/2008
Status: offline 		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.

(in reply to jnelson993)
Post #: 6
RE: Report needs to be limited to one record per computer - 10/16/2008 1:43:14 PM   
aparrott

 

Posts: 36
Score: 3
Joined: 12/1/2005
Status: offline 		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?

(in reply to jnelson993)
Post #: 7
RE: Report needs to be limited to one record per computer - 10/16/2008 1:58:36 PM   
MeenEnta


Posts: 105
Score: 0
Joined: 9/9/2008
Status: offline 		Is this for SCCM only? do you have the SMS2003 SP3 version ..i.e. the appropriate mof?


(in reply to aparrott)
Post #: 8
RE: Report needs to be limited to one record per computer - 10/16/2008 3:38:07 PM   
khoffman432

 

Posts: 6
Score: 0
Joined: 5/23/2008
Status: offline 		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?



(in reply to MeenEnta)
Post #: 9
RE: Report needs to be limited to one record per computer - 10/21/2008 11:37:08 PM   
jnelson993


Posts: 894
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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.



_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to aparrott)
Post #: 10
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Report needs to be limited to one record per computer Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.782