this probably isn't *quite* right. I was having an issue getting just the 1 console user, and then of course if there isn't a console user grab the name from heartbeat; so it's not really as elegant as I'd want it to be for realz. But this is sorta kinda close-ish.
;with info as (SELECT sys1.Name0 as 'Computer Name' , os.Caption0 as 'Operating System' , ROW_NUMBER() OVER(Partition by sys1.resourceid ORDER BY TotalUserConsoleMinutes0 desc) as UserRankingPerComputer , scu.SystemConsoleUser0, scu.TotalUserConsoleMinutes0, scu.NumberOfConsoleLogons0 , sys1.User_Name0 , ahs.Version as Version , ahs.AntivirusSignatureAge as Age , ahs.AntivirusSignatureVersion as DatVersion , ahs.LastQuickScanDateTimeStart , ahs.LastQuickScanDateTimeEnd , ahs.LastFullScanDateTimeStart , ahs.LastFullScanDateTimeEnd , ahs.LastQuickScanAge as 'Days since last quick scan' , ahs.LastFullScanAge as 'Days since last full scan' , cs.LastActiveTime , cs.LastPolicyRequest , cs.LastHW , cs.LastSW FROM v_R_System sys1 left join v_GS_AntimalwareHealthStatus AHS on ahs.resourceid=sys1.resourceid left JOIN v_GS_OPERATING_SYSTEM os ON os.ResourceID = sys1.ResourceID left join v_CH_ClientSummary cs on cs.resourceid=sys1.ResourceID left join v_GS_SYSTEM_CONSOLE_USER scu on scu.ResourceID=sys1.ResourceID group by sys1.resourceid, sys1.Name0, os.Caption0,scu.SystemConsoleUser0, scu.TotalUserConsoleMinutes0, scu.NumberOfConsoleLogons0 , sys1.user_name0, ahs.Version ,ahs.AntivirusSignatureAge , ahs.AntivirusSignatureVersion , ahs.LastQuickScanDateTimeStart , ahs.LastQuickScanDateTimeEnd , ahs.LastFullScanDateTimeStart , ahs.LastFullScanDateTimeEnd , ahs.LastQuickScanAge , ahs.LastFullScanAge , cs.LastActiveTime , cs.LastPolicyRequest , cs.LastHW , cs.LastSW ) select * from info where UserRankingPerComputer =1 On Wed, May 11, 2016 at 11:14 AM, Murray, Mike <[email protected]> wrote: > :bump: > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Murray, Mike > *Sent:* Monday, May 9, 2016 10:57 AM > *To:* [email protected] > *Subject:* [mssms] SQL query help? > > > > Good morning, > > > > Could some help me add the following to this query I am using to pull EP > data into Excel? > > > > Client: > > > > Last Active Time > > Last Policy Request > > Last Software Scan > > Last Hardware Scan > > > > User: > > > > User > > Top Console User > > > > > > Existing Query: > > > > SELECT v_R_System.Name0 as 'Computer Name', v_GS_OPERATING_SYSTEM.Caption0 > as 'Operating System', v_GS_AntimalwareHealthStatus.Version as Version, > v_GS_AntimalwareHealthStatus.AntivirusSignatureAge as Age, > v_GS_AntimalwareHealthStatus.AntivirusSignatureVersion as DatVersion, > v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeStart, > v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeEnd, > v_GS_AntimalwareHealthStatus.LastFullScanDateTimeStart, > v_GS_AntimalwareHealthStatus.LastFullScanDateTimeEnd, > v_GS_AntimalwareHealthStatus.LastQuickScanAge as 'Days since last quick > scan', v_GS_AntimalwareHealthStatus.LastFullScanAge as 'Days since last > full scan' > > > > FROM v_GS_AntimalwareHealthStatus > > > > INNER JOIN v_R_System ON v_GS_AntimalwareHealthStatus.ResourceID = > v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON > v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID > > > > > > Best Regards, > > > > Mike Murray > > Desktop Management Coordinator - IT Support Services > > California State University, Chico > > 530.898.4357 > [email protected] > > > > > > -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger, http://www.smguru.org
