Thanks Ken, I'll check that :) Stuart Watret
Offshore - IT Ltd ________________________________________ From: [email protected] <[email protected]> on behalf of Lutz, Ken <[email protected]> Sent: 31 October 2014 15:06 To: [email protected] Subject: [mssms] RE: Average Scan Times I got the following SQL from Todd Hemsell (I think) ... SELECT Distinct VRS.Netbios_Name0 as 'Computer Name', (SUM(LD.Size0) - SUM(ld.FreeSpace0)) / DATEDIFF(HOUR,HS.LastFullScanDateTimeStart,HS.LastFullScanDateTimeEnd) AS [Full Scan MB Scanned Per Hour], (SUM(LD.Size0) - SUM(ld.FreeSpace0)) / DATEDIFF(HOUR,HS.LastQuickScanDateTimeStart,HS.LastQuickScanDateTimeEnd) AS [Quick Scan MB Scanned Per Hour], (SUM(LD.Size0) - SUM(ld.FreeSpace0)) / 1024 AS [GBDataSize], DATEDIFF(HOUR,HS.LastQuickScanDateTimeStart,HS.LastQuickScanDateTimeEnd) AS [LastQuickScanDurationInHours], DATEDIFF(HOUR,HS.LastFullScanDateTimeStart,HS.LastFullScanDateTimeEnd) AS [LastFullScanDurationInHours], OS.TotalVisibleMemorySize0 AS [TotalVisibleMemory], P.Name0 AS [CPU], SUBSTRING( convert(varchar, DATEADD(ss,SUM(datediff(ss,HS.LastQuickScanDateTimeStart, HS.LastQuickScanDateTimeEnd)),CAST('00:00:00' AS TIME)),108),1,5) AS [LastQuickScanDuration], SUBSTRING( convert(varchar, DATEADD(ss,SUM(datediff(ss,HS.LastFullScanDateTimeStart, HS.LastFullScanDateTimeEnd)),CAST('00:00:00' AS TIME)),108),1,5) AS [LastFullScanDuration], HS.LastQuickScanDateTimeStart, HS.LastQuickScanDateTimeEnd, HS.LastFullScanDateTimeStart, HS.LastFullScanDateTimeEnd, HS.LastQuickScanAge as 'Days since last quick scan', HS.LastFullScanAge as 'Days since last full scan', OS.Caption0 as 'Operating System' FROM v_R_System VRS Left Join v_GS_LOGICAL_DISK LD on LD.ResourceID = VRS.ResourceID INNER JOIN v_GS_AntimalwareHealthStatus HS ON HS.ResourceID = VRS.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM OS ON VRS.ResourceID = OS.ResourceID Left Join v_GS_PROCESSOR P on P.ResourceID = VRS.ResourceID Where LD.Name0 NOT IN('Q:','A:') AND (DATEDIFF(HOUR,HS.LastQuickScanDateTimeStart,HS.LastQuickScanDateTimeEnd) IS Not Null OR DATEDIFF(HOUR,HS.LastFullScanDateTimeStart,HS.LastFullScanDateTimeEnd) Is Not Null) AND LD.Size0 Is Not Null AND DATEDIFF(HOUR,HS.LastFullScanDateTimeStart,HS.LastFullScanDateTimeEnd) > 0 AND DATEDIFF(HOUR,HS.LastQuickScanDateTimeStart,HS.LastQuickScanDateTimeEnd) > 0 Group By VRS.Netbios_Name0, OS.TotalVisibleMemorySize0, P.Name0, OS.Caption0, HS.LastQuickScanDateTimeStart, HS.LastQuickScanDateTimeEnd, HS.LastFullScanDateTimeStart, HS.LastFullScanDateTimeEnd, HS.LastQuickScanAge, HS.LastFullScanAge Order By [LastQuickScanDurationInHours] Desc Thanks, Ken ... -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Stuart Watret Sent: Friday, October 31, 2014 6:05 AM To: [email protected] Subject: [mssms] Average Scan Times Trying to collect scan times from our desktop fep estate. Before I reinvent the wheel, anyone have a script I could deploy to collect; Compname/Model/FullScanStart and End time. I realise the system event log holds this as events 1000 and 1001 respectively. Cheers Sent from a dog and bone.
