SEP Report with "Last Scan Time" and "Scan Type"

[Migration User]

Hi everyone,

I have been tasked to create a weekly report that will display all users and the last time they had a scan with SEP.  One of the columns in the report needs to be Scan Type so we can tell if the scan was a "scheduled" scan or some other type of scan.  Our scheduled scans are run weekly and are full scans so management wants to know when was the last time all the users had a full system scan.

If there is no built-in report that will provide both of these things then a SQL query would be appreciated.

Thanks in advance.

[Migration User]

I did find a SQL query in other posts that displays "Last Scan Time, but I am unable to include "scan type" without getting errors:

 

SELECT DISTINCT "SEM_AGENT"."DELETED"
  ,"PATTERN"."VERSION"
  ,"PATTERN"."PATTERNDATE"
  ,"SEM_AGENT"."AGENT_VERSION"
  ,"SEM_CLIENT"."COMPUTER_NAME" "Computer Name"
  , "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System"
  ,dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00') CREATION_DTTM
  , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970 00:00:00') Lastupdatetime
  , DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time"
  , "PATTERN"."PATTERNDATE" "Pattern Date"
  , "SEM_CLIENT"."USER_NAME" "User Name"
  , "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address"
  , "IDENTITY_MAP"."NAME" "Group Name"
FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER" "SEM_COMPUTER"
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"
  AND "SEM_AGENT"."DELETED"=0
ORDER BY "Computer Name"

[ℬrίαη]

Go to Reports >> Scheduled Reports >> and click Add

Set Report Type to Scan Report

Set Selected report to Computers by last Scan Time

Edit anything else you need and click OK to save it

That's about the best you will get from SEPM reporting. Scan type won't be included.

[Mithun Sanghavi]

Hello,

Check this Article:

Compilation of SQL queries to the SEPM database

https://www-secure.symantec.com/connect/articles/compilation-sql-queries-sepm-database

SEP Client Information Query. Query result shows:
♦ SEP computername
♦ Installed SEP Version
♦ AV definition revision with the timestamp of the last update
♦ Assignement to SEPM Group
♦ Operating System
♦ Logged-on User
♦ IP address
♦ Last scan time

SELECT DISTINCT "SEM_AGENT"."DELETED"
  , "PATTERN"."VERSION"
  , "SEM_AGENT"."AGENT_VERSION"
  , "SEM_CLIENT"."COMPUTER_NAME" "Computer Name"
  , "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System"
  , dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00') CREATION_DTTM
  , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970 00:00:00') Lastupdatetime
  , dateadd(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time"
  , "PATTERN"."PATTERNDATE" "Pattern Date"
  , "SEM_CLIENT"."USER_NAME" "User Name"
  , "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address"
  , "IDENTITY_MAP"."NAME" "Group Name"
FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT" 
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID") 
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID")) 
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER" "SEM_COMPUTER" 
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID") 
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID")) 
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN" 
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP" 
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER" 
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID" 
  AND "SEM_AGENT"."DELETED"=0
ORDER BY "Computer Name"

 

Hope that helps!!

[Migration User]

Thanks Brian, but I need Scan Type in the results.  The "Last Scan Time" report doesn't have "Scan Type".

[ℬrίαη]

A SQL query will be the only way to go here. SEPM does not have customised reporting.

[Migration User]

Thanks Mithun, I did see that SQL query, and it does work fine but it does not have "Scan Type" as one of its fields.  I have another script that gives me "Scan Type" but I cannot figure out how to combine the two to give me both fields in one report:

SELECT SCANS.STARTDATETIME, SCANS.STOPDATETIME, SCANS.STATUS, SEM_COMPUTER.COMPUTER_NAME, SCANS.CLIENTUSER1, SCANS.CLIENTUSER2, SCANS.SCAN_TYPE, SCANS.TOTALFILES,SCANS.MESSAGE1 AS 'Start Message', SCANS.DURATION
FROM xxxx.dbo.SCANS SCANS, xxxx.dbo.SEM_COMPUTER SEM_COMPUTER
WHERE SCANS.COMPUTER_IDX = SEM_COMPUTER.COMPUTER_ID

If you or someone can combine the two scripts I would greatly appreciate it.

 

[Mithun Sanghavi]

Hello,

In that case, you need to check this Article which would surely assist you - 

Extract SEPM Reports into clear SQL query - A step by step process

https://www-secure.symantec.com/connect/articles/extract-sepm-reports-clear-sql-query-step-step-sepm-hack

Hope that helps!!

[Migration User]

Thanks Mithun, this is definately good to have.  But it really didn't solve my problem.  I did find a report that is revy close to what I want, its called "Scan Failure", it has the following fields:

 

Computers not Recently Scanned: xxx Total Computers except with Legacy Agent: xxx Failure Ratio: 12.31% Maximum Acceptable Failure Ratio: 10%

Computer Name IP Address User Name Last Scan Time

I see it in the "Security Status - Attention Needed" secion of Home (in the SEP console). When you click on "View Details", one of the reports is this one.   Can you give me the SQL query that is run to get this information?