Sum304: Deploying and Troubleshooting Edgesight

1. Sum304: Deploying and Troubleshooting Edgesight Vincent Papoz Escalation Engineer October 16, 2012

2. Agenda • Architecture overview • Use case: Troubleshooting slow logon • Troubleshooting and resolving Edgesight Performance issues • Custom Reporting (SQL objects and where to find data) • Question

3. Architecture Overview

4. Edgesight Server SQL Server XenApp servers with Edgesight Agent Edgesight and Report Server Databases Endpoints RS Server

5. What does the agent monitor? • Event-driven data • Process starts/stops, errors and faults • User logon • WinSock connection and HTTP transactions • Windows Event Log • System reboot • Polled data • System and Process performance metrics • ICA session data • Session connects/disconnects • Detailed logon and session metrics, virtual channel statistics • Scheduled data • Drive space calculation • Asset history

6. End User Experience monitoring • Collects both client Start-up and Server Start-up metrics • Service running on the XenApp Servers

7. Use case: Troubleshooting slow logon

13. Troubleshooting and resolving Edgesight Performance issues

14. Symptoms • Report Rendering takes forever, • Payloads are queuing up in the webload directory and are imported very slowly, • Servers will not update the Database, • Nightly maintenance takes a very long time - and possibly eventually fails, • Grooming error messages are showing up in the console, • Database data files very large

15. Cause • Usually a problem with the SQL database • SQL server out of disk space or too much data to process • Data processing cannot complete because the Tlog cannot grow • Data processing is resource intensive and is delayed

16. Action plan • Assess your reporting requirements • Analyze the content of the database • Remove the unwanted data • Reclaim disk space • Adjust the collection configuration

17. Analyzing the content of the database – Step1 • Find out the Size of the Edgesight Database data files SELECT Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'EdgesightDBname‘GO

18. Analyzing the content of the database – Step2 SELECT DISTINCT object_name(i.id) as TableName, f.name as Filegroup FROM sys.sysindexes i, sys.filegroups fWHERE objectproperty(i.id,'IsUserTable') = 1AND f.data_space_id = i.groupidORDER BY f.nameGO

19. Analyzing the content of the database – Step3 SELECT sysobjects.Name, sysindexes.RowsFROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.idWHERE sysindexes.IndId < 2 AND sysobjects.Name IN('CORE_NET_STAT', 'CORE_NET_TRANS')

20. Analyzing the content of the database – Step4 - Finding out about the grooming schedule: SELECT f.table_name, m.default_days, m.groom_days FROM maint_table_config m JOIN table_def f on f.tableid=m.tableid WHERE table_name in ('CORE_NET_STAT', 'CORE_NET_TRANS')

21. Analyzing the content of the database – Step4 - Has the grooming been failing? SELECT COUNT(*) FROM CORE_NET_TRANS WHERE dtperiod < GETUTCDATE() - 11

22. Removing Data manually • Grooming is failing – Delete data in increments declare @row int;declare @date datetime;set @date = GETUTCDATE() - 10 set @row = (select COUNT(*) from core_net_trans where dtperiod < @date); while @row <> 0 begin delete top(100000) from core_net_trans where dtperiod < @date set @row = (select COUNT(*) from core_net_trans where dtperiod < @date); end; go DBCC SHRINKFILE(N'Edgesight_FG6_Data', 0, TRUNCATEONLY) GO

23. Removing Data manually • Grooming is not failing - Analyze the content of the tableSELECT c.imid,i.[filename],count(c.imid)as cnt from core_net_trans c JOIN [image] i ON i.imid=c.imid GROUP BY c.imid,i.[filename] ORDER BY cnt DESC => Delete the process instance records from the table

24. Reclaiming disk space • Using SQL Studio (SSMS) • Using the following query DBCC SHRINKFILE(N'Edgesight_FG6_Data', 0, TRUNCATEONLY) GO

25. Adjust collection configuration • Adjust the grooming schedule • Review the upload configuration • Ignore processes if applicable

26. Ignoring a process

27. Ignoring a process (Excluding a process) • Disable injection of csma_ldr.dll • Per device registry setting • Requires a reboot of the monitored device

28. Ignoring a process – Finding affected servers SELECT m.name,COUNT(m.name)AS cnt FROM core_net_trans cINNERJOIN instance i ON i.instid=c.instidINNERJOIN machine m ON m.machid=i.machidINNERJOIN [image] im ON im.imid=c.imidWHERE im.[filename] =‘SomeApp.exe‘GROUPBY m.nameORDERBY cnt DESC

29. Ignoring a process – Verifying process exclusion • Use Sysinternals Process Explorer

31. Design consideration – SQL server • Split the filegroups on different hard driveshttp://msdn.microsoft.com/en-us/library/ms187087(v=sql.105).aspx • Set the recovery model to simple for the Edgesight databasehttp://msdn.microsoft.com/en-us/library/ms189275(v=sql.105).aspx • Implement Data warehousing

32. A word on Custom Reporting

33. XenApp servers with Edgesight Agent Endpoints

34. Where to find data… Tables vs Views • 283 user defined tables • Schema is not documented • Design is complex • 96 SQL views • Fully documented • Used by the built in reports • Recommended when creating custom reports

35. What you need to know… • Limited to historical reports • Built in report files located under <Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Pages\app\ext\reporting\9> • Uses Reporting services RDL format (XML) • Queries are written in T-SQL • Localization built in Edgesight

36. Tools • An XML editor • Report Builder • Report Designer (Business Intelligence Development Studio) • SQL Studio (SSMS)

40. Resources • Custom Report Bloghttp://blogs.citrix.com/2009/12/09/edgesight-custom-reporting/ • Report Definition Languagehttp://msdn.microsoft.com/en-us/library/ms155062.aspx • Microsoft Report Builderhttp://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/SQL-Server-2012-reporting-services.aspx • Anatomy of an Edgesight Report http://support.citrix.com/article/CTX116452