TopDesk – Cisco ISE Guest Wifi

We just replaced our network to Cisco with Cisco ISE.
We always had a Visitors registration in Topdesk with Wifi code request. Now I needed to make it happen in Cisco ISE.

First we need to get the ID group in Cisco ISE what to use and when.

The script below needs to be running in PowerShell 7 -SkipCertificateCheck
If you are running ISE with Certificate you can remove this option and change <IP ADDESS> to DNS name.

        $ISEserver = "<IP ADDRESS>"

        [int]$ISEport = '9060'
$Credential = Get-Credential
        

         $Uri = "https://" + $ISEserver +":" + $ISEport + "/ers/config/endpointgroup/?size=100"
        $Headers = @{
            'Accept'= "application/xml"
            'Content-Type'= "application/xml"
            'ERS-Media-Type'= "identity.endpointgroup.1.1"
            }



        $Response = Invoke-RestMethod -Uri $Uri -Method "Get" -Credential $Credential -SkipCertificateCheck -Headers $Headers 
        $Response.searchResult.resources.resource
   

Select the Corresponding ID And Save it for later Usage.

Now get the Correct data from the Topdesk SQL database.
FYI We changed Topdesk to have the Wifi option there.

Topdesk visitor registration:

The SQL query for me was:
vrijeopzoek1 is a long nr what is created when using it.

“SELECT unid,naam,bezoekerbedrijf,verwachteaankomst,verwachtvertrek,identiteitsbewijsid,aanmelderemail,aanmeldernaam,nummer,vrijeopzoek1,aanmeldertelefoon FROM [TOPdesk].[dbo].[bezoeker] where verwachteaankomst >= ‘$($vandaagvan)’ and verwachteaankomst <= ‘$($vandaagvtot)’ and vrijeopzoek1 in (‘E1532A44-2474-48E0-A540-XXXXXXX’)”

Below is the Full code that needs to be runned in PowerShell 7
Change all the < TEXT > to the correct output. Edit the HTML Body for nice looking email.
At the end, put this script in Task Scheduler every hour and now automatic Wifi accounts are created.

Remove-Variable * -ErrorAction SilentlyContinue

$SQLServer = "<servername>" 
$SQLDBName = "<DBNAME>" 
$User = "Username"
$File = "Passwordfile.txt"
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, (Get-Content $File | ConvertTo-SecureString)
$ISEserver = "<ISESERVER IP>"
[int]$ISEport = '9060'
        $Uri = "https://" + $ISEserver +":" + $ISEport + "/ers/config/guestuser/"
        $Headers = @{
            'Accept'= "application/vnd.com.cisco.ise.identity.guestuser.2.0+xml"
            'Content-Type'= "application/vnd.com.cisco.ise.identity.guestuser.2.0+xml"
            }
$SmtpClient = New-Object system.net.mail.smtpClient 
$SmtpClient.host = "<SMTP HOST>" 
###### Password Generator
function Get-RandomCharacters($length, $characters) {
    $random = 1..$length | ForEach-Object { Get-Random -Maximum $characters.length }
    $private:ofs=""
    return [String]$characters[$random]
}

function Scramble-String([string]$inputString){     
    $characterArray = $inputString.ToCharArray()   
    $scrambledStringArray = $characterArray | Get-Random -Count $characterArray.Length     
    $outputString = -join $scrambledStringArray
    return $outputString 
}

$vandaagvan = (get-date).tostring(“yyyy-MM-dd HH:mm:ss:fff”)

$vandaagvtot = (get-date).AddHours(+1).tostring(“yyyy-MM-dd HH:mm:ss:fff”)

Try 
{ 
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection 
$SQLConnection.ConnectionString ="Server = $SQLServer; Database = $SQLDBName; User ID=<DB ID>; Password=<DB PASSWORD>" 
$SQLConnection.Open() 
} 
catch 
{ 
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")  
} 

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand 
$SQLCommand.CommandText = "SELECT unid,naam,bezoekerbedrijf,verwachteaankomst,verwachtvertrek,identiteitsbewijsid,aanmelderemail,aanmeldernaam,nummer,vrijeopzoek1,aanmeldertelefoon FROM [TOPdesk].[dbo].[bezoeker] where verwachteaankomst >= '$($vandaagvan)' and verwachteaankomst <= '$($vandaagvtot)' and vrijeopzoek1 in ('E1532A44-2474-48E0-A540-XXXXXX')"
$SQLCommand.Connection = $SQLConnection 

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SQLCommand                  
$SQLDataset = New-Object System.Data.DataSet 
$SqlAdapter.fill($SQLDataset) | out-null
 
$tablevalue = @() 

foreach ($data in $SQLDataset.tables[0]) 
{
$unid = $data[0] 
$Naam = $data[1]
$bezoekerbedrijf = $data[2]
$verwachteaankomst = $data[3].tostring(“MM\/dd\/yyyy HH:mm”)
$verwachteaankomstdag = $data[3].tostring(“yyyy/MM/dd”)
$verwachtvertrek = $data[4].tostring(“MM\/dd\/yyyy HH:mm”)
$verwachtvertrekdag = $data[4].AddHours( +1 ).tostring(“yyyy/MM/dd”)
$aanmelderemail = $data[6]
$aanmeldernaam = $data[7]
$bezoekersTicket = $data[8]
$wifi = $data[9]
$aanmelderTel = $data[10]
$aanmelderTel = $aanmelderTel -replace ' ',''

$ts = New-TimeSpan -Start $verwachteaankomstdag -End $verwachtvertrekdag



$TotalDays = $ts.Days +1 



#### GUEST WIFI
Foreach ($account in $Wifi)
{

If ($Wifi -eq 'E1532A44-2474-48E0-A540-XXXXXXX')
{


Write-host GUEST
$password = Get-RandomCharacters -length 2 -characters 'abcdefghiklmnoprstuvwxyz'
$password += Get-RandomCharacters -length 2 -characters 'ABCDEFGHKLMNOPRSTUVWXYZ'
$password += Get-RandomCharacters -length 4 -characters '1234567890'
#$password += Get-RandomCharacters -length 1 -characters '!"§$%&/()=?}][{@#*+'
$password = Scramble-String $password


$username = Get-RandomCharacters -length 8 -characters 'abcdefghiklmnoprstuvwxyz'
#$password += Get-RandomCharacters -length 1 -characters 'ABCDEFGHKLMNOPRSTUVWXYZ'
#$username += Get-RandomCharacters -length 1 -characters '1234567890'
#$password += Get-RandomCharacters -length 1 -characters '!"§$%&/()=?}][{@#*+'
$username = Scramble-String $username


        $Body = @"
<?xml version="1.0" encoding="UTF-8"?>
<ns2:guestuser xmlns:ns2="identity.ers.ise.cisco.com">
<customFields>
</customFields>
<guestAccessInfo>
<fromDate>$verwachteaankomst</fromDate>
<location><INFO></location>
<toDate>$verwachtvertrek</toDate>
<validDays>$TotalDays</validDays>
</guestAccessInfo>
<guestInfo>
<company>$bezoekerbedrijf</company>
<emailAddress>abcfd@nvt.com</emailAddress>
<firstName>$Naam</firstName>
<lastName>na</lastName>
<notificationLanguage>English</notificationLanguage>
<password>$password</password>
<phoneNumber>$aanmelderTel</phoneNumber>
<smsServiceProvider>Global Default</smsServiceProvider>
<userName>$username</userName>
</guestInfo>
<guestType>GUEST</guestType>
<personBeingVisited>$aanmelderemail</personBeingVisited>
<portalId><PORTAL ID></portalId>
<reasonForVisit>Requestor $aanmeldernaam</reasonForVisit>
</ns2:guestuser>
"@
        $Response = Invoke-RestMethod -Uri $Uri -Method "Post" -Credential $Credential -SkipCertificateCheck -Headers $Headers -Body $Body
        $Response.searchResult.resources.resource

$GuestBody = @"
<style>
h1, h5, th { text-align: center; font-family: Segoe UI; }
</style>

<html>
    <body>
    <span lang=NL style='font-size:10.0pt;line-height:106%;color:black'>
Geachte $aanmeldernaam,<br>
<br>
Hierbij de Wifi gegevens voor bezoeker $Naam <br>

<br>
Wifi SSID: <b>GUEST</b><br>
Username: <b>$username</b><br>
Password: <b>$password</b><br>
<br>
Deze is geldig tot <b>$verwachtvertrek</b><br>


<BR><BR>
This is an autogenerate message from server $env:computername<br>
<br>
</span>
"@

##################
## Mail config 
##################
 
$GuestSubject = "GUEST Wifi Account voor $Naam"
$GuestMessage = New-Object system.net.mail.mailmessage 
$GuestMessage.from = ""
$GuestMessage.To.add("$aanmelderemail")  
$GuestMessage.To.cc("") 
$GuestMessage.Subject = $GuestSubject  
$GuestMessage.IsBodyHtml = 1
$GuestMessage.Body = $GuestBody
 

$SmtpClient.Send($GuestMessage)

}


}
}

This is an example how the requestor gets the emails.