Query for Disabled Accounts

banoo

Trying to write a query for disabled user accounts similar to one I have for locked out accounts.  Then I can add a widget with the information.  Here is what I have but I am missing something.  Any ideas?

SELECT DISTINCT ToLocal(TimeGeneratedUtc) as LocalTime

,SubString(message, CHARINDEX('Account Name:',message,CHARINDEX('Target Account:',message))+15, CHARINDEX('Additional Information:',message)-31-CHARINDEX('Account Name:',message,
CHARINDEX('Target Account:',message))+15) as Account
,SUBSTRING(message,CHARINDEX('Account Domain:',message,CHARINDEX('Account Domain:',message))+15, CHARINDEX('Logon ID:',message,CHARINDEX('Account Domain:',message))- (CHARINDEX('Account Domain:',message,CHARINDEX('Account Domain:',message))+15)) AS Domain

FROM Orion.APM.WindowsEvent

WHERE Message LIKE 'A user account was disabled.%'
--AND Message NOT LIKE ''
AND TimeGeneratedUtc BETWEEN ADDDATE('Day',-1, GETUTCDATE()) AND GETUTCDATE()

banoo

I fixed it, so if anyone else needs to do it, here ya go...

SELECT DISTINCT TOLOCAL (TimeGeneratedUtc) AS LocalTime

,SUBSTRING(message,CHARINDEX('Account Name:',message,CHARINDEX('Account Domain:',message))+15, CHARINDEX('Security ID:',message,CHARINDEX('Account Name:',message))- (CHARINDEX('Target Account:',message,CHARINDEX('Account Name:',message))+10)) AS Account

,SUBSTRING(message,CHARINDEX('Account Name:',message,CHARINDEX('Account Name:',message))+13, CHARINDEX('Account Domain:',message,CHARINDEX('Account Name:',message))- (CHARINDEX('Subject:',message,CHARINDEX('Subject:',message))+87)) AS Who_Disabled

FROM Orion.APM.WindowsEvent
WHERE Message LIKE 'A user account was disabled.%'
--AND Message NOT LIKE ''
AND TimeGeneratedUtc BETWEEN ADDDATE('Day',-14, GETUTCDATE()) AND GETUTCDATE()

ORDER BY LocalTime DESC;