SharePoint Audit Data Query

 

 

USE [SharePointAuditDB]

GO

/****** Object: Table [dbo].[EventData] Script Date: 2016-11-15 오후 12:12:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EventData](

[No] [int] IDENTITY(1,1) NOT NULL,

[WebUrl] [nvarchar](50) NULL,

[WebName] [nvarchar](50) NULL,

[ListName] [nvarchar](50) NULL,

[ItemName] [nvarchar](50) NULL,

[UserID] [nvarchar](50) NULL,

[UserName] [nvarchar](50) NULL,

[DocLocation] [nvarchar](100) NULL,

[EventName] [nvarchar](50) NULL,

[Occurred] [datetime] NULL

) ON [PRIMARY]

GO

 

 

 

USE SharePointAuditDB

GO

 

DECLARE @SiteUrl varchar(50) = 'http://wonkim.cloudapp.net/'

INSERT INTO EventData (WebUrl, WebName, ListName, ItemName, UserID, UserName, DocLocation, EventName, Occurred)

SELECT @SiteUrl + (Select FullUrl FROM [WSS_Content].[dbo].[AllWebs] WHERE Id = Lists.tp_WebId) AS WebUrl

,(Select Title FROM [WSS_Content].[dbo].[AllWebs] WHERE Id = Lists.tp_WebId) AS WebName

,Lists.tp_Title AS ListName

,(CASE

WHEN [ItemType] = '1' THEN 'Page or File'

WHEN [ItemType] = '3' THEN 'List Item'

WHEN [ItemType] = '4' THEN 'List'

WHEN [ItemType] = '5' THEN 'Folder'

WHEN [ItemType] = '6' THEN 'Related pages and data within Site Collection'

WHEN [ItemType] = '7' THEN 'Site Collection'

END) AS ItemName

,(SELECT SUBSTRING(tp_Login, CHARINDEX('\', tp_Login) + 1, LEN(tp_Login)) FROM [WSS_Content].[dbo].[UserInfo] WHERE (tp_ID = AuditData.UserId) AND (tp_SiteID = AuditData.SiteId)) AS UserID

,(SELECT tp_Title FROM [WSS_Content].[dbo].[UserInfo] WHERE (tp_ID = AuditData.UserId) AND (tp_SiteID = AuditData.SiteId)) AS UserName

,[DocLocation]

,(CASE

WHEN [EVENT] = '1' THEN 'CheckOut'

WHEN [EVENT] = '2' THEN 'CheckIn'

WHEN [EVENT] = '3' THEN 'View'

WHEN [EVENT] = '4' THEN 'Delete'

WHEN [EVENT] = '5' THEN 'Update'

WHEN [EVENT] = '6' THEN 'ProfileChange'

WHEN [EVENT] = '7' THEN 'ChildDelete'

WHEN [EVENT] = '8' THEN 'SchemaChange'

WHEN [EVENT] = '9' THEN 'Undelete'

WHEN [EVENT] = '10' THEN 'Workflow'

WHEN [EVENT] = '11' THEN 'Copy'

WHEN [EVENT] = '12' THEN 'Move'

WHEN [EVENT] = '13' THEN 'AuditMaskChange'

WHEN [EVENT] = '14' THEN 'Search'

WHEN [EVENT] = '15' THEN 'ChildMove'

WHEN [EVENT] = '16' THEN 'FileFragmentWrite'

WHEN [EVENT] = '17' THEN 'SecGroupCreate'

WHEN [EVENT] = '18' THEN 'SecGroupDelete'

WHEN [EVENT] = '19' THEN 'SecGroupMemberAdd'

WHEN [EVENT] = '20' THEN 'SecGroupMemberDel'

WHEN [EVENT] = '21' THEN 'SecRoleDefCreate'

WHEN [EVENT] = '22' THEN 'SecRoleDefDelete'

WHEN [EVENT] = '23' THEN 'SecRoleDefModify'

WHEN [EVENT] = '24' THEN 'SecRoleDefBreakInherit'

WHEN [EVENT] = '25' THEN 'SecRoleBindUpdate'

WHEN [EVENT] = '26' THEN 'SecRoleBindInherit'

WHEN [EVENT] = '27' THEN 'SecRoleBindBreakInherit'

WHEN [EVENT] = '28' THEN 'EventsDeleted'

WHEN [EVENT] = '100' THEN 'Custom' END) AS EventName

,[Occurred]

FROM [WSS_Content].[dbo].[AuditData] AuditData INNER JOIN [WSS_Content].[dbo].[AllSites] Sites ON AuditData.SiteId = Sites.Id

LEFT OUTER JOIN [WSS_Content].[dbo].[AllLists] Lists ON AuditData.ItemId = Lists.tp_ID

WHERE (Lists.tp_Title NOT IN ('Translation Packages', 'Master Page Gallery', 'Web Part Gallery') OR Lists.tp_Title IS NULL) AND DocLocation NOT LIKE '_catalogs/masterpage/%'

AND [Occurred] >= DATEADD(DAY, -1, GETDATE()) AND Lists.tp_Title IS NOT NULL

ORDER BY Occurred