Anyone Tried to Query the Data to Produce Report

leemarkum

So, I have a Security employee that gets the emails about Drift. He is clicking the link to open the dashboard and manually entering the data from there into a spreadsheet. He would like this info provided for him in Excel on a SharePoint site. Has anyone done something similar? The query below gets some data but it appears that the database only keeps a limited number of records. Where is the rest of the data and how do I get to older events?

USE RedGate;
GO

SELECT
[id]
,[PostTime]
-- ,[transaction_id]
,[spid]
-- ,[options]
-- ,[nestlevel]
-- ,[langid]
,[client_net_address]
,[appname]
,[eventdata]
,eventdata.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(50)') AS EventType
,eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','VARCHAR(50)') AS PostTime
,eventdata.value('(/EVENT_INSTANCE/SPID)[1]','VARCHAR(50)') AS SPID
,eventdata.value('(/EVENT_INSTANCE/ServerName)[1]','VARCHAR(50)') AS ServerName
,eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','VARCHAR(50)') AS DBName
,eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(50)') AS LoginName
,eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','VARCHAR(50)') AS ObjectName
,eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','VARCHAR(50)') AS ObjectType
,eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(8000)') AS SQLText
FROM
[RedGate].[SQLLighthouse].[DDL_Events]