Developing: determine how quickly data in one table is being completly updated with new inserts.

5/10/2013 - 7:19 PM
Developing: determine how quickly data in one table is being completly updated with new inserts.



--CREATE AND SELECT FOR TEMPTABLE1
/*
  IF OBJECT_ID('tempdb.dbo.#TempTable1' ) IS NOT NULL
  BEGIN
  DROP TABLE #TempTable1
  END


CREATE TABLE #TempTable1(
  [DOMAIN_ID] [char](32) NOT NULL,
	[SITE_ID] [char](32) NOT NULL,
	[SERVER_ID] [char](32) NOT NULL,
	[GROUP_ID] [char](32) NOT NULL,
	[COMPUTER_ID] [char](32) NOT NULL,
	[TIME_STAMP] [bigint] NOT NULL,
	[EVENT_ID] [int] NOT NULL,
	[EVENT_TIME] [bigint] NOT NULL,
	[SEVERITY] [int] NOT NULL,
	[AGENT_ID] [char](32) NULL,
	[HARDWARE_KEY] [char](32) NULL,
	[HOST_NAME] [nvarchar](256) NULL,
	[LOCAL_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_NAME] [nvarchar](64) NULL,
	[NETWORK_PROTOCOL] [tinyint] NULL,
	[TRAFFIC_DIRECTION] [tinyint] NULL,
	[HACK_TYPE] [int] NOT NULL,
	[BEGIN_TIME] [bigint] NULL,
	[END_TIME] [bigint] NULL,
	[REPETITION] [int] NULL,
	[APP_NAME] [nvarchar](256) NULL,
	[EVENT_DESC] [nvarchar](2000) NULL,
	[EVENT_DATA] [varbinary](3000) NULL,
	[ALERT] [tinyint] NULL,
	[SEND_SNMP_TRAP] [tinyint] NULL,
	[LOCAL_HOST_MAC] [varchar](18) NULL,
	[REMOTE_HOST_MAC] [varchar](18) NULL,
	[LOCATION_NAME] [nvarchar](256) NULL,
	[USER_NAME] [nvarchar](256) NULL,
	[DOMAIN_NAME] [nvarchar](256) NULL,
	[AGENT_SECURITY_LOG_IDX] [char](32) NULL,
	[LOCAL_PORT] [int] NOT NULL,
	[REMOTE_PORT] [int] NOT NULL,
	[CIDS_SIGN_ID] [bigint] NOT NULL,
	[STR_CIDS_SIGN_ID] [nvarchar](520) NOT NULL,
	[CIDS_SIGN_SUB_ID] [bigint] NOT NULL,
	[INTRUSION_URL] [nvarchar](4000) NOT NULL,
	[INTRUSION_PAYLOAD_URL] [nvarchar](256) NULL,
	[HI_EXECUTION_ID] [nvarchar](50) NULL,
	[AGENT_VERSION] [varchar](64) NULL,
	[PROFILE_SERIAL_NO] [nvarchar](64) NULL,
	[SERVER_NAME] [nvarchar](2000) NULL,
	[SITE_NAME] [nvarchar](2000) NULL
) ON [PRIMARY]

GO

INSERT INTO #TempTable1
	(
	  [DOMAIN_ID],
      [SITE_ID],
      [SERVER_ID], 
      [GROUP_ID], 
      [COMPUTER_ID], 
      [TIME_STAMP], 
      [EVENT_ID], 
      [EVENT_TIME], 
      [SEVERITY], 
      [AGENT_ID], 
      [HARDWARE_KEY],
      [HOST_NAME], 
      [LOCAL_HOST_IP],
      [REMOTE_HOST_IP], 
      [REMOTE_HOST_NAME],
	  [NETWORK_PROTOCOL],
	  [TRAFFIC_DIRECTION],
	  [HACK_TYPE],
      [BEGIN_TIME],
      [END_TIME],
      [REPETITION],
      [APP_NAME],
      [EVENT_DESC],
      [EVENT_DATA], 
      [ALERT],
      [SEND_SNMP_TRAP],
	  [LOCAL_HOST_MAC],
	  [REMOTE_HOST_MAC],
	  [LOCATION_NAME], 
      [USER_NAME], 
	  [DOMAIN_NAME],
      [AGENT_SECURITY_LOG_IDX], 
      [LOCAL_PORT], 
      [REMOTE_PORT], 
      [CIDS_SIGN_ID], 
      [STR_CIDS_SIGN_ID], 
      [CIDS_SIGN_SUB_ID],
	  [INTRUSION_URL],
	  [INTRUSION_PAYLOAD_URL],
	  [HI_EXECUTION_ID],
	  [AGENT_VERSION],
	  [PROFILE_SERIAL_NO],
	  [SERVER_NAME],
	  [SITE_NAME]
	)
	SELECT log.DOMAIN_ID, SITE_ID, SERVER_ID, GROUP_ID, COMPUTER_ID, TIME_STAMP, EVENT_ID, EVENT_TIME, SEVERITY, AGENT_ID, 
		HARDWARE_KEY, HOST_NAME, LOCAL_HOST_IP, REMOTE_HOST_IP, REMOTE_HOST_NAME, NETWORK_PROTOCOL, TRAFFIC_DIRECTION, 
		HACK_TYPE, BEGIN_TIME, END_TIME, REPETITION, APP_NAME, EVENT_DESC, EVENT_DATA, ALERT, SEND_SNMP_TRAP, 
		LOCAL_HOST_MAC, REMOTE_HOST_MAC, LOCATION_NAME, USER_NAME, DOMAIN_NAME, AGENT_SECURITY_LOG_IDX, 
		LOCAL_PORT, REMOTE_PORT, CIDS_SIGN_ID, STR_CIDS_SIGN_ID, CIDS_SIGN_SUB_ID, INTRUSION_URL, INTRUSION_PAYLOAD_URL, 
		HI_EXECUTION_ID, AGENT_VERSION, PROFILE_SERIAL_NO, server.NAME as SERVER_NAME, site.NAME as SITE_NAME 
     FROM 
	 ( 
			SELECT * 
			FROM [SEPMD02_APAC].[sem5].[dbo].[AGENT_SECURITY_LOG_1] 
			UNION ALL
			SELECT * 
			FROM [SEPMD02_APAC].[sem5].[dbo].[AGENT_SECURITY_LOG_2] 
      ) as log 
	  LEFT JOIN [SEPMD02_APAC].[sem5].[dbo].[IDENTITY_MAP] as site on log.SITE_ID=site.ID and log.DOMAIN_ID=site.DOMAIN_ID 
	  LEFT JOIN [SEPMD02_APAC].[sem5].[dbo].[IDENTITY_MAP] as server on log.SERVER_ID=server.ID and log.DOMAIN_ID=server.DOMAIN_ID 
*/



----CREATE AND SELECT FOR TEMPTABLE2

--/*


  IF OBJECT_ID('tempdb.dbo.#TempTable2' ) IS NOT NULL
	BEGIN
	DROP TABLE #TempTable2
	END


CREATE TABLE #TempTable2(
	[DOMAIN_ID] [char](32) NOT NULL,
	[SITE_ID] [char](32) NOT NULL,
	[SERVER_ID] [char](32) NOT NULL,
	[GROUP_ID] [char](32) NOT NULL,
	[COMPUTER_ID] [char](32) NOT NULL,
	[TIME_STAMP] [bigint] NOT NULL,
	[EVENT_ID] [int] NOT NULL,
	[EVENT_TIME] [bigint] NOT NULL,
	[SEVERITY] [int] NOT NULL,
	[AGENT_ID] [char](32) NULL,
	[HARDWARE_KEY] [char](32) NULL,
	[HOST_NAME] [nvarchar](256) NULL,
	[LOCAL_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_NAME] [nvarchar](64) NULL,
	[NETWORK_PROTOCOL] [tinyint] NULL,
	[TRAFFIC_DIRECTION] [tinyint] NULL,
	[HACK_TYPE] [int] NOT NULL,
	[BEGIN_TIME] [bigint] NULL,
	[END_TIME] [bigint] NULL,
	[REPETITION] [int] NULL,
	[APP_NAME] [nvarchar](256) NULL,
	[EVENT_DESC] [nvarchar](2000) NULL,
	[EVENT_DATA] [varbinary](3000) NULL,
	[ALERT] [tinyint] NULL,
	[SEND_SNMP_TRAP] [tinyint] NULL,
	[LOCAL_HOST_MAC] [varchar](18) NULL,
	[REMOTE_HOST_MAC] [varchar](18) NULL,
	[LOCATION_NAME] [nvarchar](256) NULL,
	[USER_NAME] [nvarchar](256) NULL,
	[DOMAIN_NAME] [nvarchar](256) NULL,
	[AGENT_SECURITY_LOG_IDX] [char](32) NULL,
	[LOCAL_PORT] [int] NOT NULL,
	[REMOTE_PORT] [int] NOT NULL,
	[CIDS_SIGN_ID] [bigint] NOT NULL,
	[STR_CIDS_SIGN_ID] [nvarchar](520) NOT NULL,
	[CIDS_SIGN_SUB_ID] [bigint] NOT NULL,
	[INTRUSION_URL] [nvarchar](4000) NOT NULL,
	[INTRUSION_PAYLOAD_URL] [nvarchar](256) NULL,
	[HI_EXECUTION_ID] [nvarchar](50) NULL,
	[AGENT_VERSION] [varchar](64) NULL,
	[PROFILE_SERIAL_NO] [nvarchar](64) NULL,
	[SERVER_NAME] [nvarchar](2000) NULL,
	[SITE_NAME] [nvarchar](2000) NULL
) ON [PRIMARY]

GO

INSERT INTO #TempTable2
	(
	  [DOMAIN_ID],
      [SITE_ID],
      [SERVER_ID], 
      [GROUP_ID], 
      [COMPUTER_ID], 
      [TIME_STAMP], 
      [EVENT_ID], 
      [EVENT_TIME], 
      [SEVERITY], 
      [AGENT_ID], 
      [HARDWARE_KEY],
      [HOST_NAME], 
      [LOCAL_HOST_IP],
      [REMOTE_HOST_IP], 
      [REMOTE_HOST_NAME],
	  [NETWORK_PROTOCOL],
	  [TRAFFIC_DIRECTION],
	  [HACK_TYPE],
      [BEGIN_TIME],
      [END_TIME],
      [REPETITION],
      [APP_NAME],
      [EVENT_DESC],
      [EVENT_DATA], 
      [ALERT],
      [SEND_SNMP_TRAP],
	  [LOCAL_HOST_MAC],
	  [REMOTE_HOST_MAC],
	  [LOCATION_NAME], 
      [USER_NAME], 
	  [DOMAIN_NAME],
      [AGENT_SECURITY_LOG_IDX], 
      [LOCAL_PORT], 
      [REMOTE_PORT], 
      [CIDS_SIGN_ID], 
      [STR_CIDS_SIGN_ID], 
      [CIDS_SIGN_SUB_ID],
	  [INTRUSION_URL],
	  [INTRUSION_PAYLOAD_URL],
	  [HI_EXECUTION_ID],
	  [AGENT_VERSION],
	  [PROFILE_SERIAL_NO],
	  [SERVER_NAME],
	  [SITE_NAME]
	)
	SELECT log.DOMAIN_ID, SITE_ID, SERVER_ID, GROUP_ID, COMPUTER_ID, TIME_STAMP, EVENT_ID, EVENT_TIME, SEVERITY, AGENT_ID, 
		HARDWARE_KEY, HOST_NAME, LOCAL_HOST_IP, REMOTE_HOST_IP, REMOTE_HOST_NAME, NETWORK_PROTOCOL, TRAFFIC_DIRECTION, 
		HACK_TYPE, BEGIN_TIME, END_TIME, REPETITION, APP_NAME, EVENT_DESC, EVENT_DATA, ALERT, SEND_SNMP_TRAP, 
		LOCAL_HOST_MAC, REMOTE_HOST_MAC, LOCATION_NAME, USER_NAME, DOMAIN_NAME, AGENT_SECURITY_LOG_IDX, 
		LOCAL_PORT, REMOTE_PORT, CIDS_SIGN_ID, STR_CIDS_SIGN_ID, CIDS_SIGN_SUB_ID, INTRUSION_URL, INTRUSION_PAYLOAD_URL, 
		HI_EXECUTION_ID, AGENT_VERSION, PROFILE_SERIAL_NO, server.NAME as SERVER_NAME, site.NAME as SITE_NAME 
     FROM 
	 ( 
			SELECT * 
			FROM [SEPMD02_APAC].[sem5].[dbo].[AGENT_SECURITY_LOG_1] 
			UNION ALL
			SELECT * 
			FROM [SEPMD02_APAC].[sem5].[dbo].[AGENT_SECURITY_LOG_2] 
      ) as log 
	  LEFT JOIN [SEPMD02_APAC].[sem5].[dbo].[IDENTITY_MAP] as site on log.SITE_ID=site.ID and log.DOMAIN_ID=site.DOMAIN_ID 
	  LEFT JOIN [SEPMD02_APAC].[sem5].[dbo].[IDENTITY_MAP] as server on log.SERVER_ID=server.ID and log.DOMAIN_ID=server.DOMAIN_ID 


--*/



--CREATE AND SELECT FOR TEMPTABLE3

			IF OBJECT_ID('tempdb.dbo.#TempTable3' ) IS NOT NULL
			BEGIN
			DROP TABLE #TempTable3
			END


CREATE TABLE #TempTable3(
	[DOMAIN_ID] [char](32) NOT NULL,
	[SITE_ID] [char](32) NOT NULL,
	[SERVER_ID] [char](32) NOT NULL,
	[GROUP_ID] [char](32) NOT NULL,
	[COMPUTER_ID] [char](32) NOT NULL,
	[TIME_STAMP] [bigint] NOT NULL,
	[EVENT_ID] [int] NOT NULL,
	[EVENT_TIME] [bigint] NOT NULL,
	[SEVERITY] [int] NOT NULL,
	[AGENT_ID] [char](32) NULL,
	[HARDWARE_KEY] [char](32) NULL,
	[HOST_NAME] [nvarchar](256) NULL,
	[LOCAL_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_IP] [bigint] NULL,
	[REMOTE_HOST_NAME] [nvarchar](64) NULL,
	[NETWORK_PROTOCOL] [tinyint] NULL,
	[TRAFFIC_DIRECTION] [tinyint] NULL,
	[HACK_TYPE] [int] NOT NULL,
	[BEGIN_TIME] [bigint] NULL,
	[END_TIME] [bigint] NULL,
	[REPETITION] [int] NULL,
	[APP_NAME] [nvarchar](256) NULL,
	[EVENT_DESC] [nvarchar](2000) NULL,
	[EVENT_DATA] [varbinary](3000) NULL,
	[ALERT] [tinyint] NULL,
	[SEND_SNMP_TRAP] [tinyint] NULL,
	[LOCAL_HOST_MAC] [varchar](18) NULL,
	[REMOTE_HOST_MAC] [varchar](18) NULL,
	[LOCATION_NAME] [nvarchar](256) NULL,
	[USER_NAME] [nvarchar](256) NULL,
	[DOMAIN_NAME] [nvarchar](256) NULL,
	[AGENT_SECURITY_LOG_IDX] [char](32) NULL,
	[LOCAL_PORT] [int] NOT NULL,
	[REMOTE_PORT] [int] NOT NULL,
	[CIDS_SIGN_ID] [bigint] NOT NULL,
	[STR_CIDS_SIGN_ID] [nvarchar](520) NOT NULL,
	[CIDS_SIGN_SUB_ID] [bigint] NOT NULL,
	[INTRUSION_URL] [nvarchar](4000) NOT NULL,
	[INTRUSION_PAYLOAD_URL] [nvarchar](256) NULL,
	[HI_EXECUTION_ID] [nvarchar](50) NULL,
	[AGENT_VERSION] [varchar](64) NULL,
	[PROFILE_SERIAL_NO] [nvarchar](64) NULL,
	[SERVER_NAME] [nvarchar](2000) NULL,
	[SITE_NAME] [nvarchar](2000) NULL
) ON [PRIMARY]

GO


--insert into NEW TEMPTABLE what is in temptable2 that is not in temptable1
--assumption-a record in temp2 that is not in temp1 is new



INSERT INTO #TempTable3
(
      [DOMAIN_ID],
      [SITE_ID],
      [SERVER_ID], 
      [GROUP_ID], 
      [COMPUTER_ID], 
      [TIME_STAMP], 
      [EVENT_ID], 
      [EVENT_TIME], 
      [SEVERITY], 
      [AGENT_ID], 
      [HARDWARE_KEY],
      [HOST_NAME], 
      [LOCAL_HOST_IP],
      [REMOTE_HOST_IP], 
      [REMOTE_HOST_NAME],
	  [NETWORK_PROTOCOL],
	  [TRAFFIC_DIRECTION],
	  [HACK_TYPE],
      [BEGIN_TIME],
      [END_TIME],
      [REPETITION],
      [APP_NAME],
      [EVENT_DESC],
      [EVENT_DATA], 
      [ALERT],
      [SEND_SNMP_TRAP],
	  [LOCAL_HOST_MAC],
	  [REMOTE_HOST_MAC],
	  [LOCATION_NAME], 
      [USER_NAME], 
	  [DOMAIN_NAME],
      [AGENT_SECURITY_LOG_IDX], 
      [LOCAL_PORT], 
      [REMOTE_PORT], 
      [CIDS_SIGN_ID], 
      [STR_CIDS_SIGN_ID], 
      [CIDS_SIGN_SUB_ID],
	  [INTRUSION_URL],
	  [INTRUSION_PAYLOAD_URL],
	  [HI_EXECUTION_ID],
	  [AGENT_VERSION],
	  [PROFILE_SERIAL_NO],
	  [SERVER_NAME],
	  [SITE_NAME]
)

SELECT 
      DOMAIN_ID,
      SITE_ID,
      SERVER_ID, 
      GROUP_ID, 
      COMPUTER_ID, 
      TIME_STAMP, 
      EVENT_ID, 
      EVENT_TIME, 
      SEVERITY, 
      AGENT_ID, 
      HARDWARE_KEY,
      HOST_NAME, 
      LOCAL_HOST_IP,
      REMOTE_HOST_IP, 
      REMOTE_HOST_NAME,
	  NETWORK_PROTOCOL,
	  TRAFFIC_DIRECTION,
	  HACK_TYPE,
      BEGIN_TIME,
      END_TIME,
      REPETITION,
      APP_NAME,
      EVENT_DESC,
      EVENT_DATA, 
      ALERT,
      SEND_SNMP_TRAP,
	  LOCAL_HOST_MAC,
	  REMOTE_HOST_MAC,
	  LOCATION_NAME, 
      USER_NAME, 
	  DOMAIN_NAME,
      AGENT_SECURITY_LOG_IDX, 
      LOCAL_PORT, 
      REMOTE_PORT, 
      CIDS_SIGN_ID, 
      STR_CIDS_SIGN_ID, 
      CIDS_SIGN_SUB_ID,
	  INTRUSION_URL,
	  INTRUSION_PAYLOAD_URL,
	  HI_EXECUTION_ID,
	  AGENT_VERSION,
	  PROFILE_SERIAL_NO,
	  SERVER_NAME,
	  SITE_NAME
         
FROM

#TempTable2 a

where NOT EXISTS 

( SELECT 
      a.DOMAIN_ID,
      a.SITE_ID,
      a.SERVER_ID, 
      a.GROUP_ID, 
      a.COMPUTER_ID, 
      a.TIME_STAMP, 
      a.EVENT_ID, 
      a.EVENT_TIME, 
      a.SEVERITY, 
      a.AGENT_ID, 
      a.HARDWARE_KEY,
      a.HOST_NAME, 
      a.LOCAL_HOST_IP,
      a.REMOTE_HOST_IP, 
      a.REMOTE_HOST_NAME,
	  a.NETWORK_PROTOCOL,
	  a.TRAFFIC_DIRECTION,
	  a.HACK_TYPE,
      a.BEGIN_TIME,
      a.END_TIME,
      a.REPETITION,
      a.APP_NAME,
      a.EVENT_DESC,
      a.EVENT_DATA, 
      a.ALERT,
      a.SEND_SNMP_TRAP,
	  a.LOCAL_HOST_MAC,
	  a.REMOTE_HOST_MAC,
	  a.LOCATION_NAME, 
      a.USER_NAME, 
	  a.DOMAIN_NAME,
      a.AGENT_SECURITY_LOG_IDX, 
      a.LOCAL_PORT, 
      a.REMOTE_PORT, 
      a.CIDS_SIGN_ID, 
      a.STR_CIDS_SIGN_ID, 
      a.CIDS_SIGN_SUB_ID,
	  a.INTRUSION_URL,
	  a.INTRUSION_PAYLOAD_URL,
	  a.HI_EXECUTION_ID,
	  a.AGENT_VERSION,
	  a.PROFILE_SERIAL_NO,
	  a.SERVER_NAME,
	  a.SITE_NAME
INTERSECT

SELECT

      DOMAIN_ID,
      SITE_ID,
      SERVER_ID, 
      GROUP_ID, 
      COMPUTER_ID, 
      TIME_STAMP, 
      EVENT_ID, 
      EVENT_TIME, 
      SEVERITY, 
      AGENT_ID, 
      HARDWARE_KEY,
      HOST_NAME, 
      LOCAL_HOST_IP,
      REMOTE_HOST_IP, 
      REMOTE_HOST_NAME,
	  NETWORK_PROTOCOL,
	  TRAFFIC_DIRECTION,
	  HACK_TYPE,
      BEGIN_TIME,
      END_TIME,
      REPETITION,
      APP_NAME,
      EVENT_DESC,
      EVENT_DATA, 
      ALERT,
      SEND_SNMP_TRAP,
	  LOCAL_HOST_MAC,
	  REMOTE_HOST_MAC,
	  LOCATION_NAME, 
      USER_NAME, 
	  DOMAIN_NAME,
      AGENT_SECURITY_LOG_IDX, 
      LOCAL_PORT, 
      REMOTE_PORT, 
      CIDS_SIGN_ID, 
      STR_CIDS_SIGN_ID, 
      CIDS_SIGN_SUB_ID,
	  INTRUSION_URL,
	  INTRUSION_PAYLOAD_URL,
	  HI_EXECUTION_ID,
	  AGENT_VERSION,
	  PROFILE_SERIAL_NO,
	  SERVER_NAME,
	  SITE_NAME
        
        from #TempTable1
  )
  
  
  
select COUNT (*) from #TempTable1

select COUNT (*) from #TempTable2

select COUNT (*) from #TempTable3

USE [SEPM]
GO
Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

Developing: determine how quickly data in one table is being completly updated with new inserts.
