Home » Lync » #Lync Server 2010, Group Chat #SQL DB Query for Messages Older than…

Ah, Lync Server 2010, Group Chat role… you crusty piece of software. I had a customer make a request to see if it was possible to purge data from their Group Chat Database after ‘x’ time. ‘x’ being a number of days. I’m not going to discuss the motivations for leveraging a persistent system only to purge it, but needless to say I had to determine what was feasible. Looking into the tables of the Group Chat DB, I started to gather how to accomplish this but not being a SQL guy, I was confused about the chatDate value in the message table(See Figure1).

Figure 1.

groupchatdate

I started researching this morning and wasn’t getting much other than “Hey we have these cool cmdlets in Lync Server 2013, Persistent Chat for this!

I posted out to Twitter and didn’t get any instant responses other than some new SQL related automated followers.

I went back to the drawing board and changed up my original Google/Bing queries and I started to make progress. Sometimes you need to take a break from thinking about it for a moment to make forward progress… anyways… that lead me to this TechNet Article.

In the first example got the ball rolling for me as it was the key I needed.

I jumped over to SQL Management Studio and built a super basic query to find what I wanted.

The query itself is rather basic, we’re looking at the table within the Group Chat SQL Database called tblChat. We convert the data in the chatDate column and then evaluate it against a Function. The DATEADD Function specifically.  My query above evaluates the value in chatDate to see if it’s older than 2 days ago.

The rest of the solution involves deleting the results and restart the Group Chat Services.  All of which I’m executing via PowerShell. Hopefully that helps someone.

At a minimum I have it to reference :).

 

  • Justin T.

    Nice catch on that man, one of those things that will come in handy in some strange way in the future methinks 🙂