Run the following script to automate creation of a local MS SQL Server LPU user.
use master;
CREATE LOGIN bluemedora WITH PASSWORD = 'tmppassword';
CREATE USER bluemedora FOR LOGIN bluemedora;
GRANT CONNECT SQL to bluemedora;
GRANT VIEW SERVER STATE to bluemedora;
GRANT VIEW DATABASE STATE TO bluemedora;
GRANT VIEW ANY DEFINITION TO bluemedora;
GO
GRANT EXEC on [xp_readerrorlog] to bluemedora
GO
use model;
CREATE USER bluemedora FOR LOGIN bluemedora;
GO
use msdb;
CREATE USER bluemedora FOR LOGIN bluemedora;
GRANT SELECT ON [dbo].[syssessions] TO bluemedora
GRANT SELECT ON [dbo].[sysjobhistory] to bluemedora
GRANT SELECT ON [dbo].[sysjobsteps] TO bluemedora
GRANT SELECT ON [dbo].[sysjobs_view] TO bluemedora
GRANT SELECT ON [dbo].[sysjobactivity] TO bluemedora
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [bluemedora]
-- For SQL Server 2008 R2, use: sp_addrolemember [SQLAgentReaderRole], [sql_lpu]
-- Goes through each user database and adds public permissions
DECLARE @name NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('USE ' + @name + '; CREATE USER sql_lpu FOR LOGIN sql_lpu;' );
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Host
Required
The Microsoft SQL Server to connect to.
Port
The port for communication to the Microsoft SQL Server.
Username
Required
Password
Required
SSL Configuration
The SSL mode to use when connecting to the target. Can be configured to not use SSL (No SSL), use SSL but do not verify the target's certificate (No Verify), and use SSL and verify the target's certificate (Verify).
Instance
Required
The SQL Server Instance to connect to.
Use NTLM v2
Set to true to send LMv2/NTLMv2 responses when using Windows authentication.
Query Count
The number of queries to collect.
Query Order By
Excluded Queries
Connection Pool Size
Thread Pool Size
Maximum number of threads used to perform collection.
Thread Timeout
Exclude Events
Exclude Log Based Events
Event Cutoff Time
How long ago (in minutes) to collect log-based events.
Automated Backup Preference
Group ID
Primary Recovery Health Description
Primary Replica
Replica ID
Replicas
List of replicas being used by availability group.
Resource Name
Secondary Recovery Health Description
Description of recovery health of the secondary replica.
Synchronization Health
Number represenation of rollup of synchronization health of all availability replicas in the availability group.
Synchronization Health Description Property
Description of rollup of synchronization health of all availability replicas in the availability group.
Host
Instance
Is Current Owner
This metric signals whether the current node is the owner of the cluster.
Node Name
This is the name of a single node of a cluster.
Status Text
This metric is the text representation of a node's status.
Status Value
This metric is the integer value that represents a node's status.
Active Connections
Active Transactions
Auto Close
Auto Close Enabled
Auto Create Statistics
Auto Shrink
Auto Shrink Enabled
Auto Update Statistics
Availability Group Name
Buffer Pool Size (Mebibytes)
Creation Date
Data File Size (Kibibytes)
Data Space Usage (Mebibytes)
Disk Space Available (Mebibytes)
Effective Free Space (Mebibytes)
The amount of space on disk that is usable by a database, this includes the free space of the entire disk, and any space being used by the database.
Effective Memory Pages Used (Pages)
Effective Memory Server Total (Mebibytes)
Effective Memory Used Total (Mebibytes)
The amount of effective server memory in MB used by the database.
Host
Index Space Usage (Mebibytes)
Instance
IOPS (Operations)
Log Cache Hit Ratio
Log Cache Hit Ratio Base
Log Cache Reads (per Second)
Log File Size (Kibibytes)
Log File Usage (%)
Log File Used Size (Kibibytes)
Log Pool Cache Misses (per Second)
Database log cache misses over time.
Log Pool Disk Reads (per Second)
Number of disk reads due to log cache misses for a database.
Log Pool Requests (per Second)
Database log cache requests over time.
Log Size (Mebibytes)
Name
Read Delay (Milliseconds)
Read Only
Read Operations (Operations)
Read Total (Bytes)
Recovery Model
Reserved Space (Mebibytes)
Reserved Space Unused (Mebibytes)
Resource Name
Row Size (Mebibytes)
Single User Mode
Size Used (Mebibytes)
Smallest Fixed File Growth Increment (Mebibytes)
Smallest Percent File Growth Increment (Mebibytes)
Space Available (%)
Space Used (%)
Status
Suspended Reason
If the database is suspended, the reason for the suspended state.
Synchronization Health
Synchronization State
Description of the data-movement state.
Time Since Creation (Days)
Total IO Data (Bytes)
Total Space (Mebibytes)
Total Used Disk Size (Mebibytes)
Total database file size on disk, this includes allocated but unused.
Tracked Transactions (per Second)
Transactions (per Second)
User Lookups
User Scans
User Seeks
User Updates
Write Delay (Milliseconds)
Write Operations (Operations)
Write Total (Bytes)
Write Transactions (per Second)
Drive ID
Free Space (Mebibytes)
Host
Instance
Resource Name
Size (Mebibytes)
Allocated Free Space (Mebibytes)
Autogrow Amount (Mebibytes)
Autogrow Fixed (Mebibytes)
Autogrow Rate (%)
Autogrow Type
Database Name
Free Space (Mebibytes)
Host
ID
Instance
Maximum Size (Mebibytes)
Name
Physical Name
Size (Mebibytes)
Space Utilization (%)
State Description
Type Description
Used Space (Mebibytes)
Availability Group Name
Availability Mode Description
Failover Mode Description
Group ID
Server Name
Active Connections (Connections)
Agent Running
Indicates whether or not the agent is running. Agent must run to retrieve MAC Address.
Available Physical Memory (Mebibytes)
Average Query CPU Time (Milliseconds)
Average Query Executions
Average Wait Time (Milliseconds)
Average Wait Time Base (Milliseconds)
AWE Enabled
Background Processes
Batch Compilation Ratio
Batch Requests
Blocked Processes
Buffer Cache Hit Ratio
Buffer Ideal Page Life Expectancy (Seconds)
Buffer Page Life Expectancy (Seconds)
Buffer Pool Hit Ratio
Buffer Pool Size (Mebibytes)
Checkpoint Pages
Cluster Owner Status
Cluster Status
Connection Memory (Kibibytes)
Connections (Connections)
Connections Reset
CPU Count
CPU Usage (%)
Creation Date
Current Cluster Owner
The name of the node in the cluster that is the current owner.
Data File Size (Kibibytes)
Database Cache Memory (Kibibytes)
Database Pages (Pages)
Dead Locked Queries
Deadlocks
Dormant Processes
Effective Total Memory (Mebibytes)
The total amount of memory available between SQL Server and unused memory on the host.
Failed Jobs
Forced Parameterizations
Free List Stalls
Free Memory (Kibibytes)
Granted Workspace Memory (Kibibytes)
High Query CPU Time (Milliseconds)
High Query Executions
Host
Instance
IOPS (Operations)
Lazy Writes
Lock Memory (Kibibytes)
Lock Requests
Lock Timeouts
Lock Wait Time (Milliseconds)
Lock Waits
Log Cache Hit Ratio
Log Cache Hit Ratio Base
Log Cache Reads
Log Cache Requests (Requests)
Log File Size (Kibibytes)
Log File Usage (%)
Log File Used Size (Kibibytes)
Log Pool Cache Misses
Log Pool Disk Reads
Log Pool Memory (Kibibytes)
Logins
Logouts
MAC Addresses
Maximum Server Memory (Mebibytes)
Maximum Thread Count
Maximum Workspace Memory (Kibibytes)
Memory Usage (%)
The percentage of memory that is being utilized by SQL Server.
Most Recent Start Date
MSSQL Architecture Type
Number of Healthy Cluster Nodes
The number of nodes in a cluster in the up state.
Number of Unhealthy Cluster Nodes
The number of nodes in a cluster in a down, paused, or unknown state.
Optimizer Memory (Kibibytes)
OS Architecture Type
OS Memory (Gibibytes)
Page Lookups
Page Reads
Page Splits
Page Writes
Physical Memory Maximum Limit (Mebibytes)
Physical Memory Recommended (Mebibytes)
Physical Memory Used (Mebibytes)
Possible Connections (Connections)
Preconnect Processes
Procedure Cached Pages (Pages)
Product Version
Read Aheads
Read Operations (Operations)
This metric represents the amount of disk read operations over time.
Reserved Server Memory (Kibibytes)
Resource Name
Runnable Processes
Runnable Tasks Count
Running Processes
Server Name
Service Name
Sleeping Processes
SQL Cache Memory (Kibibytes)
SQL Compilations
SQL Recompilation Ratio
SQL Recompilations
SQL Version
Stolen Server Memory (Mebibytes)
Successful Jobs
Suspended Processes
Target Pages (Pages)
Thread Count
Thread Size (Mebibytes)
Time Since Creation (Days)
Time Since Last Start (Days)
Total Disk Space (Mebibytes)
Total IO Data (Bytes)
Total Physical Memory (Mebibytes)
Total Query CPU Time (Milliseconds)
Total Query Executions
Total Read (Bytes)
Total Server Memory (Mebibytes)
Total Written (Bytes)
Tracked Transactions
Transactions
Type
Used Disk Space (Mebibytes)
User Connections (Connections)
Wait Time (Milliseconds)
Windows Version
Write Operations (Operations)
This metrice represents the amount of disk write operations over time.
Write Transactions
Description
Host
ID
Instance
Last Message
Last Run Duration (Seconds)
Last Run Status
Last Run Time
Last Step
Name
Next Scheduled Run
Raw Last Run Status
Command
Host
Instance
Job ID
Last Run Duration
Duration (hhmmss) of the step the last time it ran
Last Run Outcome
Outcome of the previous execution. 0-Failed,1-Succeeded,2-Retry,3-Canceled,5-Unknown
Step ID
Step Name
Step UID
Subsystem
Name of the subsystem used by SQL Server Agent to execute the job step
CPU Time (Milliseconds)
Sum of CPU time used by sessions of this program.
Host
Instance
Logical Reads
Number of logical reads that have been performed by sessions of this program.
Maximum Elapsed Time (Milliseconds)
Elapsed time of longest running session.
Memory Usage (Pages)
Number of 8-KB pages of memory used by this program.
Name
Open Sessions
Reads
Number of reads performed by requests of sessions of this program.
Writes
Number of writes performed by requests of sessions of this program.
Average Execution Time (Microseconds)
Execution Count
Execution Time (Microseconds)
Last Execution (Microseconds)
Name
SQL Handle
Statement End Offset
Statement Start Offset
Text
Total Elapsed Time (Microseconds)
Total Logical Reads
Total Logical Writes
Total Physical Reads
Unique Query Plans
Host
Instance
Wait Time (Milliseconds)
Total wait time for this wait type in milliseconds.
Wait Type
The name of the type of wait being reported
Waiting Tasks Count
Number of waits on this wait type. This counter is incremented at the start of each wait.