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 CLIENT"."COMPUTER NAME" "Computer Name" , "SEM AGENT"."
42 Comments - SELECT COMPUTER NAME, IP ADDR1 TEXT, CURRENT LOGIN USER, AV DEF, IPS DEF, SONAR DEF, MRCLEAN DEF, AGENT VERSION, COMPUTER DOMAIN NAME, OPERATION SYSTEM, STATUS, LAST UPDATE TIME, LAST SCAN TIME, FREE MEM, FREE DISK FROM [sem5]
Symantec Endpoint Protection Report fields Version Client Type Operating System Client Version Policy Version Policy Serial Creation Time Status Last time status changed Infected Worst Detection Last Scan Time Last Virus Time Accepts Content Update Computer Name Computer Domain Name Current login domain Service pack IP Address1 Network Threat Protection On Current User Thank you Regards
[SEM CLIENT] WHERE [COMPUTER NAME] in (SELECT SEP Clients FROM SEP ClientToDelete)) GO -- SEM CLIENT DELETE from [sem5]
How to delete particular Computers from SEPM MSSQL Database.doc
2 Comments - no search term matches found in comments.
> - <computers ver="14" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" -xsi:noNamespaceSchemaLocation="computers.xsd"> - <computer> - <name>RACK01C10</name> - <computer name>RACK01C10</computer name> - <asset tag>2559471</asset tag> - <serial num>123419N4HJ</serial num> - <uuid>(43567897-3432-5355-4538-31394E34484A)</uuid> - <nics> - <nic> - <nic id>0</nic id> - <macaddr>001F2964264A</macaddr> - <interfaces> - <interface> - <ip addr>10.104.136.76</ip addr> - </interface> - </interfaces> - </nic> - </nics> - <grouppath> - <group>Marketing</group> - </grouppath> - </computer> - </computers>
[SEM CLIENT] WHERE [COMPUTER NAME] in (SELECT SEP Clients FROM SEP ClientToDelete)) GO -- SEM AGENT DELETE from [sem5]
1 Comment - no search term matches found in comments.
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)
We can query it from the database using either of the two Creation Time/1000, '1/1/1970 00:00')[Creation Time] Or time stamp /86400000 + DATEADD (ms,time stamp%86400000,'19700101') as 'Time Stamp' Example: SELECT computer domain name,computer name,current login user,ip addr1 text,dateadd(s, Last scan time/1000, '1/1/1970 00:00')[Last Scan Time] FROM sem agent INNER JOIN v sem computer ON sem agent.computer id=v sem computer.computer id WHERE last scan time > '0' OR SELECT Computer Name [Computer Name], dateadd(s, Creation Time/1000, '1/1/1970 00:00')[Creation Time] from SEM CLIENT
5 Comments - no search term matches found in comments.
SELECT T2.[computer name] AS "Computer Name", T3...[ip address], T2.[computer name] To create a report in NS using the above queries open your Reports tab
2 attachments
To show all machines in the database run the following query: select client id "Client unique ID",computer name "Host name",hardware key "Hardware ID", isnull((select count(hardware key) from sem client c2 where c1.hardware key=c2.hardware key group by hardware key having count(hardware key)>1),1) "Number of this HWID in the DB",Dateadd(ss, creation time/1000,'19700101') "Client creation time", deleted "To be deleted?"
INSERT INTO @ComputerIDs (NewCompID, OldCompID) SELECT MAX(computer id) AS NewCompID, MIN(computer id) AS OldCompID FROM computer GROUP BY computer name HAVING COUNT(1) = 2 --Now update the younger computer record with all the data from the old computer record --Get the Group ID from the old computer record UPDATE N SET group id = O.group id FROM computer N JOIN @ComputerIDs I ON N.computer id = I.NewCompID JOIN computer O ON I.OldCompID = O.computer id --Delete location from the new record DELETE FROM location WHERE computer id IN ( SELECT NewCompID FROM @ComputerIDs) --Delete service processors from the new record DELETE FROM service processor WHERE computer id IN ( SELECT NewCompID FROM @ComputerIDs) --Move location from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM location T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move service processors from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM service processor T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move config items from the old record to the new record UPDATE T SET target computer id = I.NewCompID FROM config item T JOIN @ComputerIDs I ON T.target computer id = I.OldCompID --Move event schedules from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM event schedule T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move history from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM history T JOIN @ComputerIDs I ON T.computer id = I.OldCompID UPDATE T SET computer id = I.NewCompID FROM history archive T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move status log from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM status log T JOIN @ComputerIDs I ON T.computer id = I.OldCompID UPDATE T SET computer id = I.NewCompID FROM status log archive T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Delete old computer records DELETE FROM computer WHERE computer id IN ( SELECT OldCompID FROM @ComputerIDs)
7 Comments - INSERT INTO @ComputerIDs (NewCompID, OldCompID) SELECT MAX(computer id) AS NewCompID, MIN(computer id) AS OldCompID FROM computer GROUP BY computer name HAVING COUNT(1) = 2 --Now update the younger computer record with all the data from the old computer record --Get the Group ID from the old computer record UPDATE N SET group id = O.group id FROM computer N JOIN @ComputerIDs I ON N.computer id = I.NewCompID JOIN computer O ON I.OldCompID = O.computer id --Delete location from the new record DELETE FROM location WHERE computer id IN (SELECT NewCompID FROM @ComputerIDs) --Delete service processors from the new record DELETE FROM service processor WHERE computer id IN (SELECT NewCompID FROM @ComputerIDs) --Move location from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM location T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move service processors from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM service processor T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move config items from the old record to the new record UPDATE T SET target computer id = I.NewCompID FROM config item T JOIN @ComputerIDs I ON T.target computer id = I.OldCompID --Move event schedules from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM event schedule T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move history from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM history T JOIN @ComputerIDs I ON T.computer id = I.OldCompID UPDATE T SET computer id = I.NewCompID FROM history archive T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Move status log from the old record to the new record UPDATE T SET computer id = I.NewCompID FROM status log T JOIN @ComputerIDs I ON T.computer id = I.OldCompID UPDATE T SET computer id = I.NewCompID FROM status log archive T JOIN @ComputerIDs I ON T.computer id = I.OldCompID --Delete old computer records DELETE FROM computer WHERE computer id IN (SELECT OldCompID FROM @ComputerIDs)