Запросы к mssql серверу к базе синхронизированной с traffic inspector и пример построения в Reporting Services


Административные скрипты MS SQL > Запросы к mssql серверу к базе синхронизированной с traffic inspector и пример построения в Reporting Services
24.11.2015 20:33:35


Наиболее часто встречающиеся слова в статье:

[dateadd] [varchar] [RatingLog] [/1024/1024] [between] [RxBytes] [TxBytes] [UserGUID] [DisplayName]


Статья:

Запросы к mssql серверу к базе синхронизированной с traffic inspector и пример построения в Reporting Services

 select
RatingLog.Time as "time",UserGUID as userguid,UsersAndGroups.DisplayName as displayname,Host as host,ContentName as catogory,IsBlocked as isblocked,FilterName as filtername,Size as size
from RatingLog (NOLOCK)
inner join UsersAndGroups (NOLOCK) on RatingLog.UserGUID = UsersAndGroups.GUID

where (RatingLog.ServerID = 0)
and [Time] between cast(dateadd(hour,-3,@data1) as varchar(50)) and cast(dateadd(hour,-3,@data2) as varchar(50))
order by time desc



SELECT

(Select h.DisplayName from [ti].[dbo].UsersAndGroups h (nolock) where h.GUID = h2.GUID) as displayname
,sum([RxBytes])/1024/1024 as [Rx_мб]
,sum([TxBytes])/1024/1024 as [Tx_мб]
,sum([RxBytes])/1024 as [Rx_kб]
,sum([TxBytes])/1024 as [Tx_kб]
,sum([RxBytes])/1024/1024/1024 as [Rx_Гб]
,sum([TxBytes])/1024/1024/1024 as [Tx_Гб]
FROM [ti].[dbo].[UserTrafficLog] h2 (nolock)

where h2.[Time] between cast(dateadd(hour,-3,@data_c) as varchar(50)) and cast(dateadd(hour,-3,@data_po) as varchar(50))
group by h2.GUID 

Proxy reports

select
UserName,Host,sum(Rx)/1024/1024 as [RX мб ],sum(Tx) /1024/1024 as [TX мб ] ,sum(Rx)/1024 as RX , sum(Tx)/1024 as TX
from ProxyLog (NOLOCK)
where (ServerID = 0)
and [Time] between cast(dateadd(hour,-3,@data_c) as varchar(50)) and cast(dateadd(hour,-3,@data_po) as varchar(50))
group by UserName,Host

SELECT
(0.01+max(h.[RxBytes])-min(h.[RxBytes]))/1024/1024 as [Rx_мб]
,(0.01+max(h.[TxBytes])-min(h.[TxBytes]))/1024/1024 as [Tx_мб]
,(0.01+max(h.[RxBytes])-min(h.[RxBytes]))/1024/1024/1024 as [Rx_Гб]
,(0.01+max(h.[TxBytes])-min(h.[TxBytes]))/1024/1024/1024 as [Tx_Гб]
,max(h.[RxBytes])-min(h.[RxBytes]) as [RxBytes]
,max(h.[TxBytes])-min(h.[TxBytes]) as [TxBytes]
,h2.DisplayName

FROM [ti].[dbo].[ExtCounterLog] h (nolock)
join [ti].[dbo].[ExtInfo] h2 on h.GUID=h2.GUID
where h.[Time] between cast(dateadd(hour,-3,@data_c) as varchar(50)) and cast(dateadd(hour,-3,@data_po) as varchar(50))
group by h2.DisplayName
order by h2.DisplayName asc

 select
1 as engine,RatingLog.Time as "time",UserGUID as userguid,UsersAndGroups.DisplayName as displayname,Host as host,ContentName as catogory,IsBlocked as isblocked,FilterName as filtername,Size as size
from RatingLog (NOLOCK)
inner join UsersAndGroups (NOLOCK) on RatingLog.UserGUID = UsersAndGroups.GUID

where (RatingLog.ServerID = 0)
and [Time] between cast(dateadd(hour,-3,@data1) as varchar(50)) and cast(dateadd(hour,-3,@data2) as varchar(50))
order by time desc

select
time,1 as engine,virtype,virsubtype,virname,service,action,srcid,src,(Select DisplayName from UsersAndGroups where UserGuid = GUID) as displayname
from AVLog (NOLOCK)
where (ServerID = 0)
and [Time] between cast(dateadd(hour,-3,@data_c) as varchar(50)) and cast(dateadd(hour,-3,@data_po) as varchar(50))
order by displayname desc


Заказ отчётов для Reporting Services iocsha@yandex.ru