December 13, 2012 at 2:06 pm
I have the following query which returns a column name, the database name the column is from, the week (out of 52 in a year) the columns was accessed, and the number of times it was accessed in that week. I need to add the date and hour inwhich it was accessed most within that week. Can someone help me with this?
SELECT object_name, database_name, DATEPART(Week, event_time) AS EventWeek, SUM(sequence_number) AS [#TimesAccessed]
FROM SQLPLEX_AUDITLOG_ALL WITH (nolock)
WHERE (database_name NOT LIKE 'Lite%') AND (class_type_desc IN ('view', 'table')) AND (server_machine_name = 'wpnoa1gNpdbpr') AND
(sequence_number = 1) AND (schema_name = 'DBO') AND (event_time BETWEEN '11/01/2012' AND '11/07/2012')
GROUP BY object_name, database_name, DATEPART(Week, event_time)
ORDER BY [#TimesAccessed] DESC, EventWeek DESC
December 13, 2012 at 2:10 pm
I'm not really familiar with your table. That makes it kind of hard to know what to suggest.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 2:15 pm
Sorry, heres a create script for the table
CREATE TABLE [dbo].[SQLPLEX_AUDITLOG_ALL](
[SQLPlexAuditLogID] [bigint] IDENTITY(1,1) NOT NULL,
[SQLPlexAuditLogID_Local] [bigint] NULL,
[event_time] [datetime2](7) NOT NULL,
[sequence_number] [int] NOT NULL,
[database_type] [varchar](13) NOT NULL,
[server_instance_name] [nvarchar](128) NULL,
[server_machine_name] [sql_variant] NULL,
[server_ip_address] [varchar](15) NOT NULL,
[client_host_name] [nvarchar](128) NULL,
[client_network_protocol] [nvarchar](40) NULL,
[client_ip_address] [varchar](48) NULL,
[client_login] [nvarchar](128) NULL,
[session_server_principal_name] [nvarchar](128) NULL,
[server_principal_name] [nvarchar](128) NULL,
[database_principal_name] [nvarchar](128) NULL,
[client_Authentication] [nvarchar](40) NULL,
[database_name] [nvarchar](128) NULL,
[schema_name] [nvarchar](128) NULL,
[object_name] [nvarchar](128) NULL,
[securable_class_desc] [nvarchar](35) NULL,
[class_type_desc] [nvarchar](35) NULL,
[action_name] [nvarchar](128) NULL,
[statement] [nvarchar](max) NULL,
[program_name] [varchar](1000) NULL,
[error_message] [varchar](1000) NULL,
[ByEnd] [varchar](1) NULL,
[NPI_SchemaName] [varchar](500) NULL,
[NPI_TableName] [varchar](500) NULL,
[NPI_ColumnName] [varchar](500) NULL,
[NPI_ColumnName_Accessed] [varchar](500) NULL,
[DatabaseRoleName] [varchar](50) NULL,
[MemberName] [varchar](50) NULL,
[Process] [varchar](50) NULL,
[ReadDefinition] [varchar](50) NULL,
[Read] [varchar](50) NULL,
[Administer] [varchar](50) NULL,
[Reader] AS ([read]),
PRIMARY KEY CLUSTERED
(
[SQLPlexAuditLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
December 13, 2012 at 5:15 pm
SELECT object_name, database_name, DATEPART(Week, event_time) AS EventWeek, SUM(sequence_number) AS [#TimesAccessed],
DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0) AS [EventHour]
INTO #EventHour
FROM SQLPLEX_AUDITLOG_ALL WITH (nolock)
WHERE (database_name NOT LIKE 'Lite%') AND (class_type_desc IN ('view', 'table')) AND (server_machine_name = 'wpnoa1gNpdbpr') AND
(sequence_number = 1) AND (schema_name = 'DBO') AND
(event_time >= '20121101' AND event_time < '20121108')
GROUP BY object_name, database_name, DATEPART(Week, event_time), DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0)
CREATE CLUSTERED INDEX object_db_week ON #EventHour ( object_name, database_name, EventWeek )
SELECT object_name, database_name, EventWeek, [#TimesAccessed],
(SELECT TOP (1) EventHour FROM #EventHour eh2 WHERE eh2.object_name = eh1.object_name AND
eh2.database_name = eh1.database_name AND eh2.EventWeek = eh1.EventWeek ORDER BY [#TimesAccessed] DESC) AS EventHourMostAccessed
FROM (
SELECT object_name, database_name, EventWeek, SUM([#TimesAccessed]) AS [#TimesAccessed]
FROM #EventHour eh1 WITH (NOLOCK)
GROUP BY object_name, database_name, EventWeek
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2012 at 11:04 am
I appreciate your help. The load of the temp table and creation of the index is working but I'm getting errors with the last select statement.
December 14, 2012 at 1:18 pm
Sorry, I didn't have test data so I couldn't pre-test it myself.
If you'll post some data as INSERT statements, I'll be happy to correct the query.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply