Generate a Simple Group Chat Report

I get all sorts of odd, and fun, requests. Recently, I was asked to provide a quick and dirty report on which users are configured for Group Chat and to list the rooms they can join.

I really don’t want to get too good at this SQL stuff, but this one was good fun.

$SQLQuery = “SELECT isMember,isManager,prinID,prinName,prinDisabled,nodeName,nodeDesc,disabled,visibility

FROM [GroupChatDB].[dbo].[Exp_RoleView]

       INNER JOIN [GroupChatDB].[dbo].[tblPrincipal] on Exp_RoleView.principalId=tblPrincipal.prinGuid

           INNER JOIN [GroupChatDB].[dbo].[tblPrincipalType] on tblPrincipal.prinTypeID=dbo.tblPrincipalType.ptypeID

           INNER JOIN [GroupChatDB].[dbo].[tblNode] on Exp_RoleView.nodeDbId=tblNode.nodeID

      ORDER BY prinName”

 

$Connection = new-object system.data.sqlclient.sqlconnection

$Connection.connectionString=”Data Source=VSQLNCSB0010CDC\N1SQL01;Initial Catalog=GroupChatDB;Integrated Security=SSPI”

$Connection.open()

$Command = $Connection.CreateCommand()

$Command.Commandtext = $SqlQuery

$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command

$Dataset = New-Object System.Data.Dataset

$DataAdapter.Fill($Dataset)

$Dataset.Tables[0] | Export-CSV ConnectionList.csv -notype

$Connection.close()

$Connection = $null

 

About these ads
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Generate a Simple Group Chat Report

  1. Pingback: Weekly IT Newsletter – July 28-August 1, 2014 | Just a Lync Guy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s