/****** Script for F5计算机用户操作数据SQL记录跟踪 ******/
SELECT HostName FROM SQL_KB_US_01 GROUP BY HostName
SELECT HostName FROM SQL_KB_US_02 GROUP BY HostName
SELECT HostName FROM SQL_KB_US_03 GROUP BY HostName
--SQL_KB_US_01
SELECT * FROM SQL_KB_US_01 WHERE HOSTNAME='KBC-AOI'
--SQL_KB_US_02
SELECT * FROM SQL_KB_US_02
--SQL_KB_US_03
--TBINFOP 'SQL_KB_US_01'
--ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
SELECT * FROM SQL_KB_US_03 with (nolock) where HostName='QTA-ZZ-70'
SELECT * FROM SQL_KB_US_03 with (nolock) where HostName like '%QTA%'
SELECT distinct HostName FROM SQL_KB_US_03 with (nolock) where HostName like '%QTA%'
SELECT * FROM SQL_KB_US_03 with (nolock) where HostName like '%QTA%' order by HostName
SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 with (nolock) where HostName like '%QTA%' group by HostName
SELECT * FROM SQL_KB_US_03 with (nolock) where HostName='QTA-ZZ-70'
SELECT HostName,TextData FROM SQL_KB_US_03 with (nolock) where HostName='QTA-ZZ-70'
SELECT ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY HostName),HostName,TextData FROM SQL_KB_US_03 with (nolock) where HostName like '%QTA%'
SELECT ROW_NUMBER() OVER(ORDER BY HostName),HostName,TextData FROM SQL_KB_US_03 with (nolock) where HostName='QTA-ZZ-70'
--用户SQL数目
SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 with (nolock) where HostName like '%%' group by HostName
SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 with (nolock) where HostName like '%%' group by HostName order by 条数 desc
SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 with (nolock) where HostName like '%AOI%' group by HostName
--先对HostName排序,再生成序号
SELECT ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY HostName DESC) '排序',HostName '用户计算机名',TextData '用户操作数据记录提取SQL语具' FROM SQL_KB_US_03 with (NOLOCK)
SELECT ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY HostName DESC) '排序',HostName '用户计算机名',TextData '用户操作数据记录提取SQL语具' FROM SQL_KB_US_03 with (NOLOCK) where HostName like '%%'
SELECT ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY HostName DESC) '排序',HostName '用户计算机名',TextData '用户操作数据记录提取SQL语具' FROM SQL_KB_US_03 with (NOLOCK) where HostName like '%AOI%'
--数据分析
--用户操作SQL最多的条目分布
SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 where HostName like '%%' group by HostName order by 条数 desc --条数 desc
SELECT top 10 HostName,count(HostName) 条数 FROM SQL_KB_US_03 where HostName like '%%' group by HostName order by 条数 desc --TOP 10
SELECT HostName '用户计算机名',条数 FROM ( SELECT HostName,count(HostName) 条数 FROM SQL_KB_US_03 where HostName like '%%' group by HostName) A WHERE 条数>500 order by 条数 desc --条数>500
