Home > OCS 2007 / Lync 2010 > OCS 2007 R2: Query PIC statistics with PICStats.sql

OCS 2007 R2: Query PIC statistics with PICStats.sql

In the older Resource Kit Tools of OCS 2007 was a nice SQL query script added which is not available anymore in the OCS 2007 R2 Resource Kit Tools.  The query script Picstats.sql is to report statistics related to public IM use. For example, you can use the script to determine the average number of public IM users that are in the contact list of your deployed users.

On the Standard Edition server navigate in the command prompt to the SQL Binn folder: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn

Execute:

osql.exe -E -S OCS001\RTC -n -d rtc -i c:\temp\picstats.sql

The -E flag Uses a trusted connection, -S Specifies the instance, -n hides the line numbers of the script, -d issues a USE db_name statement when osql is started, -i Identifies the file that contains a batch of SQL statements.

You can get the Picstats.sql from the old resource kit: http://www.microsoft.com/downloads/details.aspx?FamilyID=b9bf4f71-fb0b-4de9-962f-c56b70a8aecd Or copy the script below and save it as Picstats.sql. I corrected line 107 in the script: “from HomedUser as h with(readpast)” -> “from HomedResource as h with(readpast)”


set nocount on
set transaction isolation level read committed

select PicDomain as [PIC Domain],
       ltrim(str(MinContactsPerUser, 7,2))   as [Min Contacts/User],
       ltrim(str(MaxContactsPerUser, 7,2))   as [Max Contacts/User],
       ltrim(str(AvgContactsPerUser, 7,2))   as [Avg Contacts/User],
       ltrim(str(StdevContactsPerUser, 7,2)) as [Stdev Contacts/User]
from (
    -- Statistics on AOL contacts
    select 'AOL' as PicDomain,
           min(ContactCount) as MinContactsPerUser,
           max(ContactCount) as MaxContactsPerUser,
           avg(cast(ContactCount as dec(15,3))) as AvgContactsPerUser,
           stdev(ContactCount) as StdevContactsPerUser
      from (select count(*) as ContactCount
              from Contact as c with(readpast)
             inner join Resource as r with(readpast) on r.ResourceId = c.BuddyId
             where r.UserAtHost like N'%aol.com'
             group by c.OwnerId) as a
    union all
    -- Statistics on Yahoo! contacts
    select 'Yahoo!' as PicDomain,
           min(ContactCount) as MinContactsPerUser,
           max(ContactCount) as MaxContactsPerUser,
           avg(cast(ContactCount as dec(15,3))) as AvgContactsPerUser,
           stdev(ContactCount) as StdevContactsPerUser
      from (select count(*) as ContactCount
              from Contact as c with(readpast)
             inner join Resource as r with(readpast) on r.ResourceId = c.BuddyId
             where r.UserAtHost like N'%yahoo.com'
             group by c.OwnerId) as a
    union all
    -- Statistics on MSN contacts
    select 'MSN' as PicDomain,
           min(ContactCount) as MinContactsPerUser,
           max(ContactCount) as MaxContactsPerUser,
           avg(cast(ContactCount as dec(15,3))) as AvgContactsPerUser,
           stdev(ContactCount) as StdevContactsPerUser
      from (select count(*) as ContactCount
              from Contact as c with(readpast)
             inner join Resource as r with(readpast) on r.ResourceId = c.BuddyId
             where r.UserAtHost like N'%hotmail.%'
                or r.UserAtHost like N'%msn.com'
                or r.UserAtHost like N'%sympatico.ca'
                or r.UserAtHost like N'%webtv.net'
                or r.UserAtHost like N'%passport.com'
                or r.UserAtHost like N'%messengeruser.com'
             group by c.OwnerId) as a
    ) as b

-- How many users have at least one PIC contact?  (and percentage of total this represents.)
declare @CountWithPic int,
        @CountInEnterprise int
select @CountWithPic = count(*)
  from (select count(*) as ContactCount
          from Contact as c with(readpast)
         inner join Resource as r with(readpast) on r.ResourceId = c.BuddyId
         where r.UserAtHost like N'%aol.com'
            or r.UserAtHost like N'%yahoo.com'
            or r.UserAtHost like N'%hotmail.%'
            or r.UserAtHost like N'%msn.com'
            or r.UserAtHost like N'%sympatico.ca'
            or r.UserAtHost like N'%webtv.net'
            or r.UserAtHost like N'%passport.com'
            or r.UserAtHost like N'%messengeruser.com'
         group by c.OwnerId) as a
 where ContactCount >= 1
select @CountInEnterprise = count(*) from ResourceDirectory
select @CountWithPic      as [Users with at least one PIC Contact],
       @CountInEnterprise as [Total Enterprise Users],
       case when @CountInEnterprise > 0
            then ltrim(str(cast(@CountWithPic as dec(15,3)) / @CountInEnterprise * 100.0, 7,2))
            else null
            end           as [% with at least one PIC Contact]

-- Relative percentage of PIC contacts for a user
select
    ltrim(str(min(b.PercentPic), 7,2))                    as [Min PIC %/User],
    ltrim(str(max(b.PercentPic), 7,2))                    as [Max PIC %/User],
    ltrim(str(avg(cast(b.PercentPic as dec(15,3))), 7,2)) as [Avg PIC %/User],
    ltrim(str(stdev(b.PercentPic), 7,2))                  as [Stdev PIC %/User]
from (
    select
        OwnerId, TotalContacts, PicContacts,
        (cast(a.PicContacts as dec(15,3)) / a.TotalContacts * 100.0) as PercentPic
    from (
        select h.ResourceId as OwnerId,

            (select count(c2.BuddyId)
               from Contact as c2 with(readpast)
              where c2.OwnerId = h.ResourceId) as TotalContacts,

            (select count(*) as ContactCount
               from Contact as c3 with(readpast)
              inner join Resource as r with(readpast) on r.ResourceId = c3.BuddyId
              where c3.OwnerId = h.ResourceId
                and (   r.UserAtHost like N'%aol.com'
                     or r.UserAtHost like N'%yahoo.com'
                     or r.UserAtHost like N'%hotmail.%'
                     or r.UserAtHost like N'%msn.com'
                     or r.UserAtHost like N'%sympatico.ca'
                     or r.UserAtHost like N'%webtv.net'
                     or r.UserAtHost like N'%passport.com'
                     or r.UserAtHost like N'%messengeruser.com')) as PicContacts

        from HomedResource as h with(readpast)
        ) as a
    where TotalContacts > 0
    ) as b

Output:

Advertisements
Categories: OCS 2007 / Lync 2010
  1. alin
    February 15, 2011 at 4:11 pm

    Hi thanks for the sql querry. Can this be modified to report the users who have PIC contacts ?

    Thanks in advance.

    A.

  2. Dino Caputo
    February 15, 2011 at 4:11 pm

    Thanks for this. I was wondering if anyone knew how to modify this script to also identify and dump the users (by sip uri or accountname) that have at least one PIC contact in their buddylist?

  1. No trackbacks yet.

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

%d bloggers like this: