Sunday, March 22, 2015

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!

Monday, March 9, 2015

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 :)

Sunday, March 8, 2015

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