Powershell script interface to Skype for Business and Lync CDR and QoE data

Hi EveryoneI have been working a bit lately on Enterprise Voice and Call Quality and so wanted to do a bit of digging into the data collected by Lync and Skype for Business in the LCSCDR and QoEMetrics databases. I love the Lync Monitoring reports that you get with the product,

LyncCalls.ps1
 
 
 
 
 
4.7 Star
(6)
2,538 times
Add to favorites
Lync
5/27/2016
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • CSV export
    1 Posts | Last post August 16, 2018
    • trying to export it to CSV but the display headers get included in the pipeline because theyre just dumped to the screen.
      
      as there are now two objects in the pipeline and the export csv only sees the properties of the first object, which is a string, all you get is a csv file with the length of the headers and no actual data
      
      any chance you could update all the "header" text throughout the script to use your OutLn function instead, as that doesnt end up in the pipeline and you just get the data when you want to export it
  • Timeout to SQL when running your script
    8 Posts | Last post June 22, 2016
    • Steve….
      
      Attempting to run the following query from a SfB front end server. Im logged into the server with my admin account. (This account has sysadmin rights to SQL). Any ideas?
      
      .\lynccalls.ps1 -action detailed -INTERVAL 1d -DATELIMITED $True
      
      note Ive hardcoded front-end poolname, SQLSERVER and SQLINSTANCE
      Im assuming the environment is equivalent to Lync/SfB site name
      
      Im getting an error:
      
      Exception calling “ExecuteReader” with “0” argument(s): “Timeout expired. The
      timeout period elapsed prior to completion of the operation or the server is
      not responding.”
      At F:\Program Files\Skype for Business Server 2015\LyncCalls.ps1:424 char:5
      + $objResult = $objCommand.ExecuteReader()
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException
      
      15:36:16: WARN: No active records were found!
    • Hi david 
      
      Is it a large environment? Do you have a lot of cqm/qoe data in your monitoring instance? I usually only see sql timeouts using a large time interval. I'll do some digging into adjusting timeout interval.
      
      What spec sql backend do you have? Ram, cpu, disk.
      
      Steve
    • SfB server on prem w/6 FE servers in pool to a backend SQL 2012 SP2 mirror
      the SQL server itself is a virtual windows 2012 server
      32GB RAM
      4 vCPU
      the SQL DB and log drives were provisioned with 200gb drives 
      17.5gb is used on DB drive
      94gb used on log drive
    • Hi David
      
      could you please have a try for me, adding the following line (CommandTimeout=0).
          $objCommand = $objConn.CreateCommand()
          $objCommand.CommandText = $strSQL
          <b>$objCommand.CommandTimeout = 0</b>
      
      You can find the line in the function called Get-SQLResultsTable.
      
      This should turn off the SQL time out on the query. I have tested on a smaller set of data, but not against a large data set (which I expect you have with a 6 FE front end pool.
      
      Let me know how you go.
      
      Steve
    • Hi David
      
      ignore the <b>, that was a comment fail by me, was hoping to bold the line :)
      
      Steve
    • Steve...
      
      Good news is adding $objCommand.CommandTimeout = 0 helped in that I no longer see the 'Timeout Expired' error immediately after executing the LyncCalls.ps1 cmdlet.   Bad news is that the cmdlet has been running for 15 minutes with no output (but also no errors).   Do you have a verbose mode switch or anything else I could check?
      
      
      This is what my active window currently shows
      
      ---------------------------------------------------------
      -                       EV Calls                        -
      -       Used to automate checking Lync EV statistics    -
      -                        '1.09'                         -
      ---------------------------------------------------------
      14:16:39: Processing calls for <FE pool name> on sqlservername\sqlinstance
      14:16:39: INFO: Interval is 1 - 1d
      14:16:39: INFO: About to process SQL Query
    • Ignore my last response...if I wouldve waited 5 more minutes I then saw an update INFO: Records were returned.    I will ask...what do you set your powershell window buffers to be?  
      
      Either way...it looks like your script is working...thanks.
    • Hi David,
      
      I'll look at what I can do to optimise that query. I take it you have a high call volume site you are managing. 
      
      I mostly work in smaller sites where there aren't a lot of calls in a 24 hour period, and most queries return quickly. I'll hit my SQL guru colleagues to see if i can improve it for you.