/*This will get sessions for last 90 days
it will get the following columns
Username, Client IP Address, Client Platform, ClientName, Session Start Date/Time (in local Time), Session End Date/Time (in local time), VDA machine Name, DesktopGroup Name,
*/
Select     
    [User].UserName,
    [connection].ClientAddress,
    [connection].ClientPlatform,
    [connection].clientname,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].StartDate) as StartDate_LocalTime,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].EndDate) as EndDate_LocalTime, 
    [Machine].HostedMachineName,
    [DesktopGroup].Name
from (((([MonitorData].[Session]     
inner join [MonitorData].[User] On [MonitorData].[User].ID = [MonitorData].[Session].UserID)
inner join [MonitorData].[Connection] on [MonitorData].[Session].Sessionkey = [MonitorData].[Connection].Sessionkey)
inner join [MonitorData].[Machine] on [MonitorData].[Session].MachineID = [MonitorData].[Machine].ID)
inner join [MonitorData].[DesktopGroup] on [MonitorData].[Machine].DesktopGroupID = [MonitorData].[DesktopGroup].ID)
where StartDate >= dateadd(day,-90,GetUTCDate()) order by StartDate


Select top 1 
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].StartDate) as StartDate_LocalTime
from [MonitorData].[Session]     
inner join [MonitorData].[User] On [MonitorData].[User].ID = [MonitorData].[Session].UserID
where ((username like '<Username>')) order by StartDate Desc


/*Selects last starttime per user in last 30 days*/
Select t.UserName, Max(t.StartDate_UTC) as LastLogin from (Select    
    [User].UserName,
    [Session].StartDate as StartDate_UTC
from ([MonitorData].[Session]     
inner join [MonitorData].[User] On [MonitorData].[User].ID = [MonitorData].[Session].UserID)
where StartDate >= dateadd(day,-30,GetUTCDate()))t group by UserName order by username asc

/*Shows count per user over last 30 days*/
select [User].UserName, count (*) as TotalSessions
from ([MonitorData].[Session]     
inner join [MonitorData].[User] On [MonitorData].[User].ID = [MonitorData].[Session].UserID)
where  StartDate >= dateadd(day,-30,GetUTCDate()) 
group by UserName

/*shows distinct users that logged in successfully for over 5 min in a timespan*/
select distinct(t1.UserName) from 
(Select     
    [User].UserName,
    [Session].StartDate,
    [Session].EndDate,
	DATEDIFF(mi, [session].startdate, [Session].endDate) as Duration,
    [DesktopGroup].Name
from (((([MonitorData].[Session]     
inner join [MonitorData].[User] On [MonitorData].[User].ID = [MonitorData].[Session].UserID)
inner join [MonitorData].[Connection] on [MonitorData].[Session].Sessionkey = [MonitorData].[Connection].Sessionkey)
inner join [MonitorData].[Machine] on [MonitorData].[Session].MachineID = [MonitorData].[Machine].ID)
inner join [MonitorData].[DesktopGroup] on [MonitorData].[Machine].DesktopGroupID = [MonitorData].[DesktopGroup].ID)
where StartDate >= '2021-07-01 00:15' and EndDate <= '2021-07-01 13:13' and [DesktopGroup].Name like 'DGName%' 
) as t1
where duration > 5