We are viewing network reports that show numerous IP addresses that are pulling large bandwidth from our SEPM servers, and are trying to find a trend.
I am creating a custom report that takes an IP address, and queries the SQL database for various information about the SEP client. Here is the SQL query so far:
SELECT dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT],(dbo.SEM_AGENT.FREE_DISK/1073741824)as 'Free Disk Space (GB)',
(dbo.SEM_COMPUTER.DISK_TOTAL/1073741824) as 'Total Disk Space (GB)',
(dbo.SEM_AGENT.FREE_MEM/1073741824) as 'Free Memory (GB)', (dbo.SEM_COMPUTER.MEMORY/1073741824) as 'Total Memory (GB)',
dbo.SEM_COMPUTER.OPERATION_SYSTEM,NAME, SEM_AGENT.MAJOR_VERSION
FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT, dbo.SEM_AGENT
WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
AND dbo.SEM_CLIENT.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID
AND dbo.SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
AND [IP_ADDR1_TEXT] = 'TYPE IN THE IP ADDRESS'
What tables and colums have the date of Antivirus, Proative Threat Protection, Network Threat protection definitions, and the last time the SEP client successfully communicated with the SEPM server?