For example: I have common name between all the users. You can have common “Department”, “OU” or something else which can be used to filter the users.

Get-CsAduser -LDAPFilter “(name=sfb*)” | Enable-CsUser -RegistrarPool “poolfqdn” -SipAddressType EmailAddress

Let me start by saying that I am NOT even half as good as the great Mr. Pat Richard when it comes to PowerShell.  If he is the major league of PowerShell, on my best day I’m a solid backup player for a little league team.  Therefore the PowerShell that I’m about to share could be a lot better but think of it as a first step to something better in the future.  If you have any improvements feel free to add them to the comments.

Over the last few weeks I’ve been thinking about a way to assist a client in the draining of their servers for the purposes of patching.  As it is well documented, the draining process will leave services up and running in a paused state until all active conversations are completed.  The issue I’ve ran into a few times however is that I’ve waited over 24 hours and some services (typically the IM MCU) are still up and running.  Often times, this is just a random person who left a conference IM window open on their computer but none the less I don’t want to bounce the box if there is a legitimate conference going on.

So I started digging around the database (because that is where all the cool stuff happens in Lync) and knowing exactly where all of the active conference are happening decided to start looking for a way to display that information via PowerShell.  I decided the simplest way was to prompt for a pool, look-up the members of that pool and loop through each of the front-end servers.

The SQL is the easy part:

SELECT ActiveConference.ConfId AS ‘Conference ID’, ActiveConference.Locked, Participant.UserAtHost AS ‘Participant’, Participant.JoinTime, Participant.EnterpriseId, ActiveConference.IsLargeMeeting AS ‘Large Meeting’ FROM ActiveConference INNER JOIN Participant ON ActiveConference.ConfId = Participant.ConfId

Each front-end server in the RTCDYN database has an ActiveConference table which contains all of the active conferences running on that particular server.

And the Participants table contains all of the active participants for the call with a corresponding conference ID.  Therefore all we need to do is join the two tables together and we have a complete list.  Using this information I went ahead and created this short script:

Download Here: Get-CSActiveConferences

You will need to rename the script to .ps1 from .txt.  Once you have renamed the file, go ahead and run .Get-CsActiveConferences from the Lync Management Shell.  It will prompt you for the name of the pool you want get results from.  Your results will look like:

NOTE: You may need to adjust firewall rules if you don’t allow your local SQL databases to be accessible remotely.  Port 1433 is the default SQL port.

All meetings will be grouped by Conference ID.  By default I’m showing the Participant, Join Time and if it is a Large Meeting.  You can always edit the PowerShell file to display other information if needed.  If you have any good requests or changes please just pass them along and I’ll post them.

http://masteringlync.com/2013/11/19/list-all-active-conferences-via-powershell/

Howdy,

I had free time in the holidays that I spent playing around with databases of Lync and took notes of what I found and learned and decided to put them in an article to be used as a…let’s say Lync databases anatomy, I’ll be talking about a simple Lync pool installation and databases included with it.

Overview of Lync database architecture

First things first, in Lync there are couple of places that databases are usually installed, depend on your Lync pool design

In My lab I have an Enterprise front end pool with a SQL backend server, so I’ll be talking about two locations where databases are installed on the SQL backend and the front end servers. In case of a standard edition front end pool those databases are installed in one location – the front end.

PowerShell command Get-CsConfigurationStoreLocation can help locating the server used as Lync Backend

RTC instance, RTC and RTCLOCAL… huh?

for some engineers with limited understanding of SQL (like me), they might get confused when talking about too many use of the word RTC in a conversation, and if you include RTCLOCAL to the conversation they just start fade away

so let’s clear things up shall we?

taking this definition and apply it to Lync databases we get the following

NOTE: RTC is the default name of the Lync instance, but it can be changed during deployments.

you can learn more about RTC & RTCLOCAL from my previous article Simple understanding for Lync CMS

Lync Databases

Now let’s talk about the type of each of those databases you see and what are they used for. In Lync there are number of database types I used the way TechNet divided them with small different (full list can be found here)

Application Databases

following databases are considered “Application” databases

• CPsdyn: contain dynamic information used by Call Park application like “Orbits”

• RGSConfig: contain the configuration of the Response Groups, like Agents, Workflows, Queues, Holidays…etc.

• RGSDyn: contain dynamic “live” information used by the Response Groups

Archiving & Monitoring

I think from the name it is obvious what are they used for, just pay attention that usually those databases are installed on a different SQL instance, not the default one.

• LCSLog: archive information about Instant messaging sessions, P2P calls and conferencing used by the archiving server.
• LCSCdr: store the call details records used by the monitoring server.
• QoEMetrics: store information about Quality of Experience used by the monitoring server.

Central Management

• XDS: this is the Lync Central management Store (CMS) database, it holds Lync topology, configuration and polices (more details: Simple understanding for Lync CMS)
• Lis: store location information (network subnets, ports, Switches…etc.) that is used by Lync Location services for E9-1-1 feature.

Lync Core

Following databases are usually installed on both the RTC and RTCLOCAL instances

• RTC: as mentioned before, it store persistent user information (contact list, scheduled conferences…etc.)
• RTCDyn: Store dynamic “live” data about users (current presence, endpoints user logged in from…etc.)

Users

• RTCAB: store the Lync address book information used by Lync address book services
• RTCXDS: store a back up of user data

that’s all, a small quick and to the point explanation of Lync Databases types and functionalities, hope you found it helpful.

Happy New year everyone

https://lyncdude.com/2014/12/31/understand-lync-databases/#more-1863

https://technet.microsoft.com/ko-kr/library/gg398479.aspx

Update May 2015: The process below is the same for Skype for Business.

Issues with the Lync Address book are common to all Lync admins. Troubleshooting these issues isn’t as easy as it looks. To better troubleshoot or change the information stored in the Lync Address Book, you need to be aware of all the steps it takes in creating the Lync Address Book. To simplify the explanation of how the address book ends up at the users side, I created the following flowchart of this process.

Lync Address Book Sync

Step 1 – User Replicator

AbAttribute Table

Information about your Users, Contacts and Groups is stored in Active Directory (AD). This information is retrieved from AD by the User Replicator and placed in the user database into the RTCab database. The table AbUserEntry is used to store this information and it contains two colums, one for the UserGUID and one for all the UserData. What kind of data is placed in the column is defined by the entries in the AbAttribute table. If this table is empty the User Replicator will skip the AbUserEntry filling process (no users in the Lync Address Book). With each attribute a flag is also present. This flag determines what happens with translation between AD and the AbUserEntry table or, if a certain AD attribute is present, to include or exclude the user from adding it to the AbUserEntry table. You can control the AbAttribute table by using the ABSConfig.exe tool (Lync 2013 Resource Kit). For more information regarding the attributes and flags see: Administering the Address Book Service, Lync 2013. After changing the AbAttribute table, make sure you run Update-CSAddressBook to regenerate the address book.

By default all domains that are found will be replicated and will be replicated alphabetically from A to Z. If there are some domains that you don’t want to replicate to the Lync Address Book, use the ADDomainNamingContextList parameter (Set-CsUserReplicatorConfiguration) to add only the domains you want to synchronize. Use the following commands:

# Add domains
Set-CsUserReplicatorConfiguration -Identity global -ADDomainNamingContextList @{Add="dc=domain1,dc=local","dc=domain2,dc=local"}

# Remove domain
Set-CsUserReplicatorConfiguration -Identity global -ADDomainNamingContextList @{Remove="dc=domain2,dc=local"}

Note: In previous versions of Lync it’s still imperative to use the Update-CSUserDatabase powershell command. In Lync 2013 this process runs at a configured interval. To retrieve how often this is done or find more settings, use Get-CsUserReplicatorConfiguration.

Step 2 – Address Book

Information of your users, contacts and groups is now in the Lync database. The next step is to create the actual address book files. These files are generated by the Front End with the Address Book Server task and stored in the Lync file share defined in your topology . In this share you’ll find the following subfolder: \ServerLyncShare1-WebServices-1ABFiles. When you’re in a single tenant Lync configuration you’ll only find the tenant directory 00000000-0000-0000-0000-000000000000, which is the default for the msRTCSIP-TenantId attribute in AD. This attribute stores the unique identifier of the tenant.

In this subdirectory you’ll find all *.lsabs and *.dabs files which your clients will download. The address book files are generated by default each day at  1:30 AM. When the process starts, it uses the company_phone_number_normalization_rules.txt to translate telephone numbers to E.164 format. Remember that this file will translate all numbers, even numbers that are already in E.164 format if rules are applicable, like the msRTCSIPLine attribute. The text file is used when it’s placed in the ABFiles directory. By default this text file doesn’t exist. Numbers not being translated correctly are placed in the tenant directory in a file called Invalid_AD_Phone_Numbers.txt. Check your Lync eventviewer for information or errors regarding the Lync Address Book Server, filter on LS Address Book Server.

Log Name:      Lync Server
Source:        LS Address Book Server
Event ID:      21034
Task Category: (1008)
Level:         Warning
Keywords:
Classic Description:
One or more phone numbers failed to normalize. 300 total numbers failed to normalize.  They are listed in the text file: ‘\ServerLyncShare1-WebServices-1ABFiles0000000-0000-0000-0000-0000000000000000000-0000-0000-0000-000000000000Invalid_AD_Phone_Numbers.txt’ Cause: One or more phone number attributes in Active Directory contained text that could not be normalized.  Normalization rules are contained in the optional Company_Phone_Number_Normalization_Rules.txt file located in the output location or in the generic rules built into Address Book Server.  Refer to the documentation for a description of the built-in generic normalization rules.  Use the ABServer -dumpRules command to see all the rules that Address Book Server is currently configured with. Resolution: Either create a Company_Phone_Number_Normalization_Rules.txt file in the output location and make sure it covers all cases found in your Active Directory deployment or fix the invalid phone number(s) in the Active Directory record(s).

Use Get-CsAddressBook to retrieve all the Address Book settings. For the use of all normalization rules make sure the UseNormalizationRules is set to True.

You can make the Address Book files from the Lync share readable by exporting them, use the following cmd:

abserver -dumpfile “\ServerLyncSharen-WebServices-nABFiles0000000-0000-0000-0000-0000000000000000000-0000-0000-0000-000000000000F-0ef3.lsabs” c:TempExport-ABS.txt

Note: An un-documented feature, found by  Trevor Miller, when there is a Company_Phone_Number_Normalization_Rules.txt file placed in the installation directory of Lync (ex. C:Program FilesMicrosoft Lync Server 2013ServerCore), this file will be used.

Step 3 – Clients

Now that your Address Book files are ready, clients can download them, if the AddressBookAvailability is set to download the address book this is the default. Make sure you have a good reason to make the address book available locally, as the preferred method would be to set the addressbookavailability to WebSearchOnly.

If you have set the availability to download, when a client starts it will connect to the internal or external Address Book Server URL:

• URL Internal From Server;https://LyncpoolFQDN:443/abs/handler
• URL External From Server;https://LyncExternalWebServiceFQDN:443/abs/handler

Use the Test-CsAddressBookService and Test-CsAddressBookWebQuery commands to check errors regarding the configuration. The client will download the files by default within 0 to 60 minutes. You can change this by using the GalDownloadInitialDelay (DWORD) registry key. Setting this key to the value of 0 will enforce a direct download. You can find or place this key in:

• Lync 2010 : HKLM or HKCU SoftwarePoliciesMicrosoftCommunicator
• Lync 2013:  HKLM or HKCU SoftwarePoliciesMicrosoftOffice15.0Lync

When Lync has downloaded the files, you will have 2 files in the userprofile, namely GalContacts.db and GalContacts.db.idx. In Lync 2013 this is a file called ABS__sipdomain.cache. The cache file is updated when the user logs into Lync or after 24 hours when the cache is valid. On a Windows 7 or higher machine these files will be located in the folder:

Lync 2010: %userprofile%AppDataLocalMicrosoftCommunicatorsip_user@domain
Lync 2013: %userprofile%AppDataLocalMicrosoftOffice15.0Lyncsip_user@domain

Common issues

Now that you know how the process works, here are some issues regarding the Lync Address Book.

Lync User is not being displayed in the Lync Address Book

You have a user who is Lync enabled. Everything is working, the user can log in, but when you search for this user on your Lync client no name is returned. The most common reason for this behaviour is based on an issue with step 1. When the User Replicator runs, it checks the attribute msExchangeHideFromAddressBook. This is the Exchange attribute for hiding users from the Global Address List (GAL). If this is set to TRUE, which indicates it doesn’t need to appear on the GAL, Lync will also ignore this user for the Address Book.

Lync Response Group number to name

You created a Lync Response Group, you can find it in the address book using it’s name, but when you type the phone number the Response Group isn’t resolved. Check the Invalid_AD_Phone_Numbers.txt file. See if your Response Group number is placed here. If it is, you haven’t used the E.164 format in the Display Number field. Change the field to the correct format or add a valid translation to the company_phone_number_normalization_rules.txt. If you leave the Display Number field empty, it will use the lineuri (telephone number) field to be placed as phone number in the address book. This also applies to extensions not being correctly translated to the Response Group name.

The address book is preparing to synchronize

If you haven’t set the GalDownloadInitialDelay registry, this will be displayed at a maximum of 0 to 60 minutes. If it keeps displaying the message try the following:

1. Exit the Lync client
2. Delete the local address book files (see step 3)
3. Restart the Lync client
   Or a script to automatically delete the files (The Expta Blog).

Try using the WebSearchOnly value in the Lync client policy. This will not use the local address book files anymore, but will only use the Address Book Web Query (ABWQ).

Certain phone numbers from Active Directory not displayed on the contact card

When you view a contact card in Lync, you’re missing phone numbers which you know you placed in AD.

First make sure these numbers of the user are in E.164 format. If it’s not in E.164 format, use the Company_Phone_Number_Normalization_Rules.txt (Step 2) to normalize the numbers correctly. Run the Update-CSAddressBook command, make sure that your Lync client updates the Lync Address Book (step 3) and the numbers should now be visible in Lync.

Something else…?

There are a lot more questions, tools and issues regarding the address book. Feel free to contact me or drop a comment.

And even more information about the Address Book:

• Lync Address Book Service 2010 (Resource Kit) – Chapter_01_Address_Book_Service.doc
• Best Practices Enterprise Voice (Ken Lasko)
• WebSearchOnly

Incoming search terms:

• ItsallabouttheLyncAddressBook-Fots-Lync/SfB
• abwq normalized numbers
• lync common area phone global address book
• GET /abs/handler/F-5094 lsabs - 443
• lync 2010 websearchonly showing wrong info
• lync 2013 client address book sip
• lync web based address book
• sfb 2015 contacts not appearing
• lync address name status common name
• skype for business 2015 some users not showing in the address book

$photo = [byte[]](Get-Content "C:\photo\test01.jpg" -Encoding byte)
Set-ADUser 계정명 -Replace @{thumbnailPhoto=$photo}

https://joinlync.mani4u.co.kr/meet/sojuhyang/HCTHWVVL?sl=1

모임 URL 뒤에 ?sl=1 옵션을 붙여준다.

Get-Counter "\LS:USrv - Endpoint Cache\USrv - Active Registered Endpoints","\LS:USrv - Endpoint Cache\USrv - Active Registered Users" | Select-Object -ExpandProperty CounterSamples | Format-Table Path,CookedValue -Auto

Get-Counter "\LS:USrv - Endpoint Cache\USrv - Active Registered Endpoints","\LS:USrv - Endpoint Cache\USrv - Active Registered Users" -ComputerName frontend.contoso.com | Select-Object -ExpandProperty CounterSamples | Format-Table Path,CookedValue -Auto

A question that comes up from customers from time to time is how do I get a list of what users are actually using OCS/Lync?  While there’s no built in report to easily tell you what users are actually signing into the environment, there is some information stored in SQL that you can use to help figure out adoption rate in your environment.  The information we’re looking for is stored in the LastNewRegisterTime column in the HomedResourceDynamic table in the rtcdyn database.  You can run the following queries* to pull back the user’s SIP URI and their last registration time:

For Lync Server 2010/2013

USE rtcdyn
SELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTime
FROM rtcdyn.dbo.HomedResourceDynamic
INNER JOIN rtc.dbo.Resource on rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
INNER JOIN rtcdyn.dbo.RegistrarEndpoint ON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
WHERE IsServerSource = 0
ORDER BY UserAtHost

Which produces the following output:

For OCS 2007 R2

USE rtcdyn
SELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTime
FROM rtcdyn.dbo.HomedResourceDynamic
INNER JOIN rtc.dbo.Resource ON rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
ORDER BY UserAtHost

Which produces the following output:

Note: A user with a LastNewRegisterTime of NULL is a user that has never logged in, but has been added to someone’s contact list.  You would want to remove these entries from your exported results.

While the query to gather the information is very similar the SQL instance that you connect to to retrieve this information is different depending on which version you’re using.  In OCS 2007 R2 all of this information was stored in the rtcdyn database on the SQL instance that you defined when first creating the pool.  Because of the changes to the registrar functionality in Lync Server 2010 to support survivability, the information that we’re looking for has moved to the RTCLOCAL SQL instance that is stored on each registrar.

You can see this if you compare the HomedResourceDynamic table in the rtcdyn database between the Front End Pool SQL Instance and one of the Front End Server’s RTCLOCAL SQL Instance:

Front End Pool SQL Instance

RTCLOCAL SQL Instance

For row 2, OwnerId 12 corresponds to my Lync test user that has logged into the client.  You can see that the information that we’re looking for, LastNewRegisterTime, only gets populated in the RTCLOCAL SQL instance on the Front End Server.  So this means that in Lync Server 2010 you don’t have one place you can go for all of the users homed on that pool.  You will need to run the query and aggregate the data from every registrar in the pool, as well as any SBA/SBS deployed.  The other issue that comes up in an Enterprise Edition pool with multiple Front End Servers is that when users fail-over to another Front End Server in the pool, there is a record created in that Front End Server’s RTCLOCAL rtcdyn database.  After you run the queries and have exported all of the results you would want to clean up the duplicate entries so that you weren’t reporting inflated numbers.

Even though there’s a little work involved in gathering the information this is a fairly easy way to gauge adoption and see which of your users are actually using the OCS/Lync.

Using the Lync Server 2013 Monitoring Server

If you have the Monitoring Server role configured in your environment, and for Lync Server 2013 everyone should!, you can use information contained in the LcsCDR database to pull back the last time a user signed in.  You can run the following query* to pull back the user’s SIP URI and their last login time:

USE LcsCDR
SELECT dbo.Users.UserUri, dbo.UserStatistics.LastLogInTime
FROM dbo.UserStatistics
JOIN dbo.Users ON dbo.Users.UserId = dbo.UserStatistics.UserId
ORDER BY UserUri

Which produces the following output:

The advantage to using the Monitoring Server to obtain this data is that unlike the information contained in the rtcdyn database, the information from the LcsCDR data will persist even when the user isn’t signed into Lync.

*These queries are provided for you to use at your own risk.  Please make sure you test before running in a production environment.

use rtc

declare @sipQuery nvarchar(250)

set @sipQuery = 'test1@contoso.com'

select FE.Fqdn,R.UserAtHost, * from FrontEnd FE

join RoutingGroupAssignment RGA

On FE.FrontEndId = RGA.FrontEndId

join ResourceDirectory RD

on RGA.RoutingGroupId = RD.RoutingGroupId

join Resource R

on RD.ResourceId = R.ResourceId

where R.UserAtHost = @sipQuery

참고

https://msdn.microsoft.com/en-us/library/bb878933.aspx

http://mikestacy.typepad.com/mike-stacys-blog/2009/08/are-you-really-away.htmlㄴ

use rtc

declare @sipQuery nvarchar(250)
set @sipQuery = 'sipusername%'

select Publisher, MIN(Status) as Status
from
(select Publisher, Status=
CASE
when Availability BETWEEN 0 AND 2999 then 'Not defined:'+Availability
when Availability BETWEEN 3000 AND 4499 then 'Available'
when Availability BETWEEN 4500 and 5999 then 'Available - Idle'
when Availability BETWEEN 6000 and 7499 then 'Busy'
when Availability BETWEEN 7500 and 8999 then 'Busy - Idle'
when Availability BETWEEN 9000 and 11999 then 'Do not Disturb'
when Availability BETWEEN 12000 and 14999 then 'Be right back'
when Availability BETWEEN 15000 and 17999 then 'Away'
when Availability > 18000 then 'Offline'
end
from
(
select Publisher,
substring(
substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50),0,
patIndex('%<availability>%',substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50))
) Availability
from
(select UserAtHost Publisher,ContainerNum,CONVERT(varchar(4000),convert(varbinary(4000),Data)) PublicationDocument
from rtcdyn.dbo.PublishedInstance tblPublishedInstance,
rtc.dbo.Resource tblResource
where tblPublishedInstance.PublisherId = tblResource.ResourceId) as PublishedDocuments
where LEN(replace(PublicationDocument,'aggregateState','')) < LEN(PublicationDocument)
and ContainerNum = 2
) as PublisherAndAvailability
where Publisher like @sipQuery
union
select UserAtHost Publisher, 'Offline-Not Registered Here' Status
from rtc.dbo.Resource
where UserAtHost like @sipQuery) as PublisherAndStatus
group by Publisher

참고

http://mikestacy.typepad.com/mike-stacys-blog/sql/

Container 테이블 설명

https://msdn.microsoft.com/en-us/library/bb879521.aspx

https://msdn.microsoft.com/en-us/library/office/dn454664.aspx

Access Control List Containers