-- VDI »ç¿ëÀÚ Åë°è ¼öÁý Query

-- 1. ÀϺ° Á¢¼Ó Ƚ¼ö

-- 2. ÀϺ° »ç¿ëµÈ VM ¼ö

-- 3. ÀϺ° µ¿½ÃÁ¢¼ÓÀÚ ¼ö



declare @time_start datetime

declare @time_end datetime

declare @temptable table(StartDate varchar(10),EndDate varchar(10), Connect_Count nvarchar(512))

declare @temptable2 table(StartDate varchar(10),EndDate varchar(10), Use_VM_Count nvarchar(512))

declare @i int

declare @day_start datetime

declare @cc int	



set @time_start='2020-01-01'  --Á¶È¸ ½ÃÀÛ³¯Â¥

set @time_end='2020-12-31'  -- Á¶È¸ Á¾·á³¯Â¥







--============================================================================

------- 1. ÀϺ° Á¢¼Ó Ƚ¼ö Query----------------------------------------------

set @i=DATEPART(dd,@time_start)

set @day_start=@time_start



while @i<=DATEdiff(d,@time_start,@time_end+1)

begin



-- µ¥ÀÌÅÍ Á¶È¸

	SELECT distinct @cc=count(ed.StrValue)

	FROM dbo.event_historical AS ev,

	dbo.event_data_historical AS ed

	WHERE ev.EventID = ed.EventID 

	And ed.Name='UserDisplayName' 

	and ev.EventType='BROKER_USERLOGGEDIN'  

	and ev.Time between @day_start and @day_start+1



-- Á¶È¸µÈ µ¥ÀÌÅÍ ÀÓ½ÃÅ×ÀÌºí¿¡ Insert

	insert into @temptable

		values(convert(varchar(10),@day_start,120)

		,convert(varchar(10),@day_start+1,120)

		,@cc)

	

	set @day_start=dateadd(d,1,@day_start)

	set @i=@i+1

	

end



set @day_start=''

set @i=0

set @cc=0



-- InsertµÈ Àӽà Å×À̺í Á¶È¸

select *

from @temptable



--============================================================================

------- 2. ÀϺ° »ç¿ëµÈ VM ¼ö Query----------------------------------------------

set @i=DATEPART(dd,@time_start)

set @day_start=@time_start

while @i<=DATEdiff(d,@time_start,@time_end+1)

begin



-- µ¥ÀÌÅÍ Á¶È¸

	select @cc = COUNT(*)

	from

	( 

    SELECT distinct ed.StrValue, ev.Node

	FROM dbo.event_historical AS ev

	LEFT OUTER JOIN dbo.event_data_historical AS ed

	ON ev.EventID = ed.EventID

	WHERE (ev.EventType='AGENT_PENDING' or ev.EventType='AGENT_DISCONNECTED' or ev.EventType='AGENT_ENDED') 

	and ed.Name='UserDisplayName' 

	and ev.Time between @day_start and @day_start+1

	)a

	

-- Á¶È¸µÈ µ¥ÀÌÅÍ ÀÓ½ÃÅ×ÀÌºí¿¡ Insert

	insert into @temptable2

		values(convert(varchar(10),@day_start,120)

		,convert(varchar(10),@day_start+1,120)

		,@cc)

	

	set @day_start=dateadd(d,1,@day_start)

	set @i=@i+1

	

end



set @day_start=''

set @i=0

set @cc=0



-- InsertµÈ Àӽà Å×À̺í Á¶È¸

select *

from @temptable2

--=========================================================================





--============================================================================

------- 3. ÀϺ° µ¿½ÃÁ¢¼ÓÀÚ ¼ö Query-------------------------------------------



SELECT convert(varchar(10),a.Time,120) Date,

CASE WHEN DATALENGTH(a.ModuleAndEventText) = 184