https://worldofeuc.com logo
Join Slack
Powered by
This message was deleted.
# citrix-vad
s
This message was deleted.
h
What kind of information are you trying to grab. I have a ton of different queries I use to gather info for security teams and for grafana
d
This is the long list, which may not be available in its entirety, but its the data we are being asked for. User state tracking - active, idle, or disconnected User login/logout times Applications that were accessed and time active in apps, idle time User IP connecting from User computer name connecting from CPU usage for each application and user Memory usage for each application and user
r
midn sharing what SIEM?
d
elastic
h
I do already have SQL queries for all of that except the CPU and memory. I will pass them along when I get back to my computer
d
oh, great. Thanks !
h
all the SQL queries start with
Copy code
use <Citrix Monitoring Database>
🙌 1
Copy code
/*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
✅ 1
🙌 1
basically the same thing, but add published apps into the mix and checks last 7 months
Copy code
/*******  Script for listing all launches of pub apps ********/
Select  [connection].clientname,   
    [User].UserName,
	[connection].ClientAddress,
	[connection].ClientPlatform,
	[Connection].ClientVersion,
	[Application].PublishedName,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].StartDate) as StartDate_LocalTime,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].EndDate) as EndDate_LocalTime, 
    [DesktopGroup].Name as DesktopGroup
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].[ApplicationInstance] on [MonitorData].[Session].[SessionKey] = [MonitorData].[ApplicationInstance].[SessionKey])
inner join [MonitorData].[Application] on [MonitorData].[ApplicationInstance].[ApplicationId] = [MonitorData].[Application].[id])
inner join [MonitorData].[Machine] on [MonitorData].[Session].MachineID = [MonitorData].[Machine].ID)
inner join [MonitorData].[DesktopGroup] on [MonitorData].[Machine].DesktopGroupID = [MonitorData].[DesktopGroup].ID)
where ([connection].clientversion <> ' ') and [session].StartDate >= dateadd(month,-7,GetUTCDate()) order by PublishedName
🙌 1
does that cover basically what you are looking for?
this is for pub desktops, not pub apps
Copy code
/*******  Script for listing all launches of pub Desktops ********/
Select  [connection].clientname,   
    [User].UserName,
	[connection].ClientAddress,
	[connection].ClientPlatform,
	[Connection].ClientVersion,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].StartDate) as StartDate_LocalTime,
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [Session].EndDate) as EndDate_LocalTime, 
    [DesktopGroup].Name as DesktopGroup
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 ([Session].SessionType = '0') and ([connection].clientversion <> ' ') and [session].StartDate >= dateadd(day,-7,GetUTCDate()) order by username
all the data in the monitoring db can be mapped out using the [session] table as the main focus point and branching out from there
r
I'd also recommend sending all your netsclaer syslogs to elastic and do some clean up but its a good way to get both activity via the brokers plus any netscaler connectivity
e
Hi @Hal Lange, thanks for those queries. Do you have any more interesting queries that you could share?
h
will look through my queries to see what else I have
2 Views
Open in Slack
PreviousNext
Powered by