Lync and Skype for Business offer Call Detail Recording, essentially just a record of all calls to, from and within your Lync/Skype infrastructure. Only problem is it’s a pain to query. Now there are some SSRS custom reports available as well as the default templates but sometimes you just want to get in there and pull data quickly from SQL.

This, for example, will get all audio calls from the last 10 days:

SELECT [SessionIdTime],[ResponseTime],[SessionEndTime],DATEDIFF(ss,[ResponseTime],[SessionEndTime]) AS Duration,u1.[UserUri] AS User1Uri,[User1Id],u2.[UserUri] AS User2Uri,[User2Id],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById] FROM [LcsCDR].[dbo].[SessionDetails] s LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u1 ON s.[User1Id] = u1.[UserId] LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u2 ON s.[User2Id] = u2.[UserId] WHERE [User1Id] != [User2Id] AND [MediaTypes] = 16 AND [ResponseTime] >= dateadd(dd,0, datediff(dd,10, getDate())) ORDER BY [ResponseTime] DESC

Change the number value here: datediff(dd,10, getDate())) to display a different number of days worth of calls. Change the value of [MediaTypes] to alter the call type you’re searching for as per the database schema.

These examples will get you the user ID for a given SIP URI or external number:

SELECT [UserId] FROM [LcsCDR].[dbo].[Users] WHERE [UserUri] LIKE '+441212001234%'
SELECT [UserId] FROM [LcsCDR].[dbo].[Users] WHERE [UserUri] LIKE 'jimbob@example.com%'

With those UserIDs you can filter calls to a user (in this case user ID 147):

SELECT [SessionIdTime],[ResponseTime],[SessionEndTime],DATEDIFF(ss,[ResponseTime],[SessionEndTime]) AS Duration,u1.[UserUri] AS User1Uri,[User1Id],u2.[UserUri] AS User2Uri,[User2Id],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById] FROM [LcsCDR].[dbo].[SessionDetails] s LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u1 ON s.[User1Id] = u1.[UserId] LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u2 ON s.[User2Id] = u2.[UserId] WHERE [User1Id] != [User2Id] AND [MediaTypes] = 16 AND [User2Id] = '147' ORDER BY [ResponseTime] DESC

Or from a user:

SELECT [SessionIdTime],[ResponseTime],[SessionEndTime],DATEDIFF(ss,[ResponseTime],[SessionEndTime]) AS Duration,u1.[UserUri] AS User1Uri,[User1Id],u2.[UserUri] AS User2Uri,[User2Id],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById] FROM [LcsCDR].[dbo].[SessionDetails] s LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u1 ON s.[User1Id] = u1.[UserId] LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u2 ON s.[User2Id] = u2.[UserId] WHERE [User1Id] != [User2Id] AND [MediaTypes] = 16 AND [User1Id] = '147' ORDER BY [ResponseTime] DESC

Or both:

SELECT [SessionIdTime],[ResponseTime],[SessionEndTime],DATEDIFF(ss,[ResponseTime],[SessionEndTime]) AS Duration,u1.[UserUri] AS User1Uri,[User1Id],u2.[UserUri] AS User2Uri,[User2Id],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById] FROM [LcsCDR].[dbo].[SessionDetails] s LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u1 ON s.[User1Id] = u1.[UserId] LEFT OUTER JOIN [LcsCDR].[dbo].[Users] u2 ON s.[User2Id] = u2.[UserId] WHERE [User1Id] != [User2Id] AND [MediaTypes] = 16 AND ([User1Id] = '147' OR [User2Id] = '147') ORDER BY [ResponseTime] DESC

You can of course filter on [ResponseTime] or even [InviteTime] to limit your query by timeframe similarly to the first example. If you have a nose through the SessionDetails table in the database you’ll be able to see all the additional columns that you can also query but IMO the ones I’ve included tend to be the important ones – who the call is two/from, when it was made, how long it was and whether it was made on behalf of or referred by another user. Some columns you may find useful are [IsUser1Internal] and [IsUser2Internal] which will tell you if one or both parties were connecting via one of your Edge servers rather than internally and [ResponseCode] which will tell you whether or not the call was successfull (a “200” code means success).



So, if you have a SharePoint 2007 Shared Services Provider and are syncing user profiles with your AD then you might do something stupid like accidentally deleting the TimerJobs that do the profile sync and clean up and what have you.

So now, you’re getting An error has occurred while accessing the SQL Server database or the Office SharePoint Server Search service. errors in your SSP admin and your profiles aren’t syncing and it’s all something of a mess.

Now, it’s already documented in a couple of places how to fix the Full & Incremental profile import jobs, notably here: http://www.geekinthepink.info/2009/12/sharepoint-user-profiles-and-properties.html but what if you’ve been really dumb and deleted the other jobs too? Well I’ve included the required SQL below to recreate all 6 of the core jobs, but it does require a little work on your part; you need to look up the GUIDs from the MIPObjects table in the main SSP Database (e.g. SharedServices_DB), which is the same database that you need to run this script against. In it, you will hopefully find a record which has an XML data set that probably starts with something like:

<object><field name="canonicalMySitePortalUrl" type="string">...

If you copy the XML data set out, you will find it contains a number of “field” XML nodes that include GUIDs. Find the nodes that correspond to the name of the jobs i.e:

<field name="profileChangeJobId" type="guid">f4e31424-0361-45f2-98d6-0150b74e6345</field>
<field name="profileChangeCleanupJobId" type="guid">becd5baf-ca7e-4c1b-9c1a-0db941337b55</field>
<field name="profileFullImportJobId" type="guid">5c6d0eb4-1880-44e6-9b0a-6704f5393fde</field>
<field name="profileIncrementalImportJobId" type="guid">3128d33d-23e5-4d85-af00-26f90f760d0a</field>
<field name="DLImportJobId" type="guid">77b77902-4fb5-48b4-a709-6f0f8d167a1b</field>
<field name="audienceCompilationJobId" type="guid">7a1dcf14-fe06-40ab-82c7-d8f7e72f5fdf</field>

And insert the GUID values into the SQL below for each appropriate job, replacing the xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx placeholders. Do not just use the ones in this example, they won’t work as they’re unique to each SharePoint instance

You can also obviously change the schedules and the NextDueTime values to suit your own environment.

And next time, take backups 😉

INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.UserProfiles.UserProfileChangeJob',
'hourly between 0 and 0',
'',
'2012-10-31 15:00:00.000',
0,
'User Profile Change Job'
)
 
INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.UserProfiles.UserProfileChangeCleanupJob',
'daily between 21:00:00 and 21:00:00',
'',
'2012-10-31 15:00:00.000',
0,
'User Profile Change Cleanup Job'
)
 
INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.UserProfiles.DLImportJob',
'every 5 minutes between 0 and 0',
'',
'2012-10-31 15:00:00.000',
0,
'Distribution List Import Job'
)
 
INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.Audience.AudienceCompilationJob',
'daily between 01:00:00 and 01:00:00',
'NULL',
'2012-10-31 15:00:00.000',
0,
'Audience Compilation Job'
)
 
INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.UserProfiles.UserProfileImportJob',
'weekly between fri 15:00:00 and fri 15:00:00',
'IsIncremental#False',
'2012-10-31 15:00:00.000',
1,
'User Profile Full Import Job'
)
 
INSERT INTO MIPScheduledJob (JobId, Assembly, Class, Recurrence, JobData, NextDueTime, Disabled, DisplayName)
VALUES (
'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx',
'Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c',
'Microsoft.Office.Server.UserProfiles.UserProfileImportJob',
'daily between 12:00:00 and 12:00:00',
'IsIncremental#True',
'2012-10-31 15:00:00.000',
1,
'User Profile Incremental Import Job'
)


This is an old one, but I’ve only just managed to find a solution after searching on and off for several months; we’ve had a couple of machines that developed the problem but it was never really considered serious enough to put a lot of effort into solving.

Scenario: You have a machine with some element of SQL 2005 installed; be it simply the management tools or the full-on server with all the trimmings. This machine claims, via WSUS or Windows Update, that it needs Visual Studio 2005 SP1, but the install keeps failing. If you run the update manually, it keeps asking you for “Microsoft Visual Studio 2005 Premier Partner Edition ENU – Disk 1”. Browsing provides you with a filename “vs_setup.msi” but the only one you can find on your machine is part of the .NET Framework 3.5 and isn’t the one it’s looking for.

Solution: Point the installer to the “vs_setup.msi” file located in the “Tools” folder of your SQL 2005 install media.