Dual Pins for Users

Like I said I’m not quite sure what the new action does or how it works… I was going on the possibility that it logs in a user when actioned?

And if so… could it be constrained to action if a pin entered on a numberpad within an ask question action would work…?

For switch user I imagine numberpad entered -> validated as a pin --> ticket layed away --> ticket closed --> current user logged out --> user for verified pin is logged in

The subject of the topic was dual pin so (and I see some backlash from kendash :stuck_out_tongue_winking_eye: ) would be something like your create user automation flow which duplicates username as entity with a rfid and pin fields, one being the default user pin and the other manual pin being used to check the new Pin Code Entered rule to allow a user to have 2 logins, long rfid pin and shorter manual pin.

This is what the Action and Rule look like.

The Login User Action requires the User Name as it’s parameter. No PIN.

The Pin Code Entered Rule will only be fired if the PIN does not match a configured User PIN. That is, it only triggers on NON-valid PINs.

So you could theoretically load up the Rule with multiple Login User Actions, each with their own PIN constraint (ie. for a FOB number), which then fires JScript to look up the matching User Name for that PIN, whether that User Name comes from a SQL call, or an API call to a Staff Entity Custom Data Field, to return a User Name that can then be used with the Login User Action…

5 Likes

Awesome! Gonna be playing with this tomorrow whilst having a hangover in bed hahah

That’s what I was trying to convey with little time to spell it out. Thank you Q for clarifying my thought for them :slight_smile:

Documenting to get FOB/User from CustomData (SQL 2016) …

##Query

SELECT
e.[Id]
,e.[Name]
,jsonData.jsonValue as [FOB]
--,e.[CustomData]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId]
-- here we "join" the [Entities] table to itself and use the OPENJSON function
-- on the [CustomData] column
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (
  jsonName  varchar(50) '$.Name'
 ,jsonValue varchar(50) '$.Value'
) jsonData
WHERE et.[Name] = 'Employees'
AND cf.[Name] = 'FOB'
AND jsonName = 'FOB'


##Function

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
  var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) '$.Name', jsonValue varchar(50) '$.Value') jsonData WHERE et.[Name] = '"+entityType+"' AND cf.[Name] = '"+fieldName+"' AND jsonName = 'FOB' AND jsonValue='"+fieldValue+"'";
  var userName = sql.Query(qry).First;
  return userName || 'NOTFOUND';
}

Sample Calls:

getEntityByCustomDataFieldValue('Employees','FOB','123456') // returns Entity Name 'Q'
getEntityByCustomDataFieldValue('Employees','FOB','135')    // returns 'NOTFOUND'
2 Likes

Assuming you have an Entity Type named “Employees”, and it has a Custom Data Field with the name “FOB”, and the Entity Name matches a valid User Name …


Script:

:warning: Uses SQL syntax valid in SQL 2016 or later.

USER [user] (Script)

Script Name: USER
Script Handler: user

Script:

:warning: Uses SQL syntax valid in SQL 2016 or later.

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
  var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) '$.Name', jsonValue varchar(50) '$.Value') jsonData WHERE et.[Name] = '"+entityType+"' AND cf.[Name] = '"+fieldName+"' AND jsonName = 'FOB' AND jsonValue='"+fieldValue+"'";
  var userName = sql.Query(qry).First;
  return userName || 'NOTFOUND';
}


Actions

USER ExecAMC [Execute Automation Command] (Action)

Action Name: USER ExecAMC
Action Type: Execute Automation Command

Parameters:

Automation Command Name: [:AMCname]
Command Value: [:AMCvalue]
Background: [:runBG]
Delay: [:delay]

USER Login User [Login User] (Action)

Action Name: USER Login User
Action Type: Login User

Parameters:

User Name: [:userName]

Rules:

USER PIN Entered [Pin Code Entered] (Rule)

Rule Name: USER PIN Entered
Event Name: Pin Code Entered
Rule Tags:
Custom Constraint List (0):
Execute Rule if: Matches

Actions (1):

USER ExecAMC

Constraint: (none)

AMCname: USER Login by FOB
AMCvalue: {CALL:user.getEntityByCustomDataFieldValue('Employees','FOB','[:Pin]')}
runBG:
delay:

USER Login by FOB [Automation Command Executed] (Rule)

Rule Name: USER Login by FOB
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (1):
Execute Rule if: Matches
Automation Command Name Equals USER Login by FOB

Actions (2):

USER Login User

Constraint: ‘[:CommandValue]’ != ‘NOTFOUND’

userName: [:CommandValue]
MSG TEST

Constraint: ‘[:CommandValue]’ == ‘NOTFOUND’

MessageToDisplay: PIN not Valid!

4 Likes

Hey guys, Ive been trying to get this to work but I’ve run into a problem I can’t figure out. I thought it might be a permissions issue but i tried to run it on the server pc and it also didn’t work

Its a little tricky when you only show a small bit of the code…
Are you running sql 2016 or higher? This script uses a JSON parser introduced in 2016 which wont work in previous versions.

Yea I’ve upgraded my SQL database to 2017

the script is the same from the post

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
var qry = “SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) ‘$.Name’, jsonValue varchar(50) ‘$.Value’) jsonData WHERE et.[Name] = '”+entityType+"’ AND cf.[Name] = ‘"+fieldName+"’ AND jsonName = ‘FOB’ AND jsonValue=’"+fieldValue+"’";
var userName = sql.Query(qry).First;
return userName || ‘NOTFOUND’;
}

I also have the entity list Employees and the custom field FOB

Can you show the entity tag screen showing the field names, entity type?
Do you get any errors?
Id sugest trying the sql code in sql manager replacing the “+entityType+”, “+fieldName+” and “+fieldValue+” with string values that would be used in samba.

I am going to try and change the field type to number maybe that could be it.

I will try it in SQL manager to see if it works.

Type shouldn’t matter for a simple where =
There is defiantly a entity with that fob value?
What do you get back? Maybe remove the || NOTFOUND and put call in a show message or ask question to see what your getting back.
Where are you using the call?

1 Like

Yea there is an entity with the FOB value. The rest of the set up works, when I enter a number that isn’t active I return with a wrong pin message.

I will tinker some more after we close tonight. If I find anything I will post it.

So wrong pin gives wrong pin messgae? What happens with correct pin? This suggests the scrip isnt the problem and its the login automation.

I believe the problem is that the script isnt working, so when a FOB is entered it doesnt find it and return a wrong pin number message. Users can log in with their normal user numbers (the 4 digit pin).

Yes but your presumably catching invalid default user pins entered, the normal user pins should always work else you could messup automation and not be able to login…

So if you enter an invalid pin which isnt normal user pin or a ‘second’ pin it gives an error message?
But a pin configured in entity for FOB returns nothing?

If it returns message for invalid pin this is not default setup so is part of your automation.
If entering invalid pin/fob number gives message the script is doing something.
This is why I said add a show message or ask question action to show the value returned by the script call so you can validate it.

What does the message say? if its anything other than ‘NOTFOUND’ the message is part of your automation and your triggering based on that response.
So the problem (without seeing what the script is returning) could be your automation to login using the returned value.

Without seeing your setup its not possible to say for sure.

Show your automation for the second pin login?

Just to check, does the entity primary field (Name) exactly match the user name for samba login?

Actions:

Rules:


Here is a example employee entity:

in my set up name is the same as username

image

so what does this test return?