Skip to content

Instantly share code, notes, and snippets.

@nanoDBA
Last active December 11, 2020 21:30
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save nanoDBA/80c8deb7253d0989fa9a7504ab066903 to your computer and use it in GitHub Desktop.
Avoid running the query against instances that may be down and time out, as well as adding some output that can be seen when looking at job history when scheduling the PowerShell job using SQL Agent
# adapted from https://gist.github.com/potatoqualitee/20a3f84e987cafe03e0ebe3b4d593c65#file-process-insert-ps1
# Specify your servers
$servers = "sql2014","sql2012","sql2016","sql2017"
# Setup the T-SQL
$sql = "SELECT SERVERPROPERTY('ServerName') AS SqlInstance, login_name as [Login], [host_name] as Host,
DB_NAME(p.dbid) as [Database], s.[program_name] as Program, s.login_time as LoginTime
FROM sys.dm_exec_sessions s inner join sys.sysprocesses p on s.session_id = p.spid
WHERE p.dbid is not NULL
and DB_NAME(p.dbid) != 'tempdb'
and SERVERPROPERTY('MachineName') != [host_name]
and s.[program_name] not like 'dbatools%'
and s.[program_name] not like 'Microsoft SQL Server Management Studio%'
and s.[program_name] not like '\[%].Net SqlClient Data Provider' ESCAPE '\'" # ignore sharepoint guid junk
# Collect relevant results
foreach ($instance in $servers) {
# Write-Output will attempt to write to the job history in SQL Agent
Write-Output "Attempting to connect to $instance ..."
# Ensure instance is online before trying to run query
if ( (Test-DbaConnection $instance ).ConnectSuccess ) {
$results = Invoke-DbaQuery -SqlInstance $instance -Query $sql
}
if ($results) {
Write-Output $instance
Write-Output "$($results.count) row(s)"
Write-DbaDataTable -InputObject $results -SqlInstance localhost -Database inventory -Table watchlogins
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment