You can convert() to date to truncate the time portion of a datetime data type.
select
Action
, TimeOccurred = convert(date,TimeOccurred )
, Count(Action)
from [].[dbo].[]
where Action like '%Logon Failed%'
and TimeOccured >= dateadd(day,-30,dateadd(day, datediff(day, 0, getdate()), 0))
group by Action
, convert(date,TimeOccurred)
order by TimeOccurred
For your where, you can calculate the date for 30 days ago instead of getting a datediff() and restricting that range to 0-30.
For conditional aggregation you could do something like this:
select
TimeOccurred = convert(date, TimeOccurred)
, logon_kerberos = count (case when Action like ' %logon (kerberos)%' then 1 end)
, logon_local_wts = count (case when Action like ' %logon (local/wts)%' then 1 end)
, logon_ntlm = count (case when Action like ' %logon (ntlm)%' then 1 end)
, logon_total = count (case when Action like ' %logon (%' then 1 end)
, Count(Action)
from [CPTRAX_for_Windows].[dbo].[Logon_Logoff_and_Failed_Logon_Profiles]
where Action like '%Logon (%'
and TimeOccurred >= dateadd(day, -30, dateadd(day, datediff(day, 0, getdate()), 0))
group by convert(date, TimeOccurred)
order by TimeOccurred
You can use a Calendar or dates table for this sort of thing.
For only 152kb in memory, you can have 30 years of dates in a table with this:
/* dates table */
declare @fromdate date = '20000101';
declare @years int = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
[Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
on dbo.Dates([Date]);
Without taking the actual step of creating a table, you can use it inside a common table expression with just this:
declare @fromdate date = dateadd(day , datediff(day , 0, getdate() )-30 , 0);
declare @thrudate date = dateadd(day , datediff(day , 0, getdate() ), 0);
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
select top (datediff(day, @fromdate, @thrudate)+1)
[Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
from n as deka cross join n as hecto cross join n as kilo
cross join n as tenK cross join n as hundredK
order by [Date]
)
select [Date]
from dates;
Use either like so:
select
TimeOccurred = d.Date
, logon_kerberos = count (case when Action like ' %logon (kerberos)%' then 1 end)
, logon_local_wts = count (case when Action like ' %logon (local/wts)%' then 1 end)
, logon_ntlm = count (case when Action like ' %logon (ntlm)%' then 1 end)
, logon_total = count (case when Action like ' %logon (%' then 1 end)
, Count(Action)
from Dates d
left join [CPTRAX_for_Windows].[dbo].[Logon_Logoff_and_Failed_Logon_Profiles] l
on d.Date = convert(date,l.TimeOccured)
and l.Action like '%Logon (%'
where d.Date >= dateadd(day, -30, dateadd(day, datediff(day, 0, getdate()), 0))
group by d.Date
order by d.Date
Number and Calendar table reference:
