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:
While ESQL provides the CurrentDateTime() function, it is more efficient to use the SQL Server specific SqlServer.GetDate() function instead, as this can dramatically reduce query execution time.
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
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
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
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
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
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