Cisco Unified Computing System (UCS) Cloud Monitoring Solution

Cisco UCS Monitoring Solution (PowerShell/WinForms/REST API)
UCS_Cloud
DESCRIPTION:

Monitors UCS infrastructure by polling multiple UCS Domains and creating JSON report
of the domain inventory, configuration, and overall performance.

JSON example:

JSON_Example

Dashboard view:

Sysyem_Status

Monitors Faults and potential future problems, provides deeper insight on root cause contributing to faults.

JSON example:

Faults_Example

Front-end view:

Statistics_and_Faults

UCS Inventory Tool

The data collector configuration tool deployed on Windows server host:

Tool_UCS_Inventory

Windows Server 2012R2 (Amazon EC2) Monitoring Solution + Salesforce REST API Integration

Performance Monitoring Solution Sending Alerts to CRM System via REST API (C++/PowerShell/WinForms/REST)

DESCRIPTION:
PowerShell scripts permanently registered as Windows services to monitor various activities and send alerts to Salesforce (Cloud CRM system).

DOCUMENTATION:

1. XML Settings File Creator Tool

XML_Settings_Creator1

Description: The PowerShell/WinForms tool is designed to create an XML file based on a user input.
The produced XML file contains settings which will be used by the monitoring services and the SalesForce REST API posting script.
The “Password”, “Security Token”, and “Consumer Secret” will be stored encrypted into the XML file.

Example:

<Password>76492d1116743f0423413b16050a5345MgB8AHoASAB2AHAAZgBRAFEAegBUAHkAUQBBADEAdgAzAEkAUgByAGkAdwB5AHcAPQA9AHwAMAAxAGQAZgA5AGUAZgBmAGQAYgBhADAANgAzADEAMQBlAGUAZgA2ADMAZQBjADUAOQA4AGEANABmADUANAA5AGMAYgA0AGEAYwBlADkAYQAyAGQAMwBiADcANABjADkAYgAwAGMAMwBiAGEAYwA3ADgAYwAzADEANABmAGEAYQA=</Password>

<SecurityToken>76492d1116743f0423413b16050a5345MgB8AEQAVwBiAEsAVwAzAEQAQQBVAHUARwBPADcAdQBEAFoAWgBHAFgAbABnAEEAPQA9AHwAZAAyADIAYgA3ADAAYgAxADAANAAwADUAYwA3ADgAYQA0ADMAZQBmADUAMgBkAGIANQA1ADYAYgBkAGEAMgA1ADUAMgA0ADkANwAzAGQAZgA5ADQAMAAwAGUAYwA0ADMANgA5AGEAZQBiAGEANQAxADIAMQA2AGQAYwA4AGQAMgA5ADgAZABiAGYAZAAyADgANQAxADUAOABkADUAYwA4AGMANQA5AGIANAA1AGUAZgA2ADgAOABkADYANwA4ADYAMQBkADgANABiAGYANgAwADIAZQAyADUAOABiADcANABhADUAZQA1ADQAMwA5AGYAMQBlAGMAOQA5ADgANgA0AA==</SecurityToken>

<ConsumerSecret>76492d1116743f0423413b16050a5345MgB8AHgANABLAFIAeAA4AHoAcAAxAE0AWABQAEIASABKAHgAQgBvAEoAQQBUAGcAPQA9AHwANAA2ADgAMABlAGUAOAA3ADUANgAwADkAMwBkAGIAZABmADQAYQAwADAAMAAyAGIAOQBlADkAYgBiADkAZQA3ADQAMQA0ADMANABkAGMAYwBlAGQANwA2AGIAMABhADAAMgA2AGEAYQA2ADMAZABlAGYAZgA1ADAAZgA4AGMAOABjADcAYQA3AGMANAA5AGMAZABiADYAYgAyAGMANAA1AGQAMgAwADMAMAAzADAAMwAzADMANgAyADMANwBhADYA</ConsumerSecret>

The encryption algorithm is hard-coded into the tool executable for maximum security.

2.    Performance Monitoring Services

PowerShell/C++ solution for real-time server performance monitoring and alerting.
Each monitoring tool consists of a PowerShell script which runs continuously as a Windows service. Each of these services create event logs based on parameters set in the Settings file.

2.1.    Services general notes

Services ensure the PowerShell monitoring scripts are constantly running even when the server is rebooted.
Available commands:
install  to install the service to Windows Service Controller
uninstall to uninstall the service. The opposite operation of above.
start to start the service. The service must have already been installed.
stop to stop the service.
restart to restart the service. If the service is not currently running, this command acts like start.
status to check the current status of the service. This command prints one line to the console. NonExistent to indicate the service is not currently installed, Started to indicate the service is currently running, and Stopped to indicate that the service is installed but not currently running.

Logging:
After successful start, each process will generate the following 3 log files:

Services_Logs

.wrapper.log – logs events related to the service (start, stop, restart, etc.)
.out.log – stores the PowerShell script output if such
.err.log – logs error events related with the PowerShell monitoring script

2.2.    MSMQMon

The MSMQMon is monitoring all local Microsoft Message Queuing queues until one of them reaches the threshold specified in the XML settings file. Then it will create an event log stating which exact queue (queue name) has reached the quota.

Event log Example (body)

<event>
<ServerName>WIN-1EQP3L29OVO</ServerName>
<Message>Message queue > 4</Message>
<MessageLong>QUEUE_LENGTH_THRESHOLD = 4 QUEUE_NAME = BLMM</MessageLong>
<Severity>WARNING</Severity>
<Type>WEBSERVICES</Type>
<StartTime>04-18-2016 52:20:28</StartTime>
</event>

Files:
MSMQMon.exe  – Service executable.
MSMQMon.exe.config – Configuration file that defines .NET 4.0 runtime support (Windows Server 2012) and offline service support.
MSMQMon.xml  – Configuration file that defines the service.
MSMQMon.ps1 – PowerShell monitoring script.

Schedule:
Performs the MSMQ message number check each 60 seconds
To alter this setting, edit MSMQMon.ps1 file, line 61:
Start-Sleep -Seconds 300

2.3.    CPUMon

The CPUMon is monitoring the CPU utilization. If the threshold specified in the XML settings file is met it will create an event log.

Event log Example (body)

<event>
<ServerName>WIN-1EQP3L29OVO</ServerName>
<Message>CPU Usage > 80%</Message>
<MessageLong>CUP_USAGE_THRESHOLD = 80</MessageLong>
<Severity>WARNING</Severity>
<Type>WEBSERVICES</Type>
<StartTime>04-18-2016 52:20:28</StartTime>
</event>

Files:
CPUMon.exe  – Service executable.
CPUMon.exe.config – Configuration file that defines .NET 4.0 runtime support (Windows Server 2012) and offline service support.
CPUMon.xml  – Configuration file that defines the service.
CPUMon.ps1 – PowerShell monitoring script.

Schedule:
If the CPU load reaches and exceed the threshold for 5 seconds, start monitoring for 30 min. period.
If the average CPU usage after 30 min. test is greater than the threshold write event log.
To alter this schedule, edit lines 50 and 53:
$TotalCpuUsage = (Get-Counter -Counter “\Processor(_Total)\% Processor Time” -SampleInterval 1 -MaxSamples 5 -ErrorAction Stop | select -ExpandProperty countersamples | select -ExpandProperty cookedvalue | Measure-Object -Average).average

$TotalCpuUsage = (Get-Counter -Counter “\Processor(_Total)\% Processor Time” -SampleInterval 30 -MaxSamples 60 -ErrorAction Stop | select -ExpandProperty countersamples | select -ExpandProperty cookedvalue | Measure-Object -Average).average

2.4.    WinwordCountMon

The WinwordCountMon is monitoring the number of running WINWORD.exe processes. If the number of running Winword.exe processes reaches the threshold specified in in the XML settings file writes event log.

Event log Example (body)

<event>
<ServerName>WIN-1EQP3L29OVO</ServerName>
<Message>WINWORD concurrent count > 10</Message>
<MessageLong>WINWORD_CONCURRENT_LIMIT = 10</MessageLong>
<Severity>WARNING</Severity>
<Type>WEBSERVICES</Type>
<StartTime>04-18-2016 52:20:28</StartTime>
</event>

Files:
WinwordCountMon.exe  – Service executable.
WinwordCountMon.exe.config – Configuration file that defines .NET 4.0 runtime support (Windows Server 2012) and offline service support.
WinwordCountMon.xml  – Configuration file that defines the service.
WinwordCountMon.ps1 – PowerShell monitoring script.

Schedule:
Perform the WINWORD count check each 30 seconds.
To alter this setting, edit WinwordCountMon.ps1 file, line 57:
Start-Sleep -Seconds 30

2.5.    WinwordTimeMon

The WinwordTimeMon is monitoring WINWORD.exe process time duration. If a single WINWORD process runs more than a time limit specified in in the XML settings file writes event log.

Event log Example (body)

<event>
<ServerName>WIN-1EQP3L29OVO</ServerName>
<Message>Single WINWORD process is running > 5 minutes</Message>
<MessageLong>WINWORD_DURATION_LIMIT = 5</MessageLong>
<Severity>WARNING</Severity>
<Type>WEBSERVICES</Type>
<StartTime>04-18-2016 52:20:28</StartTime>
</event>

Files:
WinwordTimeMon.exe  – Service executable.
WinwordTimeMon.exe.config – Configuration file that defines .NET 4.0 runtime support (Windows Server 2012) and offline service support.
WinwordTimeMon.xml  – Configuration file that defines the service.
WinwordTimeMon.ps1 – PowerShell monitoring script.

Schedule:
Performs the WINWORD processes check each 5 minutes.
To alter this setting, edit MSMQMon.ps1 file, line 57:
Start-Sleep -Seconds 300

3.    Deployment on Windows Server 2012R2

  • Copy over the script and executable files with the exact directory structure on the target machine.
    The Settings.xml file must be placed in “D:\Monitoring_Tools” folder in order to be accessible by the scripts.
  • Use the configuration file generator tool (Create-SettingsXML.exe) to set the threshold and REST settings.XML_Settings_Creator_Steps
  • Create custom Event log container and source.Under PowerShell console (run as administrator) navigate to “D:\Monitoring_Tools\Scripts” and execute the following command:
    .\New-EventLogContainer.ps1 -EventLogName BGBL -SourceName “Letter Monitor Service”Please refer to the script description under “Monitoring Tools Directory Structure” section.
  • Install and start the monitoring services
    Once the settings.xml file is generated, install and start the monitoring services.
    Each folder under “D:\Monitoring_Tools\Services” contains executable which is used to manage the respective service.
    All services need to be loaded into the Windows Service Controller (installed).Example of CpuMon installation:
    From CMD (run as administrator) navigate to the service folder and execute:cpu_mon_console

    If you want to start the service and initiate the monitoring:cpu_mon_console_start
    Under the services in Windows Task Manager, you will see now that the process is loaded and running:Task_ManagerTo stop the CpuMon service you can use the Services console, or CMD:cpu_mon_console_stop
    Once a certain threshold is reached, an event log will be created and automatically send to  the CRM system via REST API.

    CPU_eventlog

Database Documenter for SQL Server + Extended Properties Editor

Database Documenter for SQL Server Tool (SQL/PowerShell/WinForms)

SQL_db_documenter

DESCRIPTION:

Automatically generates documentation of an entire SQL Server database.
Exports in HTML format every SQL object including tables, views, stored procedures, columns, indexes, foreign keys, etc.

Additionally with MS_Description properties supported Extended Properties Editor can add annotations to tables and columns.

SQL_Extended_Properties_Editor

Example of SQL database table output file:
dbo.tblAXAOSCluster

PowerShell Audit Script Managed by Continuum Portal

Requirements:
Audit script that upon a recognized event, sends notifications to given email address with the relevant alert.

The script is run thru an interface using the continuum portal (https://www.continuum.net/) , to a series of customers who have Windows computers.  Access to this portal will be provided.

The script will leave a text file (xml is fine) that holds local configuration data.  No encrypted data on the local machine is permitted.

Audit of the Desktop & Laptop Computer Systems including:

  1. Usage of computers outside normal business hours (custom per practice timeframes)
  2. Unauthorized access attempts (as recorded in the system logs)
  3. Listing of when external hard drives are attached and if they are secure
  4. Encryption confirmation of hard drive
  5. Device relocation (stolen, etc) thru network interface monitoring
  6. Configuration change to each computer system

The script sends two types of alerts:

  • Notice – based on business logic in our service, a notice is sent if an audit is slightly outside of the boundary.
  • Warning – This is a concern that will require the attention of the compliance officer. These are compiled and emailed to the compliance officer weekly, or daily based on severity.

Specific rules:

User Login timeframes:

  • If a computer log shows USER login after 5pm and before 8pm weekdays, a NOTICE is generated.
  • If a computer log shows USER login after 8pm and before 7am weekdays, a WARNING is generated.

Unauthorized access attempts

  • Logs are scanned for 3 or more attempts to access within a 15 minute period. If 3 or more are detected, a NOTICE is generated.
  • If 5 or more are detected, a WARNING is generated.

External Hard Drive

  • Attaching of any external hard drive or USB will generate a NOTICE
  • Attaching of any external hard drive or USB that is NOT encrypted will generate a WARNING

Network Interface

  • If the computer changes the last octet of the IP address (possibly moved in the building), a NOTICE is generated.
  • If the computer changes the IP more substantially, the SNM or the Gateway, a WARNING is generated.

waring_allerts

Secure SQL Database Backups and Logs Synchronization with Amazon Simple Storage Service (S3)

1.    Overview

The SQL/PowerShell automation procedure is designed to perform off-site archiving of local SQL Server full and log backups in the form of compressed and encrypted files (7zip) which are being uploaded to Amazon Simple Storage Service (Amazon S3).

2.    Native Backup SQL Agent Jobs

The SQL native backup process consists of the following jobs:

  • System databases full backup
  • User databases full backup
  • User databases transaction log backup
  • User databases differential backup

2.1 System Databases Full Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_SYSTEM_DATABASES_FULL
sql_agent_jobCreates full backup of system databases locally.
Specification and backup options:

Databases System Databases (Master, MSDB, TEMPDB, Model)
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Full Backup
Verify Verify the backup
CleanupTime 192 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.2 User Databases Full Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_FULL
sql_agent_job_fullCreates full backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Full Backup
Verify Verify the backup
CleanupTime 96 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.3 User Databases Transaction Log Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_LOG
agent_transaction_logCreates transaction log backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Transaction Log Backup
Verify Verify the backup
CleanupTime 25 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.4 User Databases Differential Log Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_DIFF
sql_agent_job_differentialCreates differential backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Differential Backup
Verify Verify the backup
CleanupTime 96 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

3.    PowerShell Tasks & Related SQL Agent Jobs

The procedure consists of the following tasks:

  • PowerShell Script to encrypt and compress backup and log files locally
  • PowerShell Script to copy files to Amazon S3 storage
  • PowerShell Script to delete after time period on Amazon S3
  • SQL Server Agent Job for scheduled execution

3.1 Encrypt and compress backup and log files locally (PowerShell script)

Filename: Bak-to-7z.ps1
Location: E:\S3BackupSync
Description: This script will leverage 7z command-line utility to compress and password encrypt SQL backup and log files.

Input folder: E:\Backup\DATABASE
The folder that contains MSSQL .bak and .trn files.

Output folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are being created.

 

Example of 7z archive file names:

WIN-2PKGIMKGIIA_company_DB_FULL_20160331_021500.bak.7z

WIN-2PKGIMKGIIA_company_DB_LOG_20160404_001001.trn.7z

3.2 Copy compressed files to Amazon S3 (PowerShell script)

Filename: 7z-to-S3.ps1
Location: E:\S3BackupSync
Description: This script will upload 7zip archives to Amazon S3 bucket.

Note: To work properly this script requires AWS SDK for .NET (https://aws.amazon.com/sdk-for-net/) to be installed on the local machine.

Input folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are stored.

Output location:

S3 bucket name: company-backups-new
S3 Region: us-east-1

3.3 S3 bucket obsolete files cleanup (PowerShell script)

Filename: Clean-S3-Bucket.ps1
Location: E:\S3BackupSync
Description: This script will purge obsolete files in Amazon S3 bucket.

Target location:
S3 bucket name: company-backups-new

S3 Region: us-east-1

3.4 SQL Server Agent Job for permanently scheduled execution

The process contains of three SQL Server agent jobs:
sql_server_agent_jobs

3.4.1 S3BackupCleanup

This job will run daily at 6:00:00 AM and once triggered it will call the PowerShell script “Clean-S3-Bucket.ps1” with an argument for the file retention period (in days):
An email notification regarding the job status will be send to Michael Roedeske.
Command:
powershell.exe “&E:\S3BackupSync\Clean-S3-Bucket.ps1 15”

The script will delete all files located on the S3 bucket older than 15 days.

3.4.2 S3BackupSyncFull

This job consist of two steps:
sql_steps

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database full backups files (.bak).

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 bak”

The PowerShell script is being called with an argument “bak” in order to process only SQL full backups.

Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .bak files to S3 bucket.

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 bak”

The PowerShell script is being called with an argument “bak” in order to process only SQL full backup archives.

An email notification regarding the job status will be send to the specified email address.

3.4.3 S3BackupSyncLog

This job also consist of two steps:
s3_syncLog

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database transaction logs (.trn).

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 trn”

The PowerShell script is being called with an argument “trn” in order to process only SQL transaction logs.

Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .trn files to S3 bucket.

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 trn”

The PowerShell script is being called with an argument “trn” in order to process only SQL transaction log archives.

An email notification regarding the job status will be send to the specified email address.

4.    Additional files/tools

more_tools

7z.dll, 7z.exe – 7zip required files
7zip.bin – 7z encrypted password file
PasswordMgr.exe – S3 credentials and 7z password manager

Password Manager GUI tool has been designed to securely store the 7zip encryption password and S3 account keys.
password_tool

7zip password is encrypted and stored into 7zip.bin file.
S3 Access and Secret keys are stored into local secure store and loaded into memory when needed.

The file is located in %LOCALAPPDATA%\AWSToolKit\RegisteredAccounts.json and the keys are obfuscated- either hashed or encrypted.

Note: The stored credentials can be used only by the same user who created the store. %USERPROFILE% environmental variable must be set.

PowerShell script to clear Event Log on remote machines in parallel (WinRM)

  1. Variable for a list of servers.
    #Server array
    $Serverlist = “WORK01”, “WORK02”, “WORK06”
  2.  The script will perform a check if the listed servers accept WinRM connections (used for the remoting).
    The ones which fail will be reported and excluded from the further action.

not_accepting_WinRM

3.  Clear the full Windows Eventlog of the servers.

Clear_EventLog

4. Force Restart of the Server

The event log deletion and restart will be performed in parallel for each available host.
After the Event Log is deleted, the script will wait the servers to reboot for 10min.

#Wait before the monitoring starts
Write-Host “Sleeping for 10 min.”
Start-Sleep -s 600

5. Check if Server is up. Report successful reboot or stuck server.

5_min

all_servers_boot

The reporting will be displayed in the console and also logged to a log file.

#Log file path

$logpath = “c:\temp\log_$(get-date -f yyyy-MM-dd).txt”

Log file example:

log_2015-09-11