How do I collect SharePoint audit data using DBConnect

[dperre_splunk]

Hi There,

I am looking for a way to get SharePoint audit data into Splunk via DBConnect. Does anyone have a working script that I can use?

[dperre_splunk · Accepted solution]

Here is a script that may work for you :).

Notes:
Change the name of the host and index. Depending on how busy your SharePoint server is the max_rows may not be enough so keep an eye on this

[OuterSharepointDB]
connection = SharepointDB
host = SharepointDBHostname
index = sharepoint
interval = 58
max_rows = 1000
mode = batch
query = SELECT
SiteId,
ItemId,
ItemType,
UserId,
AppPrincipalId,
MachineName,
MachineIp,
DocLocation,
LocationType,
Occurred,
Event,
EventName,
EventSource,
SourceName,
tp_SiteID,
tp_ID,
tp_DomainGroup,
tp_Deleted,
tp_SiteAdmin,
tp_IsActive,
tp_Login,
tp_Title,
tp_Email,
tp_Notes,
tp_Token,
tp_ExternalTokenLastUpdated,
PortalURL,
PortalName
FROM "WSS_Content_SCP"."dbo"."AuditData"
WITH (NOLOCK)
LEFT JOIN "WSS_Content_SCP"."dbo"."UserInfo"
ON "WSS_Content_SCP"."dbo"."AuditData"."UserId" = "WSS_Content_SCP"."dbo"."UserInfo"."tp_ID"
LEFT JOIN "WSS_Content_SCP"."dbo"."AllSites"
ON "WSS_Content_SCP"."dbo"."AuditData"."SiteId" = "WSS_Content_SCP"."dbo"."AllSites"."Id"
WHERE "WSS_Content_SCP"."dbo"."AuditData"."Occurred" > DATEADD(minute, -60, GETDATE() )
AND "WSS_Content_SCP"."dbo"."AuditData"."UserId" = "WSS_Content_SCP"."dbo"."UserInfo"."tp_ID"
AND "WSS_Content_SCP"."dbo"."AuditData"."SiteId" = "WSS_Content_SCP"."dbo"."UserInfo"."tp_SiteID"
AND UserId!=1
AND tp_Title NOT LIKE 'System Account'
AND tp_Title NOT LIKE 'NT AUTHORITY\LOCAL SERVICE'
ORDER BY "WSS_Content_SCP"."dbo"."AuditData"."Occurred" DESC

[noelflorendo]

@dperre_splunk Hi mate, can you elaborate where should i put your script? to connect my sharepoint data to splunk. Thanks!

[dperre_splunk]

@noelflorendo This will need to be on a heavy forwarder with dbconnect installed 🙂