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

SQL脚本如下：

USE [DHCP]GO/****** Object:  StoredProcedure [dbo].[usp_DHCP_Blacklist]    Script Date: 2016/8/10 15:36:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ===============================================-- Author:Burgess-- Create date: 2016/4/15-- Description:分析DHCP Log，对比出未加域的黑名单-- ===============================================ALTER PROCEDURE [dbo].[usp_DHCP_Blacklist]ASBEGINSET 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=0UPDATE ROWNO SET CurrentRow=2 WHERE ID=1ELSEUPDATE ROWNO SET CurrentRow+=@CurrentRow WHERE ID=1SELECT @BeginRow=CurrentRow FROM ROWNO WHERE ID=1print 'Begin:'+CONVERT(varchar(15), GETDATE(), 114)--清理表RAWDATATRUNCATE TABLE RAWDATA--copy fileset @cmd='copy '+ @SharePth+@FileName+' '+@FilePath+@FileNameEXEC master..xp_cmdshell @cmd --, no_outputprint '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 @cmdexecute (@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)--将数据同步到存放所有联网的主机信息表DHCPMERGE INTO DHCP AS A USING #DHCP AS BON A.HOSTNAME=B.HOSTNAME WHEN MATCHED THENUPDATE SET A.ID=B.ID, A.GENDATE=B.GENDATE,A.GENTIME=B.GENTIME,A.IPADDR=B.IPADDR,A.MACADDR=B.MACADDR WHEN NOT MATCHED THENINSERT (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_outputEND