Stanly PAC Access Control and ScreenCloud

8 Mar

A while back I put in a Stanly PAC Access Control system so staff can use a printed ID card to gain access to the head office. This system uses a SQL Server Express database to log everything. So I thought, wouldn’t it be nice to greet everyone when they came into the office, and so we began…

First, enable XP_CMDSHELL on SQL Server:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Then, create a Trigger in SQL to monitor the Events table for an update, i.e. when somebody authenticates with their access card. (I recently updated this to only activate the first time of that day that the user enters)

CREATE TRIGGER staffentry
ON [PAC Access Control Log].dbo.Events
FOR INSERT
AS

DECLARE @khid int
DECLARE @esid int
DECLARE @exsid int
DECLARE @todaycount int
DECLARE @useremail varchar(50)

SELECT @khid = KeyholderID FROM INSERTED
SELECT @esid = EventSourceID FROM INSERTED
SELECT @exsid = ExtraSourceID FROM INSERTED

IF (@esid = 16 and @exsid = 1)
BEGIN

	SET @useremail = (select email from [PAC Access Control].dbo.Keyholders WHERE KeyholderID = @khid)

	SET @todaycount = (select count(eventid) from [PAC Access Control Log].dbo.Events WHERE KeyholderID = @khid and EventSourceID = 16 and ExtraSourceID = 1 and DateTime > cast(getdate() as date))

	IF (@todaycount <= 1)
	BEGIN
		declare @cmd varchar(100)

		SET @cmd = 'powershell.exe "C:\Tools\Triggers\staff-entry.ps1" ' + @useremail;

		EXEC xp_cmdshell @cmd
	END
END

GO

This calls the PowerShell code to talk to theie API with an argument of the user’s email address which talks to the ScreenCloud API to force a takeover for a specific URL, an internal ASP.NET site written in C#.

The .net page checks the time of day and displays either Good Morning/Afternoon/Evening and also pulls their first name from AD and maps to their user photo from the directory.

$username=$args[0]
$uri = "https://signage-api.screen.cloud/api/v1/"
 
$user= "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$pass = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-x"
 
$pair = "${user}:${pass}"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$headers = @{ Authorization = $basicAuthValue }
 
#ScreenCloud Team ID
$teamid = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

$receptionscreenid = "xxxxxxxxxxxxxxxxxxxx"
 
$JSON = '
{
"url":"http://localwebserver/welcome.aspx?email='+$username+'",
"duration":"10000",
 "mime_type":"text/html"
}
'
$tempuri = $uri + "teams/" + $teamid + "/screens/" + $receptionscreenid + "/takeover"
 
#takeover
Invoke-WebRequest -body $JSON -uri $tempuri -Headers $headers -Method Put -ContentType "application/json"

I also threw in an applause MP3, because why not!

audio autoplay
  source src="audio/applause.mp3" type="audio/mpeg
/audio