Patriot 6 uses Entity SQL to query the database. While similar to standard SQL, there are a few differences. Creating new Entity SQL queries is outside the scope of this document and not covered by the patriot support assurance policy, however there are many books and online references available on how to write them.
Entity SQL queries only allow existing data to be retrieved. They cannot insert, update or delete data.
Go to: Reports Menu Item -> Queries -> Stored Queries
Click on the Insert (+) button in the bottom left corner to create a new query.
Enter an appropriate name into the query name field
Copy the desired query from this page and paste it into the Query field.
Save the new query, then click the Run Query button to execute the query in the Advanced Search Window.
Queries can also be created and run directly from the Advanced Search window, or used for Generic Reporting.
Expected Results | Entity SQL |
---|---|
All Clients that have custom Event Types. |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.CurrentWorkGroup = 0 AND COUNT (SELECT VALUE t.type_no FROM mem.mtype as t) > 0 |
All Clients with information entered in the Notes field. |
SELECT mem.Client_No, mem.Name, mem.Note FROM PatriotEntities.Memalarm AS mem WHERE Length(Trim(CAST(mem.Note AS String))) > 0 |
Total number of Clients |
SELECT COUNT(DISTINCT mem.ClientID) AS [Client Count] FROM PatriotEntities.Memalarm AS mem WHERE mem.CurrentWorkGroup = 0 |
All Clients that have at least 1 zone with the name "Panic". |
SELECT DISTINCT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE EXISTS (SELECT zone.zone_area FROM mem.MZone AS zone WHERE zone.Zone_area = "Panic") |
All Clients who have logged a signal since a specific time |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.CURRENTWORKGROUP = 0 AND EXISTS( SELECT signal.recdatetime from mem.Signal AS signal WHERE signal.recdatetime > DATETIME'2006-05-15 00:00:00.000' ) |
All Clients who have received a signal with no description. |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.CURRENTWORKGROUP = 0 AND Exists(SELECT signal.Description FROM mem.Signal AS signal WHERE signal.Description == "") |
Count of all signals over a given period. |
SELECT COUNT(sig.RecDateTime) AS [Signal Count] FROM PatriotEntities.Signal AS sig WHERE sig.RecDateTime Between DATETIME'2005-03-01 00:00:00.000' AND DATETIME'2005-03-03 00:00:00.000' |
All Clients currently on permanent Test Mode | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.testmode = 1 AND mem.TestModeDateTime > DATETIME'2098-01-01 00:00:00' |
All archived signals for a particular Client. | SELECT sig.recdatetime, sig.description FROM PatriotEntities.SignalArchives AS sig WHERE sig.client_no = '0003000101' |
All Clients who have not received a particular type of signal (Filtered by IDActionPlan = 18) |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE NOT EXISTS ( SELECT sig.IDAuto FROM mem.Signal AS sig WHERE sig.IDActionPlan = 18 AND sig.recdatetime > DATETIME'2005-01-01 00:00:00' ) ORDER BY mem.Client_No |
All Clients with Unset Monitoring enabled. | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.UNS_MON <> 0 |
All clients with Open/Close times set but no Auto Status Monitoring settings enabled. | SELECT DISTINCT(mem.Client_No), mem.Name FROM PatriotEntities.Memalarm AS mem WHERE (mem.UNS_MON IS NULL OR mem.UNS_MON = 0) AND EXISTS (SELECT opcl.pair FROM mem.openclostimes AS opcl) |
All clients with No Signals Monitoring enabled but missing the 65002 reminder. | SELECT DISTINCT(mem.Client_No), mem.Name FROM PatriotEntities.Memalarm as mem WHERE mem.NOSIGS_MON <> 0 AND NOT EXISTS (SELECT rem.Client_No FROM mem.Reminders as rem WHERE rem.Type = 65002) |
Clients with No Signals Monitoring set to Not Active | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm as mem WHERE mem.NoSigs_Mon = 0 AND mem.CurrentWorkGroup = 0 |
Clients with a blank client type under site groupings. | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm as mem WHERE mem.CLIENTTYPE1 = 0 AND mem.CLIENTTYPE2 = 0 AND mem.CLIENTTYPE3 = 0 AND mem.CLIENTTYPE4 = 0 AND mem.CLIENTTYPE5 = 0 |
Clients with an Install Date within a custom date range. (Modify time to suit). |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm AS mem WHERE mem.InstallDateTime BETWEEN DATETIME'2007-11-01 00:00:00' AND DATETIME'2007-11-29 23:59:59.997' |
Client number, Zone Name and special request. | SELECT zone.Client_No, zone.Zone_No, zone.Zone_area AS [Zone Name], zone.ActionPlan.Description AS [Action Plan], zone.SpecRequest FROM PatriotEntities.MZone as zone |
Any Clients with a particular panel type and No Signals Monitoring enabled. (In this example, searching for panels named "DSC") |
SELECT mem.Client_No, mem.Name, panel.name AS [Panel] FROM PatriotEntities.Memalarm AS mem JOIN PatriotEntities.PanelTypes AS panel ON mem.paneltypeid = panel.idpaneltypes WHERE panel.name LIKE "%DSC%" AND EXISTS ( SELECT sig.type_no FROM mem.signal AS sig WHERE sig.type_no = 65002 AND sig.recdatetime > (AddDays(CurrentDateTime(), -7)) ) |
All clients on ports 01 or 11 (modify to suit) where a Schedule has
been loaded but none of the Auto Status Monitoring buttons have been ticked. |
SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm as mem WHERE (mem.PortID = '01' OR mem.PortID = '11') AND ( EXISTS(SELECT opcl.Pair FROM mem.OpenClosTimes as opcl) OR (mem.AltOpenClos = 1 AND mem.AltOpenClosNo <> '') ) AND mem.UNS_MON = 0 AND mem.CurrentWorkGroup = 0 |
Clients with No Signals Monitoring disabled who have sent a signal in the last 31 days. | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm as mem WHERE mem.nosigs_mon = 0 AND EXISTS ( SELECT sig.type_no FROM mem.signal as sig WHERE sig.recdatetime > (AddDays(CurrentDateTime(), -31)) ) |
Clients who are on permanent testmode with reason 'Disconnected'. | SELECT mem.Client_No, mem.Name FROM PatriotEntities.Memalarm as mem WHERE mem.testmode = 1 AND mem.TestModeReason='Disconnected' AND mem.TestModeDateTime > DATETIME'2098-01-01 00:00:00' |
Clients who have more than one Installer (Dealer) type user assigned to them. | SELECT mem.Client_No FROM PatriotEntities.Memalarm as mem JOIN PatriotEntities.usertoclient as utc on utc.ClientNo = mem.Client_No JOIN PatriotEntities.Muser as u on u.UserId = utc.UserId WHERE u.Type = 'N' GROUP BY mem.Client_No HAVING COUNT(u.UserId) > 1 |
Clients who have an invalid client number. The client number is quoted to more easily find invalid space characters. | SELECT '"' + mem.Client_No + '"', mem.Name FROM PatriotEntities.Memalarm as mem WHERE Contains(mem.Client_No, ' ') OR Length(mem.Client_No) < 7 |
Expected Results | Entity SQL |
---|---|
The total number of attends for port 1 Clients during the month of
February 2014. Ordered by highest number of attends. (Adjust the dates as needed). |
SELECT att.Client_No, COUNT(att.AttendID) AS [Attend Count] FROM PatriotEntities.Attend AS att WHERE att.Memalarm.PortID = '01' AND att.ActDateTime BETWEEN DATETIME'2014-03-16 00:00:00.000' AND DATETIME'2014-03-17 00:00:00.000' GROUP BY att.Client_No ORDER BY COUNT(att.AttendID) DESC |
Expected Results | Entity SQL |
---|---|
Filters all clients by user where a unique code is specified in
relation to the site. (In this example, a code of '710' and a user I.D of '43'). |
SELECT utc.clientno, utc.Memalarm.name, utc.code, utc.muser.UserId, utc.muser.user_name FROM PatriotEntities.UserToClient as utc WHERE utc.code = '710' AND utc.muser.userid = 43 |
The User Grouping, User ID, Name, and Email address of Users assigned to a User Group. | SELECT uug.UserGroupings.Description, uug.Muser.UserID, uug.Muser.User_Name, uug.Muser.Email FROM PatriotEntities.UserToUserGroupings as uug |
Identify users in the database assigned to a user grouping. (In this case 'Monthly email'). |
SELECT uug.Muser.UserID, uug.Muser.User_Name, uug.Muser.Email FROM PatriotEntities.UserToUserGroupings as uug WHERE uug.UserGroupings.Description = 'Monthly email' |
The code field (containing the number of keys held) for each client that has a security type user assigned | SELECT utc.ClientNo, utc.Memalarm.Name, utc.Code FROM PatriotEntities.UsertoClient as utc WHERE utc.Muser.Type = 'S' |
Reports the clients where a defined user '##' by number doesn't appear. | SELECT mem.Client_no, mem.name FROM PatriotEntities.memalarm as mem WHERE NOT EXISTS ( SELECT utc.userid FROM mem.usertoclient as utc WHERE utc.userid = ## ) |
Identifies Clients/Users where the User has an Action Plan set i.e. is not set to Null Action Plan: | SELECT DISTINCT utc.ClientNo, utc.UserID, utc.MUser.User_Name, utc.ActionPlan.Description as [Action Plan] FROM PatriotEntities.UserToClient as utc WHERE utc.IDActionPlan <> 0 |
Expected Results | Entity SQL |
---|---|
Count all outgoing messages from a messaging task (email, sms, pages
etc). Replace "sig.Zone_User = 6" with task number of messaging task, or remove to count all messages sent. |
SELECT COUNT(sig.idauto) as [Message Count] FROM PatriotEntities.Signal as sig WHERE sig.AlarmType = 65027 AND sig.Zone_User = 6 |
Expected Results | Entity SQL |
---|---|
A list of all User Groupings, and the Preset Reports that are currently assigned to them. | SELECT UG.IDUserGroupings AS [User Group ID], UG.Description AS [User Grouping], RN.Name AS [Assigned Preset Report] FROM PatriotEntities.UserGroupings AS UG LEFT OUTER JOIN PatriotEntities.PresetReports AS PR ON UG.IDUserGroupings = PR.UserGroupID LEFT OUTER JOIN PatriotEntities.ReportNames AS RN ON PR.IDReportNames = RN.IDReportNames ORDER BY UG.IDUserGroupings |
It is also possible to run standard SQL queries through the legacy advanced search window.
This section contains a listing of advanced search queries you can run to analyze and troubleshooting your Patriot data.
WARNING! All queries on this page are SELECT queries only. This means that they will only allow the retrieval and viewing of Patriot data. Patriot Systems can not assume responsibility for any loss or damaged caused by the running of UPDATE or DELETE queries in Patriot 6. SQL queries also do not come under the jurisdiction of the Patriot systems software support and assurance assurance policy, due to being SQL related. We strongly recommend backing up your data first.
WARNING! Running an SQL query will create extra work for your SQL Server. To reduce the workload you can minimize searches by adding the command TOP 50 into the query. E.g. SELECT TOP 50 Client_No, Name...
This will only allow the first 50 records to be returned. (You can set this value as required).
1. Highlight the query (from below) and copy it by pressing ctrl-c
2. Go to: Reports Menu Item -> Saved Reports -> Advanced Search 52
3. Highlight and delete the default query from the Database Search window
4. Press ctrl-v to paste the query copied in step 1 above
5. Click the Run Query button to execute the query
Returns:All Client Records that are using Types at Site Event level.
SELECT Client_No, Name FROM Memalarm WHERE EXISTS (SELECT * FROM MType WHERE Memalarm.Client_No = MType.Client_No AND Memalarm.CurrentWorkGroup = 0) |
Returns: All Clients with information entered in the Notes field
select client_no, name, notes from memalarm where isnull(cast(notes AS varchar),'') <> '' |
Returns: The number of base clients excluding multiple areas and templates:
SELECT COUNT(DISTINCT ClientID) FROM Memalarm WHERE CurrentWorkGroup = '0' |
Returns:Returns all clients that have Panic or Duress Zones assigned
Query: SELECT DISTINCT Memalarm.Client_No, Memalarm.Name FROM Memalarm, MZone WHERE EXISTS (SELECT * FROM MZone WHERE MZone.Client_No = Memalarm.Client_No AND MZone.Zone_area = panic) |
Returns:All clients who have logged a signal in the last number hours specified; (modify time to suit)
SELECT Distinct(memalarm.Client_No), memalarm.Name FROM Memalarm JOIN signal on Memalarm.Client_No = signal.client_no where CURRENTWORKGROUP = '0' AND signal.recdatetime > '2006-05-15 00:00:00.000' |
Returns: All Clients who have received a signal with no description
SELECT Memalarm.Client_No, Memalarm.Name, Signal.Description FROM Memalarm, Signal WHERE (Signal.Description = '' AND Memalarm.Client_No = Signal.Client_No) |
Returns: Counts all signals over a given period, in this case 2005-03-01 to 2005-03-03
SELECT count(*) FROM Signal WHERE RecDateTime Between '2005-03-01 00:00:00.000' AND '2005-03-03 00:00:00.000' |
Returns: All Clients currently on permanent Test Mode at client level
SELECT Client_No, Name, Client_No FROM Memalarm WHERE test mode=1 and TestModeDateTime > '20980101 00:00:00' |
Returns: All archived signals for a particular client.
SELECT recdatetime, description from patriotarchive.dbo.signalarchive where client_no = '0003000101' |
Returns: All Clients who have not received a particular type of signal (filtered by IDActionPlan = 18)
SELECT Client_No, Name, Client_No FROM Memalarm WHERE NOT EXISTS (SELECT * FROM Signal WHERE Signal.Client_No = Memalarm.Client_No AND Signal.IDActionPlan = 18 AND signal.recdatetime > <d1>) ORDER BY Client_No |
Returns: All Clients with Unset Monitoring enabled
SELECT Client_No, Name FROM Memalarm WHERE UNS_MON <> 0 |
Returns: All clients with Open/Close times set but no Auto Status Monitoring settings enabled.
SELECT DISTINCT(Client_No), Name FROM Memalarm WHERE ISNULL(UNS_MON, 0) = 0 AND EXISTS(SELECT * from openclostimes WHERE openclostimes.clientNo= memalarm.client_No) |
Returns: All clients currently sitting in a disabled test mode.
SELECT Client_No, Name, Client_No FROM Memalarm WHERE TESTMODE = 1 AND TestModeDateTime > '20980101 00:00:00' |
Returns: All clients with No Signals Monitoring enabled but missing the 65002 reminder.
SELECT DISTINCT(Memalarm.Client_No), Name FROM Memalarm, Reminders WHERE NOSIGS_MON <> '0' AND Memalarm.Client_No = Reminders.Client_No AND NOT EXISTS(SELECT Client_No FROM Reminders WHERE Type = '65002') |
Returns:Clients with no signals monitoring set to Not Active:
SELECT Client_No, Name FROM Memalarm WHERE NoSigs_Mon = '0' AND CurrentWorkGroup = '0' |
Returns:All clients with a particular Zone Area description e.g. 'panic'
SELECT DISTINCT Memalarm.Client_No, Memalarm.Name FROM Memalarm, MZone WHERE EXISTS (SELECT * FROM MZone WHERE MZone.Client_No = Memalarm.Client_No AND MZone.Zone_area = 'panic') |
Returns: Returns clients with a blank client type under site groupings.
SELECT Client_No, Name FROM Memalarm WHERE (CLIENTTYPE1 = ) and (CLIENTTYPE2 = ) and (CLIENTTYPE3 = ) and (CLIENTTYPE4 = ) and (CLIENTTYPE5 = ) |
Returns:All clients entered into the database within a custom date range (modify time to suit).
SELECT Client_No, Name FROM Memalarm WHERE InstallDateTime BETWEEN '2007-11-01' AND '2007-11-29 23:59:59.997' |
Returns: Client number, Zone area and description and special request
Select Client_No, Zone_No, Zone_area, Description, SpecRequest from MZone JOIN ActionPlan on Mzone.IDActionplan = Actionplan.IDActionplan |
Returns: Any user defined panel currently receiving no signals monitoring on (modify DSC to suit)
SELECT memalarm.Client_No, memalarm.Name, paneltypes.name FROM Memalarm join paneltypes on memalarm.paneltypeid = paneltypes.idpaneltypes where paneltypes.name like "%DSC%" and exists (select * from signal where memalarm.client_no = signal.client_no and signal.type_no = 65002 and recdatetime > (GetDate() - 7)) |
Returns: All clients on ports 01 or 11 (modify to suit), where: - time schedule has been loaded but none of the 'auto status monitoring buttons' have been ticked
SELECT Client_No, Name FROM Memalarm WHERE (PortID = '01' OR PortID = '11') AND (EXISTS(SELECT * FROM OpenClosTimes WHERE ClientNo = Client_No) OR (AltOpenClos = '1' AND AltOpenClosNo <> '')) AND UNS_MON = '0' AND CurrentWorkGroup = '0' |
Returns: Returns all clients with no signals monitoring disabled who have sent a signal in the last 31 days
SELECT Client_No, Name FROM Memalarm WHERE NOSIGS_MON = 0 AND EXISTS(SELECT * FROM Signal WHERE Signal.Client_No = Memalarm.Client_No AND DateDiff(day,RecDateTime,GetDate()) < 31) |
Returns: Returns all clients who are on permanent testmode with reason 'Disconnected'
SELECT Client_No, Name FROM Memalarm WHERE TestMode=1 AND TestModeReason='Disconnected' AND DatePart(year,TestModeDateTime)=9999 |
Attends
Returns: The total number of attends for port 1 Clients during the month of February 2004, ordered by highest number of attends. (The query can be altered by changing the port: RIGHT(Client_No,'1') = '1' and the between dates).
SELECT Client_No, COUNT(AttendID) FROM Attend WHERE RIGHT(Client_No,'1') = '1' AND ActDateTime Between '2004-03-16 00:00:00.000' AND '2004-03-17 00:00:00.000' GROUP BY Client_No ORDER BY COUNT(AttendID) DESC |
Expected Results | T-SQL |
---|---|
Filters all clients by user where a unique code is specified. In this case a code of '710' and a user I.D of '43'. |
SELECT client_no, name, code, muser.UserId, user_name FROM muser JOIN UserToClient on Muser.UserId = usertoclient.UserId JOIN Memalarm on usertoclient.clientno = memalarm.client_no WHERE code = '710' AND muser.userid = '43' |
The User Grouping, User ID, Name, and Email address of Users assigned to a particular User Grouping, in this case "Monthly email". |
SELECT Description, Muser.UserID, User_Name, Email FROM Muser, UserToUserGroupings, UserGroupings WHERE Muser.UserID = UserToUserGroupings.UserID AND UserGroupings.IDUserGroupings = UserToUserGroupings.IDUserGroupings AND description = 'Monthly email' |
The code field (containing the number of keys held) for each client that has a security type user assigned. |
SELECT ClientNo, Name, Code FROM Muser, UsertoClient, Memalarm WHERE Muser.UserId = UsertoClient.UserId AND UsertoClient.ClientNo = Memalarm.Client_No AND Type = 'S' |
Reports the clients where a defined user '##' by number doesn't appear. |
SELECT Client_no, name FROM Memalarm WHERE NOT EXISTS ( SELECT * FROM usertoclient WHERE usertoclient.clientno = memalarm.client_no AND (userid = '##')) |
Identifies Clients where they have a Site Action Plan assigned. (IDActionPlan = 0 means that Null Action Plan is selected.) |
SELECT DISTINCT ClientNo FROM Memalarm WHERE IDActionPlan <> 0 |
Expected Results | T-SQL |
---|---|
Count of all outgoing messages from messaging task (email, sms, pages etc.) |
SELECT Count(*) FROM SIGNAL WHERE AlarmType = 65027 AND Zone_User = 6 Replace "Zone_User = 6" with task number of messaging task, or remove to count all messages sent. |