Exclude User from SCCM Query

• michael.laggis

  SSC Veteran

  Points: 212

  More actions

  July 25, 2008 at 2:16 pm

  #381947

  I am a newb in T-SQL. I have an SCCM server and it has a canned query that I want to change. The query looks at the log ins of a workstation and considers the user that logs in the most as the primary user. There is a service account in our domain that logs into machines that is skewing the results. Below is a query and what I would like to be able to do is exclude a user "domain\user" from the results so it selects the next highest user.

  Select temp1.SystemConsoleUser0 as [Primary User],

  Count (temp1.ResourceID) as [Instance Count]

  from (

  Select distinct

  v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0,

  v_GS_SYSTEM_CONSOLE_USER.ResourceID

  from v_GS_SYSTEM_CONSOLE_USER

  left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID

  inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID

  inner join v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID

  where v_FullCollectionMembership.CollectionID = @CollectionID

  AND TotalConsoleTime0 != 0

  And (cast(v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 as Decimal (20,4)))/(cast(v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TotalConsoleTime0 as Decimal(20,4))) >= .66

  ) as temp1

  group by temp1.SystemConsoleUser0

  order by Count (temp1.ResourceID) desc

• michael.laggis

  SSC Veteran

  Points: 212

  More actions

  July 28, 2008 at 9:39 am

  #849245

  Bumped to the third page with out a hint. Help guys.

  Thanks