Anyone Tried to Query the Data to Produce Report

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;

-- ,[transaction_id]
-- ,[options]
-- ,[nestlevel]
-- ,[langid]
,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
Sign In or Register to comment.