This questions may already be answered but I couldn't find it.

Is there away either via the reporting tool, powershell, or sql script to perform the following?

Create a report that shows the number of messages sent by username over a period of time.  That's all the information that's been asked.  I've tried running an export from the user activity report to an excel and cvs file and then a powershell script, but it never shows the number of messages or the user name.

For example  This is what I'm looking for.

Me 15 messages sent June 2014
You 33 messages sent January 2015

Thanks.

Yes it's possible, but you'll need to have archiving turned on and you'll need to write a custom SQL query to do so, you won't find anything with the monitoring data that references this.  Also, keep in mind that the messages will be individual messages, not IM sessions.

Reporting db will only give you the number of IM sessions, not the number of messages sent per session. As Anthony said Archiving would be the way to go here.. You'd enable archiving on the user, then your report will consist of :

1. Exporting the archive to a location (using Export-CsArchivingData -Identity "ArchivingDatabase:ArchivingSQLServer.domain" -StartDate 1/1/2015 -OutputFolder "C:\ArchivingExp" -UserUri "sip:user1@domain.com"

This will net you the conversation. Review the files to see what you should include in step 2

2. You'd need to create logic to parse the exported file and create a report which increments an 'IMs sent' number for every line where user1 has said something. Review the formatting of the exported archive folder to figure out the formatting/logic on how to do this, but it will boil down to detecting user1's name/username in the conversations and incrementing +1 for each line until another user's name is detected (or another timestamp).

Cheers,

Max

• Edited by Maxim M 23 hours 39 minutes ago

Reporting db will only give you the number of IM sessions, not the number of messages sent per session. As Anthony said Archiving would be the way to go here.. You'd enable archiving on the user, then your report will consist of :

1. Exporting the archive to a location (using Export-CsArchivingData -Identity "ArchivingDatabase:ArchivingSQLServer.domain" -StartDate 1/1/2015 -OutputFolder "C:\ArchivingExp" -UserUri "sip:user1@domain.com"

This will net you the conversation. Review the files to see what you should include in step 2

2. You'd need to create logic to parse the exported file and create a report which increments an 'IMs sent' number for every line where user1 has said something. Review the formatting of the exported archive folder to figure out the formatting/logic on how to do this, but it will boil down to detecting user1's name/username in the conversations and incrementing +1 for each line until another user's name is detected (or another timestamp).

Cheers,

Max

• Edited by Maxim M Wednesday, March 04, 2015 3:53 PM

Reporting db will only give you the number of IM sessions, not the number of messages sent per session. As Anthony said Archiving would be the way to go here.. You'd enable archiving on the user, then your report will consist of :

1. Exporting the archive to a location (using Export-CsArchivingData -Identity "ArchivingDatabase:ArchivingSQLServer.domain" -StartDate 1/1/2015 -OutputFolder "C:\ArchivingExp" -UserUri "sip:user1@domain.com"

This will net you the conversation. Review the files to see what you should include in step 2

2. You'd need to create logic to parse the exported file and create a report which increments an 'IMs sent' number for every line where user1 has said something. Review the formatting of the exported archive folder to figure out the formatting/logic on how to do this, but it will boil down to detecting user1's name/username in the conversations and incrementing +1 for each line until another user's name is detected (or another timestamp).

Cheers,

Max

• Edited by Maxim M Wednesday, March 04, 2015 3:53 PM

Reporting db will only give you the number of IM sessions, not the number of messages sent per session. As Anthony said Archiving would be the way to go here.. You'd enable archiving on the user, then your report will consist of :

1. Exporting the archive to a location (using Export-CsArchivingData -Identity "ArchivingDatabase:ArchivingSQLServer.domain" -StartDate 1/1/2015 -OutputFolder "C:\ArchivingExp" -UserUri "sip:user1@domain.com"

This will net you the conversation. Review the files to see what you should include in step 2

2. You'd need to create logic to parse the exported file and create a report which increments an 'IMs sent' number for every line where user1 has said something. Review the formatting of the exported archive folder to figure out the formatting/logic on how to do this, but it will boil down to detecting user1's name/username in the conversations and incrementing +1 for each line until another user's name is detected (or another timestamp).

Cheers,

Max

• Edited by Maxim M Wednesday, March 04, 2015 3:53 PM

Reporting db will only give you the number of IM sessions, not the number of messages sent per session. As Anthony said Archiving would be the way to go here.. You'd enable archiving on the user, then your report will consist of :

1. Exporting the archive to a location (using Export-CsArchivingData -Identity "ArchivingDatabase:ArchivingSQLServer.domain" -StartDate 1/1/2015 -OutputFolder "C:\ArchivingExp" -UserUri "sip:user1@domain.com"

This will net you the conversation. Review the files to see what you should include in step 2

2. You'd need to create logic to parse the exported file and create a report which increments an 'IMs sent' number for every line where user1 has said something. Review the formatting of the exported archive folder to figure out the formatting/logic on how to do this, but it will boil down to detecting user1's name/username in the conversations and incrementing +1 for each line until another user's name is detected (or another timestamp).

Cheers,

Max

• Edited by Maxim M Wednesday, March 04, 2015 3:53 PM

If you can get away with the number of sessions without messages per session then this should be achievable from the CDR database.

I had a quick play and came up with this SQL query:

---------------------------------------------------------

use LcsCDR

SELECT sd.SessionIdTime,

fu.UserUri as 'FROM User',

tu.UserUri as 'TO User'

FROM
--LYNC2013/2010
VoipDetails AS vd LEFT OUTER JOIN
    SessionDetails AS sd ON sd.SessionIdTime = vd.SessionIdTime  AND vd.SessionIdSeq = sd.SessionIdSeq LEFT OUTER JOIN
    Phones AS fp ON vd.FromNumberId = fp.PhoneId LEFT OUTER JOIN
    Phones AS cp ON vd.ConnectedNumberId = cp.PhoneId LEFT OUTER JOIN
Users AS fu ON sd.User1Id = fu.UserId LEFT OUTER JOIN
Users AS tu ON sd.User2Id = tu.UserId LEFT OUTER JOIN
Gateways AS fgw ON vd.FromGatewayId = fgw.GatewayId LEFT OUTER JOIN
Gateways AS tgw ON vd.ToGatewayId = tgw.GatewayId LEFT OUTER JOIN
MediationServers AS fms ON vd.FromMediationServerId = fms.MediationServerId LEFT OUTER JOIN
MediationServers AS tms ON vd.ToMediationServerId = tms.MediationServerId LEFT OUTER JOIN
Phones AS dp ON vd.DisconnectedByPhoneId = dp.PhoneId LEFT OUTER JOIN
Users AS du ON vd.DisconnectedByURIId = du.UserId LEFT OUTER JOIN
Users AS ru ON sd.ReferredById = ru.UserId LEFT OUTER JOIN
dbo.Media AS m ON m.SessionIdTime = sd.SessionIdTime LEFT OUTER JOIN
dbo.ClientVersions AS fcv ON sd.User1ClientVerId = fcv.VersionId LEFT OUTER JOIN
dbo.ClientVersions AS tcv ON sd.User2ClientVerId = tcv.VersionId

WHERE m.MediaId = '1'

---------------------------------------------------------

It appears that MediaId "1" is IM. Ive run out of time today so haven't had the time to validate if this provides the correct output. I can see a few PSTN numbers in the results which makes me a bit skeptical. 

Modified from - http://www.lync.geek.nz/p/call-accounting.html