tag:blogger.com,1999:blog-15559937.post1350807867922805746..comments2024-02-19T07:53:28.238-05:00Comments on Plain Text Prose: sql query for top user of a computerMarcus Ohhttp://www.blogger.com/profile/16669592705989568859noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-15559937.post-73770831488217541162012-12-13T08:25:20.740-05:002012-12-13T08:25:20.740-05:00hey jason --
i haven't tried this yet... but ...hey jason --<br /><br />i haven't tried this yet... but if you examine my query above, you'll see the query is looking for whatever matches "like" @machine. so... you should be able to provide it a value like...<br /><br />set @machine = '' <br /><br />(two single quotes ^ there)<br /><br />that should tell the query to match anything. see what you get ... Marcus Ohhttps://www.blogger.com/profile/16669592705989568859noreply@blogger.comtag:blogger.com,1999:blog-15559937.post-92057281471307259452012-12-12T00:58:16.175-05:002012-12-12T00:58:16.175-05:00Hi! I came across your blog post trying to figure ...Hi! I came across your blog post trying to figure out a similar report request I have. I need a list of all systems and their TOP user, not a single system. I have tried using the "Select Top 1" subquery but this doesn't seem to work very well and I'm still pretty new to SQL queries so I probably screwed it up. :) Anyway, any help would be very much appreciated!<br /><br />Here is my current query (it looks for a specific collection ID from SCCM, you can ignore this part!)<br /><br />-------------------------------------<br />select<br /> Sys.Name0 AS [Computer Name],<br /> RIGHT((select top 1 (ConsoleUser.SystemConsoleUser0) from v_GS_SYSTEM_CONSOLE_USER),CHARINDEX('\',REVERSE(ConsoleUser.SystemConsoleUser0))-1) AS [Most Frequent User],<br /> Sys.Resource_Domain_OR_Workgr0 AS [Domain], <br /> Sys.AD_Site_Name0 AS [AD Site]<br />from <br /> v_FullCollectionMembership AS FullColl <br /> LEFT JOIN v_R_System AS Sys ON FullColl.ResourceID = Sys.ResourceID <br /> LEFT JOIN v_GS_SYSTEM_CONSOLE_USER AS ConsoleUser ON Sys.ResourceID = ConsoleUser.ResourceID<br />where <br /> ConsoleUser.SystemConsoleUser0 not like '%sx%' AND<br /> FullColl.CollectionID = '00000039'<br />order by Sys.Name0 asc<br /><br />---------------------------------------------<br /><br />Thanks!Jasonhttps://www.blogger.com/profile/16763638841607084067noreply@blogger.com