Get Session Count over multiple NST’s

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.

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:

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.

One thought on “Get Session Count over multiple NST’s

  1. Pingback: Get Session Information for all NST’s – PowerShell Version - Microsoft Dynamics NAV Community

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.