More Lync PowerShell Scripts

In this Blog we will see more Power Shell Scripts.

This Script will give you an output who all the users are enabled for SIP Address

$FilePath = "C:\temp\users.csv"
$Stuff = Import-CSV $FilePath

ForEach ($_.SAMid in $Stuff)
{
$SipAddress = "sip:"+$_.SipAddress

$User= Get-CsUser –Identity $SipAddress

If($User.Enabled -eq $null)
{
Write $sipaddress "not found"
}
elseif($User.Enabled -eq $false)
{
Write "Do Enable"
}
else
{
Write $sipaddress "User Is Enabled!"
}
}

Here is the OutPut

sip:raju.raju@Contoso.com
User Is Enabled!

Lync Call Report using SQL Query

In this blog we will see how to pull out the Lync (Enterprise Voice) outgoing call Report using SQL Query

To get the Outgoing call report

Use LcsCDR
SELECT  VoipDetails.SessionIdTime [Date],
        CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
        Phones.PhoneUri [From Number],
        Users1.UserUri [From Sip],
        Phones1.PhoneUri [Number Dialed]


FROM         VoipDetails LEFT OUTER JOIN SessionDetails
             ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq
             LEFT OUTER JOIN  Phones
             ON VoipDetails.FromNumberId = Phones.PhoneId
             LEFT OUTER JOIN  Phones Phones1
             ON VoipDetails.ConnectedNumberId = Phones1.PhoneId
             LEFT OUTER JOIN  Users Users1
             ON SessionDetails.User1Id = Users1.UserId

where SessionDetails.ResponseCode = 200
      and VoipDetails.SessionIdTime between '2013-10-01 00:00:00' and '2013-10-01 23:59:59'     
order by VoipDetails.SessionIdTime

Here is the Output of above query

Date	Time	From Number	From SIP                                Number Dialed
10/1/2014 6:50	0:04:46	1234567890	user01@contoso.com             1234567893
10/1/2014 6:52	0:01:54	1234567891	user02@contoso.com             1234567895
10/1/2014 6:54	0:02:32	1234567892	user03@contoso.com             1234567896
10/1/2014 6:57	0:00:37	1234567893	user04@contoso.com             1234567897

To get the Outgoing call report for the Particular Users


Use LcsCDR
SELECT VoipDetails.SessionIdTime [Date],
       CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
       Phones.PhoneUri [From Number],
       Users1.UserUri [From Sip],
       Phones1.PhoneUri [Number Dialed]

FROM         VoipDetails LEFT OUTER JOIN SessionDetails
                        ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq
                        LEFT OUTER JOIN  Phones
                        ON VoipDetails.FromNumberId = Phones.PhoneId
                        LEFT OUTER JOIN  Phones Phones1
                        ON VoipDetails.ConnectedNumberId = Phones1.PhoneId
                        LEFT OUTER JOIN  Users Users1
                        ON SessionDetails.User1Id = Users1.UserId

where SessionDetails.ResponseCode = 200
         and VoipDetails.SessionIdTime between '2014-10-01 00:00:00' and '2013-10-01 23:59:59'        
         and Phones1.PhoneUri is not null
     and Phones.PhoneUri  is not null
     AND Users1.UserUri IN ('User01@contoso.com','User02@contoso.com')
order by VoipDetails.SessionIdTime


Any Comments are Welcome :)

Lync User Last Login Time using SQL

Lync User Last Login Time using SQL

In this Blog we will see Lync users last login time using Sql Query

SELECT  u.UserUri
      ,us.[LastLogInTime]

  FROM [LcsCDR].[dbo].[UserStatistics] us left join
  [LcsCDR].[dbo].Users u on us.UserId = u.UserId
  order by us.LastLogInTime desc