Advanced Search Entity SQL Queries

Custom queries are a powerful feature that can be used to retrieve any data from the Patriot databases.

Queries can be written directly in Entity SQL, or created and run directly from the Advanced Search window

Queries can also be used to generate reports via Generic Reporting.

Entity SQL

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.

References:

Running Queries

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.

Example Entity SQL Queries

Client Records

Expected Results Entity SQL

All Clients that have custom Event Types.
(Event Types set up at client level, rather than using templates.)

 
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
(Excluding multiple areas and templates.)

 
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
(Modify time to suit).

 
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.
(In this case: 2005-03-01 to 2005-03-03)

 
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 no site groupings assigned 
SELECT mem.Client_No, mem.Name
FROM PatriotEntities.Memalarm as mem
WHERE NOT EXISTS (
    SELECT 1 FROM mem.ClientGroupingAssigns)
Clients with a particular site grouping assigned 
SELECT mem.Client_No, mem.Name
FROM PatriotEntities.Memalarm as mem
WHERE EXISTS (
    SELECT 1 FROM mem.ClientGroupingAssigns as cga
    WHERE cga.ClientGrouping.Description = 'Commercial' AND
          cga.ClientGrouping.ClientGroupingType.Description = 'Client Type')
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(SqlServer.GetDate(), -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(SqlServer.GetDate(), -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

Attends

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

Users

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

Signals

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

Reports

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

Legacy SQL Queries

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 analyse 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).

How to run a legacy query

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

Client Records

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 no Site Grouping(s) assigned

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

Users

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

Signals

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.