Endpoint Protection

The following code will create a stored procedure on your SQL server for the SEPM database. This stored procedure excepts a single value in the form of a computer name. The ComputerName parameter is currently set to varchar(25) even though, as you probably already know, windows limits computer names to 15 characters.

Make sure you are set to use the SEPM DB and execute the following code:

CREATE PROCEDURE GetScanTime

--//* Create a parameter for user to enter computer name *\\

@ComputerName varchar(25)

AS

--//* Do not return row count * \\

SET NOCOUNT ON;
BEGIN

SELECT

--//* t1=dbo.scans, t2=dbo.sem_computer *\\

    t1.startdatetime AS 'Date',

    t2.computer_name AS 'Computer Name',

    t1.duration AS 'Scan Duration (in seconds)'

FROM

    scans AS t1

--//* Join sem_computer with scans table so we get a human readable computer name *\\

INNER JOIN sem_computer AS t2

ON t2.computer_id = 

    t1.computer_idx

WHERE

--//* including scans where the duration is 0 adds useless rows, excluding them *\\

    t1.duration !='0'

        AND 

            t2.computer_name = @ComputerName
END

GO

You can then run "EXEC GetScanTime 'ComputerNameHere' to see a list of scans that have been performed and their duration (in seconds). This is marginally useful on a day to day basis but could be a nice addition to an Administrator control panel built with workflow.




Also if you are curious about scan times as a whole you can try this:

USE SEPM

SELECT

--//* Gets average scan duration from all scans *\\

    AVG(duration) AS 'Average Scan Time'

FROM

    scans AS T1

WHERE

--//* 0 second scans throw off the average *\\

    T1.duration !='0'

        AND

--//* Other status values are started and cancelled, excluding those *\\

    T1.status ='completed'