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