DESCRIPTION:

It’s helpful to know serial numbers of your Windows servers especially for inventory reports. It’s set to only update every 60 minutes (the least frequent interval)

Serial-Number

INSTRUCTIONS:

  1. Create a LogicMonitor user account (e.g. “api_user”) so you can use the API commands. I recommend you create a separate role because then you will only need limited permissions to “devices” (ie not dashboards, settings, reports, etc)
  2. Download the datasource (XML file) and add it to your company account via Settings > Datasource > Add
  3. Set your username and account in the script (username= and account= at top of script). Account is the part before “logicmonitor.com” in the URL. Usually it’s your company name.

NOTES:

To troubleshoot, you can look at the “Raw data” tab and verify “200” status code is returned. You can also copy the Powershell script and use the “Debug command” screen and run the script (you’ll have to hardcode some values where ##hostname## and similar values are).

DESCRIPTION:

This allows you to avoid getting alerts when downstream device like a router or switch fails so you don’t get alerts on the other devices connected to it.

I have 2 versions. One that sets SDT on child groups and one that sets SDT on all devices in the same group where the applied device is located.

This is sometimes necessary to comply with security compliance or concerns.

The downside is this takes ~2 minutes to click/configure for each target computer. I have not seen any method to automate this with powershell or other scripts but I wouldn’t be surprised if someone spend a few hours on it, they might be able to automate some or all of it.

 

DESCRIPTION:

Sometimes you need to know when a certain quantity of old files are sitting in a folder and shouldn’t be. For example, a daily process normally moves/transfers files to a different server but somehow it missed one.

INSTRUCTIONS:

  1. Download the datasource file (XML) and import it into your account (Settings > datasource > add from XML file)
  2. Set these properties on the device so the datasource ‘applies to’ some device (for example)
    file_spec =  \\server1\share\*.txt
    minutes_age = 33
  3. If desired, set the threshold to alert you on a certain quantity of files. Do this by setting the datapoint within the datasource as shown in screenshot.

 

Screenshot below shows datasource and related graph:

Qty-old-files

Below shows where to set threshold for Qty of files:

Qty-old-files-threshold

For the technically curious, below is the PowerShell script I’m using:

$file_spec="##file_spec##"
$minutes_age = ##minutes_age##
$file_list = Get-ChildItem -path $file_spec | where {($_.Lastwritetime -lt (date).addminutes(-$minutes_age)) -and (! $_.PSIsContainer)}

Write-Host "The file list is... $file_list" # just show the list

$Qty = @($file_list).Count # counts the qty in list

Write-host "minutes_age threshold: $minutes_age"
Write-host "qty of files older than threshold minutes: $Qty"

DESCRIPTION:

Sometimes people want to know if one of their co-workers or subordinates disabled a SQL job.

INSTRUCTIONS:

Download the datasource file and add it to LogicMonitor (Settings > Datasources > Add from XML file)

It should alert you if enabled is not 1. If you want to ignore some jobs, just click on that instance and turn off alerting or monitoring.

It’s set to use Windows Integrated Authentication but if you want to use SQL authentication, you can change the datasource and specifying a SQL username and password.

Download the datasource file here

NOTES:

I did this with a datasource that uses JDBC collection type to run a SQL query on the database to list the jobs and show those job names as instances. The collection portion of datasource runs another SQL query for each of those jobs to retrieve the current setting for “enabled”.

SQL-jobs-disabled-salesDemo

 

DESCRIPTION:

This will alert you via LogicMonitor when a MS-SQL ‘job’ fails

INSTRUCTIONS:

  1. Create a LogicMonitor “EventSource”. Named “SQL job failures” or similar. I suggest that you clone the default “SQL eventsource”.  Set these settings:
    LogName = “Application”
    SourceName = “SQLSERVERAGENT”
    Level more urgent than “Information”
  2. Use Microsoft’s SQL Management Studio to set each job on each of your SQL servers so that when a job fails, it will write a message to the Windows Application event log (screenshot below)
    Right click on job and click “Properties”
    Click on “Notifications” on left pane
    Click on “Write to Windows event logs” checkbox and click “When the job fails”
  3. Test. I suggest you create a job with a bogus query and run it. Look in the Windows Event viewer and you should notice an EventID 208 at “warning” severity.

 

Optional:  In LogicMonitor you can create an Alert rule that notifies a specific person or team. Do this by selecting the datasource name of the eventSource you named above

notes:  I tested this with SQL 2008-R2 but it will probably also work with newer versions.

 

Below is screenshot showing how to create/clone a LogicMonitor EventSource

SQL-EventSource

Below shows how to set “Write to event log” on each SQL Job

SQL-jobs

Below shows what the alert looks like in LogicMonitor.

LM-SQL-alert

Video:

DESCRIPTION:

A special SQL “stored procedure” can detect the slowest queries and tell you what username ran them.

The PowerShell script runs 2 test SQL queries a few seconds apart. If both queries take longer than the threshold you specify, then it will run the stored procedure mentioned above to find details of the 10 longest running queries and the users who ran them. It writes this info to the Windows event log (application log) where LogicMonitor can alert on it.

DISCLAIMER: Use at your own risk. LogicMonitor tech support is not responsible for tech support on this datasource.

INSTALL/PREPARATION:

  1. Download this Stored Procedure to your SQL server and open it in Microsoft’s SQL Management Studio and run it ( It kind of installs it) so it can be called/run in the future via a PowerShell script.  Read and download here:  http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx 
  2. Run this PowerShell command to do the one time “registration” of the event source. Syntax is a note at beginning of the script.
     New-EventLog -ComputerName server1 -Source Top-SQL -LogName Application
  3. Change the speed test query so it uses YOUR database. It’s easy to edit it at the beginning of the script (in $test_query variable).
  4. Since LogicMonitor does not collect the Windows Application events by default, please add this EventSource. One suggested method is to clone the similar eventSource that collects System event log and change the “LogName” from “System” to “Application”

TESTING:

In order to test this script, I recommend use the sample database from Microsoft called “AdventureWorks” which is a backup file you need to “restore” into your SQL server.
http://msftdbprodsamples.codeplex.com/releases

To simulate load on the SQL server and force queries to run slower, I recommend running this looping script on the sample database
 USE AdventureWorks2014
 GO
 DECLARE @Flag INT
 SET @Flag = 1
 WHILE(@Flag < 8888)
 BEGIN
 ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
 ON [Sales].[SalesOrderDetail] REBUILD
 SET @Flag = @Flag + 1
 END
 GO

The screenshot below shows the alert as it appears in LogicMonitor.

Top-SQL

Follow

Get every new post delivered to your Inbox.