分析DHCP Log，对比出未加域的黑名单

SQL脚本如下：

USE [DHCP]
GO
/****** Object:  StoredProcedure [dbo].[usp_DHCP_Blacklist]    Script Date: 2016/8/10 15:36:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================
-- Author:		Burgess
-- Create date: 2016/4/15
-- Description:	分析DHCP Log，对比出未加域的黑名单
-- ===============================================
ALTER PROCEDURE [dbo].[usp_DHCP_Blacklist]
AS
BEGIN
	SET NOCOUNT ON;
	--声明变量
	declare @cmd varchar(2000),
			@sqlcmd varchar(2000),
			@SharePth varchar(200),
			@FileName varchar(50) ,
			@FilePath varchar(200), 
			@OutFileName varchar(100),		
			@OutFilePath varchar(300),
			@CurrentRow int,
			@BeginRow varchar(10)
			
	--设置默认值
	set language N'English'
	set @FileName = 'DhcpSrvLog-'+LEFT(DATENAME(WEEKDAY,GETDATE()),3)+'.log'
	IF LEFT(DATENAME(WEEKDAY,GETDATE()),3)='Fri'
		set @FileName='DhcpSrvLog-Sat.log'
	--set @FileName = 'DhcpSrvLog-Mon.log'
	set @SharePth = '\\dhcp-sh1\dhcp\'
	set @FilePath='D:\'
	set @OutFilePath='D:\'
	set @OutFileName='BlackList-'+CONVERT(VARCHAR(8), GETDATE(), 112)+REPLACE(CONVERT(VARCHAR, GETDATE(), 108),':','')+'.xls'
	
	SELECT @CurrentRow=COUNT(1) FROM RAWDATA WHERE GenDate=CONVERT(VARCHAR,GETDATE(),1)
	IF @CurrentRow=0
		UPDATE ROWNO SET CurrentRow=2 WHERE ID=1
	ELSE
		UPDATE ROWNO SET CurrentRow+=@CurrentRow WHERE ID=1

	SELECT @BeginRow=CurrentRow FROM ROWNO WHERE ID=1

	print 'Begin:'+CONVERT(varchar(15), GETDATE(), 114)
	--清理表RAWDATA
	TRUNCATE TABLE RAWDATA
	
	--copy file
	set @cmd='copy '+ @SharePth+@FileName+' '+@FilePath+@FileName
	EXEC master..xp_cmdshell @cmd --, no_output
	print 'Copy log file finished:'+CONVERT(varchar(15), GETDATE(), 114)
	--解析log文件，将数据存入表RAWDATA  --''D:\DhcpSrvLog-Mon.log''
	set @cmd='BULK INSERT DHCP.DBO.RAWDATA 
	FROM '''+@FilePath+ @FileName+'''
	WITH(MAXERRORS  = 0 ,
	FIELDTERMINATOR ='','',
	FIRSTROW ='+@BeginRow+',
	ROWTERMINATOR=''\n'',
	--KEEPNULLS,
	TABLOCK
	)'
	
	print @cmd
	execute (@cmd)
	print 'Parse log file finished:'+CONVERT(varchar(15), GETDATE(), 114)
	--提取各HostName的最新一笔记录
	;WITH A AS(
	SELECT ROW_NUMBER() OVER(PARTITION BY HOSTNAME ORDER BY GENTIME DESC) RID,ID,GENDATE,GENTIME,IPADDR,HOSTNAME,MACADDR FROM RAWDATA WHERE HostName IS NOT NULL 
	)
	SELECT ID,GENDATE,GENTIME,IPADDR,HOSTNAME,MACADDR INTO #DHCP FROM A WHERE RID=1

	;WITH B AS(
	SELECT ROW_NUMBER() OVER(PARTITION BY MACADDR ORDER BY GENTIME DESC) RID,ID,GENDATE,GENTIME,IPADDR,MACADDR AS HOSTNAME,MACADDR FROM RAWDATA WHERE HostName IS NULL AND MACADDR IS NOT NULL AND LEN(MACADDR)<=18
	)
	INSERT #DHCP 
	SELECT B.ID,B.GENDATE,B.GENTIME,B.IPADDR,B.HOSTNAME,B.MACADDR FROM B LEFT JOIN #DHCP D ON B.MACAddr=D.MACAddr WHERE B.RID=1 AND D.MACAddr IS NULL 
	print 'Process raw data finished: '+CONVERT(varchar(15), GETDATE(), 114)
	--将数据同步到存放所有联网的主机信息表DHCP
	MERGE INTO DHCP AS A 
	USING #DHCP AS B
	ON A.HOSTNAME=B.HOSTNAME 
	WHEN MATCHED THEN
		UPDATE SET A.ID=B.ID, A.GENDATE=B.GENDATE,A.GENTIME=B.GENTIME,A.IPADDR=B.IPADDR,A.MACADDR=B.MACADDR 
	WHEN NOT MATCHED THEN
		INSERT (ID, GenDate, GenTime, IPAddr, HostName, MACAddr) 
		VALUES(B.ID, B.GenDate, B.GenTime, B.IPAddr, B.HostName, B.MACAddr);
	print 'Synchronize data finished: '+CONVERT(varchar(15), GETDATE(), 114)
	--将黑名单导出
--	Set @sqlCmd= 'BCP "SELECT ID,GENDATE,GENTIME,IPADDR,D.HOSTNAME,D.MACADDR 
--FROM DBO.DHCP D WITH(NOLOCK) LEFT JOIN dbo.Workstations W WITH(NOLOCK) 
--ON REPLACE(D.HostName,'.abcd.com','')=W.NAME WHERE D.ID<>15 AND D.HostName<>D.MACAddr AND W.NAME IS NULL 
--AND D.HostName NOT LIKE 'android%' AND D.HostName NOT LIKE '%iphone%'
--AND D.HostName NOT LIKE '%ipad%' AND D.HostName NOT LIKE 'MiBOX%'
--AND D.HostName NOT LIKE 'vivo%'  " QueryOut '+@OutFilePath+@OutFileName+' -c -T' 
--	EXEC master..xp_cmdshell @sqlCmd --, no_output

	

END