[EN] SQL Queries for ACS
Summary
- In this post, we will document a query for troubleshoot problems in the ACS database
- For your reference, you can also review this article https://blogs.technet.com/b/jimmyharper/archive/2009/12/10/some-custom-acs-reports.aspx
More Information
- Usage – User Logon
- Note:
- Change 'DOMAIN\USER' for a domain and the user that you want
- There are two dates to specify, first one is initial date/time and the other is final time in format 'YYYY-MM-DD HH:MM:SS'
- Note:
SET DATEFIRST 7
SELECT DISTINCT
[AdtServer_dvAll].[CreationTime] [DateTime],
[AdtServer_dvAll].[EventMachine] [Computer],
CASE WHEN [AdtServer_dvAll].[EventId] = 4624 THEN [AdtServer_dvAll].[String03] ELSE [AdtServer_dvAll].[String02] END [IPAddress],
CASE WHEN [AdtServer_dvAll].[EventId] = 4624 THEN [AdtServer_dvAll].[String13] ELSE [AdtServer_dvAll].[String09] END [AuthPackage],
CASE WHEN [AdtServer_dvAll].[EventId] = 4624 THEN [AdtServer_dvAll].[String11] ELSE [AdtServer_dvAll].[String08] END [LogonProcess],
CASE WHEN [AdtServer_dvAll].[EventId] = 4624 THEN CASE WHEN [AdtServer_dvAll].[String02] = '3' THEN 'Network' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '2' THEN 'Interactive' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '4' THEN 'Batch' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '5' THEN 'Service' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '7' THEN 'Unlock' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '8' THEN 'NetworkCleartext' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '9' THEN 'NewCredentials' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '10' THEN 'RemoteInteractive' ELSE CASE WHEN [AdtServer_dvAll].[String02] = '11' THEN 'CachedInteractive' ELSE 'Unknown' END END END END END END END END END ELSE CASE WHEN [AdtServer_dvAll].[String01] = '3' THEN 'Network' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '2' THEN 'Interactive' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '4' THEN 'Batch' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '5' THEN 'Service' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '7' THEN 'Unlock' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '8' THEN 'NetworkCleartext' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '9' THEN 'NewCredentials' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '10' THEN 'RemoteInteractive' ELSE CASE WHEN [AdtServer_dvAll].[String01] = '11' THEN 'CachedInteractive' ELSE 'Unknown' END END END END END END END END END END [LogonType]
FROM
(
SELECT Id, EventId, SequenceNo, [S/F], Category, CreationTime, CollectionTime, AgentMachine, EventMachine, Source, HeaderSid, HeaderUser,
HeaderDomain, PrimarySid, PrimaryUser, PrimaryDomain, PrimaryLogonId, ClientSid, ClientUser, ClientDomain, ClientLogonId, TargetSid, TargetUser,
TargetDomain, String01, String02, String03, String04, String05, String06, String07, String08, String09, String10, String11, String12, String13, String14,
String15, String16, String17, String18, String19, String20, String21, String22
FROM AdtServer.dvAll WITH (nolock)
) [AdtServer_dvAll]
WHERE
[AdtServer_dvAll].[EventId] IN (528, 540, 4624) AND CONVERT(DATETIME, CONVERT(CHAR(10), [AdtServer_dvAll].[CreationTime], 102), 102) >= CONVERT(DATETIME,'YYYY-MM-DD HH:MM:SS', 121) AND CONVERT(DATETIME, CONVERT(CHAR(10), [AdtServer_dvAll].[CreationTime], 102), 102) <= CONVERT(DATETIME,'YYYY-MM-DD HH:MM:SS', 121) AND (UPPER(((COALESCE([AdtServer_dvAll].[PrimaryDomain], '') + '\') + COALESCE([AdtServer_dvAll].[PrimaryUser], ''))) = UPPER('DOMAIN\USER') OR UPPER(((COALESCE([AdtServer_dvAll].[PrimaryDomain], '') + '\') + COALESCE([AdtServer_dvAll].[PrimaryUser], ''))) IS NULL AND UPPER('DOMAIN\USER') IS NULL)
ORDER BY
[DateTime], [Computer], [IPAddress], [AuthPackage], [LogonProcess], [LogonType]
Conclusion
- This is the same report that you run from SSRS but you get the data directly from the database.