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.
