Sometimes you need to see for different instances how many sessions are connected.
You could achieve this with piping multiple PowerShell statements into each other, but not always.
On our DEV server, of which we have multiple server, we have around 90 customer instances on the largest one. If I want to know all RTC sessions, I would use something like this:
get-NAVServerSession -ServerInstance InstanceName
Which works great for 1 instance, after loading the correct AdminTools off course. (See here how)
If you wish to list the instances for multiple NST’s, you could use the following option:
Get-NAVServerInstance | Get-NAVServerSession
Again, great option for multiple NST’s, you request the Server Instances, and next, for each instance, you request the Sessions.
Problem here: different versions… you need the exact same version of the management dll for the server, to be able to request the sessions.
Now I present to you an alternative way to request the sessions.
DECLARE @command VARCHAR(MAX); create table #temptable ( DBName nvarchar(250) not null, [Active Sessions] int not null ); set @command = 'use [?] IF OBJECT_ID (N''Active Session'', N''U'') IS NOT NULL insert into #temptable select ''?'',(select count(*) from [Active Session]) as [Active Sessions]' exec sp_MSforeachdb @command delete from #temptable where [Active Sessions]=0 select * from #temptable select sum([Active Sessions]) as [Total Sessions] from #temptable drop table #temptable
I guess your first reaction will be: “Wait, this isn’t PowerShell”
No, it isn’t PowerShell, this script is SQL script. This is what it will do:
– Create a temporary SQL Table in Memory (#temptable)
– Select the sum of all Active Sessions for each database (if that table exists)
– Insert that result into the temp table
– Delete the databases without Active Sessions out of the temp table
– Present the result
– Present the total number of sessions
– Remove the temp table
It might not be the most ideal solution, but it will certainly give you a nice overview of the active sessions.
You could off course run this statement from PowerShell using the Invoke-SQLCommand PowerShell command.
If you need more detail, you could also expand the temp table fields, and select specific information into the temp table.
I helped a bit and selected specific information into the list in the following sample:
DECLARE @command VARCHAR(MAX); create table #temptable ( DBName nvarchar(250) not null, [Server Instance ID] int not null, [Session ID] int not null, [User ID] varchar(132) not null, [Client Type] int not null, [Login Datetime] datetime not null, [Client Computer Name] nvarchar(250) not null ); set @command = 'use [?] IF OBJECT_ID (N''Active Session'', N''U'') IS NOT NULL insert into #temptable select ''?'',[Server Instance ID],[Session ID],[User ID],[Client Type],[Login Datetime],[Client Computer Name] from [Active Session]' exec sp_MSforeachdb @command select * from #temptable select count(*) as [Total Sessions] from #temptable drop table #temptable
I hope you enjoy this one.
Please feel free to alter the code as you like 🙂
Also curious about your use of this script and alternate versions.