Logs scripts

Przeglądanie logów

--podsumowanie
select COUNT(*) ,Level  from log  group by level
select top 1000 * from  log where   Level ='error'  order by id desc


--logi miesiącami
select FORMAT(timestamp, 'yyyyMM'),count(*) from dbo.log
group by FORMAT(timestamp, 'yyyyMM')

--logi tygodniami
select year(timestamp) year,  right('0'+cast(datepart(week,timestamp) as varchar),2) week,count(*) from app.log
group by year(timestamp),  right('0'+cast(datepart(week,timestamp) as varchar),2) 
order by 1,2

--Usunięcie artefaktów logów MK  wynikają z testowania

delete from log where UserName= 'hello@jetquery.io' and Level ='error'
delete from log where ActionName ='Hangfire.AutomaticRetryAttribute'

archiwizacja logów dla tygodnia

DECLARE @counter INT 
SET @counter = 0 

WHILE @counter < 10
BEGIN 

    declare @year varchar(4)
    declare @week varchar(2)
    DECLARE @sqlCommand varchar(2000)
    DECLARE @sqlCommand2 varchar(2000)
    declare @logdb varchar(100) = 'logs2' --<<<<<<<<<<<<<<<<<<<<<<

   -- Begin transaction
    BEGIN TRANSACTION;

    BEGIN TRY
      select top 1  @year = year(timestamp),@week= datepart(week,timestamp)   from app.log
      where timestamp < getutcdate()-14 order by id 

      print @year
      print @week

      set @sqlcommand ='select * into '+ @logdb+'.dbo.[log-'+ db_name() + '-' +@year + right('0'+ @week,2) +'] from app.log where   year(timestamp)='+@year+ ' and  datepart(week,timestamp)='+ @week
      set @sqlcommand2 ='delete from app.log where   year(timestamp)='+@year+ ' and  datepart(week,timestamp)='+ @week

      exec (@sqlcommand)
      exec (@sqlcommand2)
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback transaction on error
        ROLLBACK TRANSACTION;
        PRINT 'An error occurred: ' + ERROR_MESSAGE();
    END CATCH;

   SET @counter = @counter + 1
END

Report - analiza logów

Logi od kiedy do kiedy

select count(*),min([TimeStamp]),max(Timestamp) from app.log 

Podsumowanie logów per dzień

select 
 CAST(timestamp AS DATE) Date,
 COUNT(*) Request ,
 sum(time)/1000 RequestTime,
 sum(case  when time > 3000 then 1 else 0 end ) LongRequest ,
 sum(case  when time > 3000 then time else 0 end )/1000 LongRequestTime ,
 count(distinct username) UniqueUser,
 count(distinct ip) UniqueIP,
 sum(case when isnull(eventid,0)>=0 and eventid<100 then 1 else 0 end) X000,
 sum(case when eventid>=100 and eventid<200 then 1 else 0 end) X100,
 sum(case when eventid>=200 and eventid<300 then 1 else 0 end) X200 ,
 sum(case when eventid>=300 and eventid<400 then 1 else 0 end) X300 ,
 sum(case when eventid>=400 and eventid<500 then 1 else 0 end) X400 ,
 sum(case when eventid>=500 and eventid<600 then 1 else 0 end) X500 ,
 sum(case when eventid>=600 and eventid<700 then 1 else 0 end) X600 , 
 sum(case when eventid>=700 and eventid<800 then 1 else 0 end) X700 ,
 sum(case when eventid>=800 and eventid<900 then 1 else 0 end) X800 ,
 sum(case when eventid>=900 and eventid<1000 then 1 else 0 end) X900
from 
  [app].[log] 
where 
   CAST(timestamp AS DATE)  > cast(dateadd(d,-7,getutcdate()) as date)
group by CAST(timestamp AS DATE)
order by 1 desc


---STATSY PER USER

select username,
 CAST(timestamp AS DATE) timestamp,
 COUNT(*) Requests ,
 sum(time) time,
 count(distinct ip) IPs
from 
    [app].[log] 
where 
   CAST(timestamp AS DATE)  ='2021-11-29'-- cast(dateadd(d,-7,getutcdate()) as date)

group by CAST(timestamp AS DATE),username
order by 1 desc


SELECT count(*) request,username,ip
  FROM [logs].[dbo].[log202051]
    where 
    username not like '%@jetquery.io'
  group by username,ip

  --where requestpath 
  order by 1 desc


SELECT count(*) requests, count(distinct username) unique_users ,dimension1
  FROM [logs].[dbo].[log202051]
  where 
 username not like '%@jetquery.io'
  group by dimension1
  --where requestpath 
  order by 1 desc