You rock, Sherry. Thanks!
From: [email protected] [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Wednesday, May 11, 2016 12:47 PM To: [email protected] Subject: Re: [mssms] RE: SQL query help? 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] <mailto:[email protected]> > wrote: :bump: From: [email protected] <mailto:[email protected]> [mailto:[email protected] <mailto:[email protected]> ] On Behalf Of Murray, Mike Sent: Monday, May 9, 2016 10:57 AM To: [email protected] <mailto:[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 <tel:530.898.4357> [email protected] <mailto:[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
smime.p7s
Description: S/MIME cryptographic signature
