首先建立新表:
然后用SQL实现把查询的数据insert进入新表:
Insert into test.dbo.tmp_tj001(server_principal_name,object_name,maxdate)
select s1.server_principal_name,s1.object_name,max(s1.event_time) as maxdate from
(SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_920000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_520000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_450000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_650000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_980000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_730000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_430000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_670000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_380000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U')s1
group by s1.server_principal_name,s1.object_name
![](http://img.blog.itpub.net/blog/attachment/201710/26/22996654_1509006430aRk9.png?x-oss-process=style/bb)
然后用SQL实现把查询的数据insert进入新表:
Insert into test.dbo.tmp_tj001(server_principal_name,object_name,maxdate)
select s1.server_principal_name,s1.object_name,max(s1.event_time) as maxdate from
(SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_920000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_520000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_450000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_650000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_980000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_730000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_430000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_670000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U'
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_380000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and class_type='U')s1
group by s1.server_principal_name,s1.object_name
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2146465/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22996654/viewspace-2146465/