Find SQL server last used (date time) by ActiveDirectory "sqlsrv" service accout

Find SQL server last used (date time) by ActiveDirectory “sqlsrv” service account by using powershell

we have 110 sql server and 38 Dc’s

now i want to find the sql server which is used by Active Directory sqlsrv service account.

note: I can find date and time when AD “sqlsrv” used in the infra on object attribute by going to dsa.msc

Please help me to find the sql server name.

Note: sqlsrv is not local account it is active directory service account

<p style=“text-align: left;”>Without fully writing the whole script for you :slight_smile: … this may get you started and point you in the general direction. Always feel free to come back with what you’ve written so far if you need further assistance\guidance.</p>
This MS article outlines some of the more useful logon codes to look for in the Security Event log (at the bottom), although it is an old article. May give you pointers to assist your research.

This rough one liner is just one option to get you the last user logged onto a computer specified by $computer variable. There may well be other methods using other technologies\processes etc.

Get-WinEvent -Computer $computer -FilterHashtable @{Logname='Security';ID=4672} -MaxEvents 1 | 
select TimeCreated, @{N='Last User';E={$_.Properties[1].Value}}

I’ve used ID 4672 here, as this references any account assigned to an “administrator equiv”, more commonly used for service accounts.

The one-liner I’ve provided above has the advantage that it should be quite straightforward to embed within a for-each loop that runs through your computer accounts within AD.