Articles
Manitou Core - SQL Query - Adding the MSSQL User and Create SNAPSHOT directory
This script will add the MSSQL user and create the SNAPSHOT directory.
echo 'Type in the MSSQL password you would like to set'; $wshell= New-Object -ComObject Wscript.Shell ; $wshell.Popup("Type in the MSSQL password you would like to set")
...
Manitou Core - SQL Query - Base Query to find Active Customers
-- use this query as a starting point to find active customers
USE MANITOU
select
c.CONTID
, co.FULLNAME
from
CUSTOMER c
join CONTACT co on co.SERIALNO=c.SERIALNO and co.CONTTYPE=1
join MONITORING m on m.SERIALNO=c.SERIALNO and m....
Manitou Core - SQL Query - BoldTrak - Fix Geofences
This script is to fix issues with geofences in BoldTrak. This is generally needed when tracking is presenting a DB error, or if replication is failing on BT_GEOFENCES. USE [MANITOU]
GO
DROP TABLE BT_GEOFENCE
DROP TABLE BT_GEOFENCE_ASSN
DROP...
Manitou Core - SQL Query - Collect Hourly Signal Data for 30 Days
This query pulls signal data for a month and does it for each hour of each day.
This was built by a customer.
use MANITOU
SELECT CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),101) + ' - ' + DATENAME(DW,(...
Manitou Core - SQL Query - Copy Configurations
This script will need to be edited to be customized for the servers.
USE MANITOU
-------------------------------------------------------------
/*VARIABLES - DO NOT EDIT*/
--------------------------------------------------...
Manitou Core - SQL Query - Decode Passwords (Internal Use Only)
DO NOT GIVE THIS TO CUSTOMERS, THIS IS STRICTLY FOR INTERNAL USE!!
A script to decode Manitou passwords if they are ever lost. There is a single line that needs to be edited and is identified by a comment just before it for this query to ...
Manitou Core - SQL Query - Extracting Custom Reports/RDL Files from the Database
The scripts below will assist with extracting the reports as an rdl file from the Manitou database. Please read through the following information before running the scripts. Finding the Report to Extract Before starting, you will first need the r...
Manitou Core - SQL Query - Find Accounts where Programming doesn't match Signaling
This query helps find accounts where the signaling doesn't match the account programming. Specifically, where there is 4X2 programming on the account and the account is sending in Contact ID.
-- this query is used to find where the customer p...
Manitou Core - SQL Query - Find Customer Addresses Query
This query helps you obtain a listing of customers with all the system details and the site telephone number.
USE MANITOU
Select
c.CONTID
, co.FULLNAME
, a.ADDR1
--, a.ADDR2
, ci.NAME as City
, r.NAME as State
, a.POSTCODE
...
Manitou Core - SQL Query - Find Customers with Specific Event Code Programming
Use this query to find Customers that have a specific event code in their customer Transmitter programming translations.
-- Use this query to find customers with a specific event code in their programming.
USE MANITOU
select
c.CONTID
...
Manitou Core - SQL Query - Find Customers with Specific Event Actions Programming
This query is used to find customers with specific Event Actions Programming on their customer record. Please Note, they may request this information be updated or deleted after this information is located. This query only finds the information.
...
Manitou Core - SQL Query - Find Accounts Edited for a Specific Date Range
Use this to find what accounts were edited by whom for a specific time period. Dates AND User IDs will need to be changed to the appropriate range for this to work.
use manitou;
go
SELECT DISTINCT CU.CONTID
, CL1.USRID
, CL1.EVDATE
...
Manitou Core - SQL Query - Find Logs
USE MANITOU
GO
DECLARE @LOG_ID as INT;
DECLARE ID_CURSOR CURSOR FOR
select ID from syslog01
where msgtext like '%Priorities to service%'
and apptype in (1, 4, 16);
OPEN ID_CURSOR
FETCH NEXT FROM ID_CURSOR
INTO @LOG_ID
WHILE...
Manitou Core - SQL Query - Manual Replication Scripts (Part 1)
This is the first part of two scripts that are needed to manually setup replication. This first script will create the distribution database and is required before running Manual Replication Scripts (Part 2) .
Please Note: There are items in...
Manitou Core - SQL Query - Manual Replication Scripts (Part 2)
This is the second part of two scripts that are needed to manually setup replication. This script will creating the publications. Please be sure that the Distribution database has been created by running Manual Replication Scripts (Part 1) .
...
Manitou Core - SQL Query - MEW Factor Report
This report is specifically for getting the MEW factor on a site.
Nothing in this should need to be changed.
select
'Residential' as [Type], count (*) as Cnt, '.333' as Multiplier, cast(count(*) * .333 as int) as [MEW Factor]
...
Manitou Core - SQL Query - QuickSearch Repair Script
This script is used to repair quick search not populating entries on an active Manitou System. Run Query against Manitou database.
OK to run while Manitou is running.
USE MANITOU;
GO
IF OBJECT_ID(N'QUICKSEARCH',N'TF') IS NOT NULL DROP ...
Manitou Core - SQL Query - Retrieving MG Config from Manitou Database
This query will allow you to retrieve the MG config from the Manitou Database.
DECLARE @XMLDATA XML
SET @XMLDATA = (select XMLDATA from MG_CONFIG where GATENO = 1)
SELECT @XMLDATA
Manitou Core - SQL Query - SELECT ACCOUNTS BY PANEL TYPE
/****** SELECT ACCOUNTS BY PANEL TYPE ******/
SELECT
C.[CONTID] AS 'CUSTOMER ID'
, CS.[PANELTYPE] AS 'PANEL TYPE'
, PT.[DESCR] AS 'PANEL TYPE DESCRIPTION'
FROM [MANITOU].[dbo].[CUSTSYS] CS
JOIN [MANITOU].[dbo].[CUSTOMER] C ON C.[SERIALNO] ...
Manitou Core - SQL Query - SELECT BLANK NAME CONTACTS
/****** Script SELECT BLANK NAME CONTACTS ******/
/*
Run script below prior to removing any data
Highlight from Select...contlist
*/
-- Select * into tablebackups..contlist_OrphRecCleanUp from contlist
/*
This script lists all account...
Manitou Core - SQL Query - Select the Alarm Queue via Query
This query is for selecting the alarm queue in the same manner the default alarm queue presents in OWS. This can be used for setting up a excel alarm queue reader for expanding a filter past 1000 rows.
SELECT
MANITOU..ALARM.ALMTIME,
MANITO...
Manitou Core - SQL Query - Select and Update Bulk On Test To Remove
Script to bulk Modify on account On-Test Status
NOTE: Make sure to Save Table before executing
/****** Script to SELECT AND UPDATE BULK ON TEST TO REMOVE ******/
-- SELECT * INTO TEMPTABLES..OUTSRVsv FROM OUTSRV
SELECT
C...
Manitou Core - SQL Query - SELECT ACCOUNTS BY AGENCY
/****** SELECT ACCOUNTS BY AGENCY ******/
USE MANITOU
GO
SELECT
C.CONTID AS 'CUSTOMER ID'
, T.CONTID AS 'AGENCY ID'
, T.FULLNAME AS 'AGENCY NAME'
FROM CUSTOMER C
JOIN CONTLIST L ON L.CONTTYPE = 1 AND L.SERIALNO = C.SERIALNO AN...
Manitou Core - SQL Query - SELECT ACCOUNT BY AUTHORITY
/****** SELECT ACCOUNT BY AUTHORITY ******/
SELECT
CU.[CONTID] AS 'CUSTOMER ID'
, AU.[CONTID] AS 'AUTHORITY ID'
, CO.[FULLNAME] AS 'AUTHORITY NAME'
, AD.[ADDR1]
, AD.[ADDR2]
, AD.[ADDR3]
, AD.[ADDR4]
, CI.[NAME] AS 'CITY'
, RE.[SHORTNA...
SQL Script - Finding Disconnection Date
This script is used for finding an account's disconnection date within Manitou.
SELECT transmitter_id, site_name, discontinued_date, last_event_date
FROM ABMtransmitter
WHERE (last_event_date ...
SQL 2016 Backup Maintenance
SQL 2016 – Creating a Backup Maintenance Plan
Click on Start > Programs > Microsoft SQL Server 2016 > Microsoft SQL Server Management Studio
Connect to the server using ‘sa’ credentials.
In the Object Explorer panel...
Manitou Core - SQL - Upgrading Expired Eval Edition of SQL Server to Full
Steps to Upgrade SQL Server License
1. Download the correct SQL Server Std edition ISO file from the Microsoft Volume Licensing center. May need help from someone with access to the VLSC.
2. Upload the ISO file to the server where the SQL...