OCS/UC - How to get user presence information (userstate and machinestate)
Recently I received a request to prepare a sample code that can produce user presence from OCS server. Users can change their state as “Away” or “Busy” when in reality they are very much available. OCS tracks this in backend DB as userstate (state set by the user) and machinestate (state he really is in).
I found 2 articles showing how to retrieve this information. First one uses UCMA:
https://www.nikhedonia.com/notebook/entry/how-to-get-presence-via-ucma/
And the second one uses backend DB directly:
https://mikestacy.typepad.com/mike-stacys-blog/sql/
I liked the 2nd one as it looks easier to implement. But as you have seen (as shown in the article) getting userstate and machinestate is little tricky. So I created one table and one stored procedure under database RTC (OCS backend db) as given below:
USE [rtc]
GO
/****** Object: Table [dbo].[Presence] Script Date: 07/29/2010 08:42:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Presence](
[sip_uri] [nvarchar](50) NOT NULL,
[presence_machine] [nvarchar](20) NOT NULL,
[machine_duration] [int] NOT NULL,
[presence_user] [nvarchar](20) NOT NULL,
[user_duration] [int] NOT NULL
) ON [PRIMARY]
GO
-- user_duration and machine_duration shows the duration for the present state in seconds.
USE [rtc]
GO
/****** Object: StoredProcedure [dbo].[DiagShowPresenceState] Script Date: 07/29/2010 08:43:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DiagShowPresenceState]
-- Add the parameters for the stored procedure here
@_Publisher nvarchar(4000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @CurrentTime datetime
set @CurrentTime = getutcdate()
-- Insert statements for procedure here
select cast(substring(i.Data, 0, 256) as varchar(256)) as Data,
datediff(second, i.LastPubTime, @CurrentTime) as AgeInSecs
from dbo.PublishedCategoryInstanceView as i
inner join dbo.CategoryDef as d
on (d.CategoryId = i.CategoryId)
where i.PublisherId = (select ResourceId from dbo.Resource
where UserAtHost = @_Publisher)and d.Name='state'
END
GO
Here is the code of my console application that inserts information about each users into “Presence” table:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleAppOCSPresence
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=rtc;Data Source=DEMO2010A\\RTC";
SqlConnection rtcSqlConnection = new SqlConnection(connectionString);
string selectString = "SELECT UserAtHost FROM dbo.Resource ";
SqlCommand rtcSqlCommand = rtcSqlConnection.CreateCommand();
rtcSqlCommand.CommandText = selectString;
SqlDataAdapter rtcSqlDataAdapter = new SqlDataAdapter();
rtcSqlDataAdapter.SelectCommand = rtcSqlCommand;
DataSet rtcDataSet = new DataSet();
rtcSqlConnection.Open();
string dataTableName = "Resource";
rtcSqlDataAdapter.Fill(rtcDataSet, dataTableName);
DataTable rtcDataTable = rtcDataSet.Tables[dataTableName];
foreach (DataRow rtcDataRow in rtcDataTable.Rows)
{
string userState = "";
string machineState = "";
int userDuration = 0;
int machineDuration = 0;
//Console.WriteLine("SIP URI = " + rtcDataRow["UserAtHost"]);
SqlCommand rtcStateSqlCommand = new SqlCommand("dbo.DiagShowPresenceState", rtcSqlConnection);
rtcStateSqlCommand.CommandType = CommandType.StoredProcedure;
rtcStateSqlCommand.Parameters.Add(new SqlParameter("@_Publisher", SqlDbType.NVarChar, 4000, "@_Publisher"));
rtcStateSqlCommand.Parameters[0].Value = rtcDataRow["UserAtHost"];
SqlDataReader rtcStateSqlReader = rtcStateSqlCommand.ExecuteReader();
while (rtcStateSqlReader.Read())
{
if (userState == "")
{
if (rtcStateSqlReader["Data"].ToString().IndexOf("userState") > -1)
{
userState = rtcStateSqlReader["Data"].ToString().Substring(rtcStateSqlReader["Data"].ToString().IndexOf("<availability>") + 14, 5);
//Console.WriteLine(userState);
userState = userState.Replace('<', ' ').Trim();
userDuration = Convert.ToInt32(rtcStateSqlReader["AgeInSecs"]);
}
}
if (machineState == "")
{
if (rtcStateSqlReader["Data"].ToString().IndexOf("machineState") > -1)
{
machineState = rtcStateSqlReader["Data"].ToString().Substring(rtcStateSqlReader["Data"].ToString().IndexOf("<availability>") + 14, 5);
//Console.WriteLine(machineState);
machineState = machineState.Replace('<', ' ').Trim();
machineDuration = Convert.ToInt32(rtcStateSqlReader["AgeInSecs"]);
}
}
}
rtcStateSqlReader.Close();
if (userState != "")
{
String insertString = @"INSERT INTO dbo.Presence(sip_uri, presence_machine, presence_user, machine_duration, user_duration) VALUES('" + rtcDataRow["UserAtHost"] + "','" + machineState + "','" + userState + "'," + machineDuration + "," + userDuration + ")";
SqlCommand insertCmd = new SqlCommand(insertString, rtcSqlConnection);
insertCmd.ExecuteScalar();
}
}
rtcSqlConnection.Close();
//Console.ReadLine();
}
}
}