Faxback-Port-Utilization

This Powershell script will execute an SQL query to get the port utilization of faxback server. It is going to log the failed report for logDNA. LogDNA will get the updated logs

Faxback-Port-Utilization files

C:\oit\port-utilization-script
+-- HourlySQLReportQuery # SQL Script that will pull the hourly port utilization
+-- port-utilization.ps1 # the main execution powershell script
+-- TaskScheduler-Port-Utilization-Script.xml #Task Scheduler XML file.
+-- ransqlquery.sql # This is a temporary file and dynamically written
+-- DailySQLReportQuery.sql # Original SQL file, As submitted by Faxback

Logs

Logs are saved in c:\oit\logdna\logs folder

This log file is the failed log file generated by the main script which only updated when we exeeded MaxPort value
FAX01-ATL-Port-Exceeded.log 

This log file is the error log file which only logs when file is missing
FAX01-ATL-PortUtilization-Error.log

How to configure the script

Supported/Tested: Windows 10

Create c:\oit\port-utilization-script and c:\oit\logdan\logs folder

ie: command
mkdir c:\oit\port-utilization-script
mkdir c:\oit\logdna\logs

Download and place all of the files in c:\oit\port-utilization-script folder Open Task Scheduler -> Import Task, Select the TaskScheduler-Port-Utilization-Script.xml file Select the TaskScheduler-Port-Utilization-Script in Task Scheduler, and click properties. Select Triggers Tab, Edit the task, click Enabled checkbox and Click Ok to save

Post Requirements

-> Install Chocolatey -> Install LogDNA

SQL Input File

+-- HourlySQLReportQuery.sql

We commented out this line

--SET @ReportDay = '2021-10-18 14:00:00' --Both '9/21/2021' and '2021-09-21' date formats can be used here.
--SET @StartDate = @ReportDay
--SET @EndDate =   DATEADD(day,1,@ReportDay)

StartDate and EndDate are replaced with report-time-start and report-time-end as a placeholder for the script

SET @StartDate = 'report-time-start'
SET @EndDate =   'report-time-end'

Commented out the following to make sure that time is not strip from the datetime with -- characters in front of the declaration

-- SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate)
-- SET @EndDate = DATEADD(minute,-DATEPART(minute,@EndDate),@EndDate);

commented out these lines so that we don't query for the full day from the DB

/*
WHILE @Day < @LastDay
BEGIN

  WITH DateIntervalsCTE AS
  (
	  SELECT 1 i, DATEADD(dd,@Day,@startdate) AS Date
	  UNION ALL
	  SELECT i + 1, DATEADD(minute,i,DATEADD(dd,@Day,@startdate))
	  FROM DateIntervalsCTE
	  WHERE DATEADD(minute, i, DATEADD(dd,@Day,@startdate) ) < DATEADD(dd,@Day+1,@startdate)
  )
  Insert Into @Timeline Select Date From DateIntervalsCTE
  OPTION (MAXRECURSION 32767);

  SET @Day = @Day + 1;
END;
*/

We added the follow just below the commented section as per above.

WITH DateIntervalsCTE AS
(
  SELECT 1 i, DATEADD(dd,@Day,@startdate) AS Date
  UNION ALL
  SELECT i + 1, DATEADD(minute,i,DATEADD(dd,@Day,@startdate))
  FROM DateIntervalsCTE
  WHERE DATEADD(minute, i, DATEADD(dd,@Day,@startdate) ) < DATEADD(dd,@Day+1,@startdate)
)
Insert Into @Timeline Select Date From DateIntervalsCTE
OPTION (MAXRECURSION 32767);

Main Script File

+-- port-utilization.ps1 This script is self explanatory as the script has comments for almost every line.