{"id":594,"date":"2023-07-06T15:38:17","date_gmt":"2023-07-06T14:38:17","guid":{"rendered":"https:\/\/blog.van-daag.nl\/?p=594"},"modified":"2023-08-22T08:17:26","modified_gmt":"2023-08-22T07:17:26","slug":"topdesk-cisco-ise-guest-wifi","status":"publish","type":"post","link":"https:\/\/van-daag.nl\/?p=594","title":{"rendered":"TopDesk – Cisco ISE Guest Wifi"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

First we need to get the ID group in Cisco ISE what to use and when.<\/p>\n\n\n\n

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

        $ISEserver = \"<IP ADDRESS>\"\n\n        [int]$ISEport = '9060'\n$Credential = Get-Credential\n        \n\n         $Uri = \"https:\/\/\" + $ISEserver +\":\" + $ISEport + \"\/ers\/config\/endpointgroup\/?size=100\"\n        $Headers = @{\n            'Accept'= \"application\/xml\"\n            'Content-Type'= \"application\/xml\"\n            'ERS-Media-Type'= \"identity.endpointgroup.1.1\"\n            }\n\n\n\n        $Response = Invoke-RestMethod -Uri $Uri -Method \"Get\" -Credential $Credential -SkipCertificateCheck -Headers $Headers \n        $Response.searchResult.resources.resource\n   \n<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Select the Corresponding ID And Save it for later Usage.<\/p>\n\n\n\n
Now get the Correct data from the Topdesk SQL database.
FYI We changed Topdesk to have the Wifi option there.<\/p>\n\n\n\n
Topdesk visitor registration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The SQL query for me was:
vrijeopzoek1 <\/em> is a long nr what is created when using it.<\/p>\n\n\n\n
“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’)”<\/em>

Below is the Full code that needs to be runned in PowerShell 7
Change all the < TEXT > <\/strong>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.<\/p>\n\n\n\n
Remove-Variable * -ErrorAction SilentlyContinue\n\n$SQLServer = \"<servername>\" \n$SQLDBName = \"<DBNAME>\" \n$User = \"Username\"\n$File = \"Passwordfile.txt\"\n$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, (Get-Content $File | ConvertTo-SecureString)\n$ISEserver = \"<ISESERVER IP>\"\n[int]$ISEport = '9060'\n        $Uri = \"https:\/\/\" + $ISEserver +\":\" + $ISEport + \"\/ers\/config\/guestuser\/\"\n        $Headers = @{\n            'Accept'= \"application\/vnd.com.cisco.ise.identity.guestuser.2.0+xml\"\n            'Content-Type'= \"application\/vnd.com.cisco.ise.identity.guestuser.2.0+xml\"\n            }\n$SmtpClient = New-Object system.net.mail.smtpClient \n$SmtpClient.host = \"<SMTP HOST>\" \n###### Password Generator\nfunction Get-RandomCharacters($length, $characters) {\n    $random = 1..$length | ForEach-Object { Get-Random -Maximum $characters.length }\n    $private:ofs=\"\"\n    return [String]$characters[$random]\n}\n\nfunction Scramble-String([string]$inputString){     \n    $characterArray = $inputString.ToCharArray()   \n    $scrambledStringArray = $characterArray | Get-Random -Count $characterArray.Length     \n    $outputString = -join $scrambledStringArray\n    return $outputString \n}\n\n$vandaagvan = (get-date).tostring(\u201cyyyy-MM-dd HH:mm:ss:fff\u201d)\n\n$vandaagvtot = (get-date).AddHours(+1).tostring(\u201cyyyy-MM-dd HH:mm:ss:fff\u201d)\n\nTry \n{ \n$SQLConnection = New-Object System.Data.SQLClient.SQLConnection \n$SQLConnection.ConnectionString =\"Server = $SQLServer; Database = $SQLDBName; User ID=<DB ID>; Password=<DB PASSWORD>\" \n$SQLConnection.Open() \n} \ncatch \n{ \n    [System.Windows.Forms.MessageBox]::Show(\"Failed to connect SQL Server:\")  \n} \n\n$SQLCommand = New-Object System.Data.SqlClient.SqlCommand \n$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')\"\n$SQLCommand.Connection = $SQLConnection \n\n$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter \n$SqlAdapter.SelectCommand = $SQLCommand                  \n$SQLDataset = New-Object System.Data.DataSet \n$SqlAdapter.fill($SQLDataset) | out-null\n \n$tablevalue = @() \n\nforeach ($data in $SQLDataset.tables[0]) \n{\n$unid = $data[0] \n$Naam = $data[1]\n$bezoekerbedrijf = $data[2]\n$verwachteaankomst = $data[3].tostring(\u201cMM\\\/dd\\\/yyyy HH:mm\u201d)\n$verwachteaankomstdag = $data[3].tostring(\u201cyyyy\/MM\/dd\u201d)\n$verwachtvertrek = $data[4].tostring(\u201cMM\\\/dd\\\/yyyy HH:mm\u201d)\n$verwachtvertrekdag = $data[4].AddHours( +1 ).tostring(\u201cyyyy\/MM\/dd\u201d)\n$aanmelderemail = $data[6]\n$aanmeldernaam = $data[7]\n$bezoekersTicket = $data[8]\n$wifi = $data[9]\n$aanmelderTel = $data[10]\n$aanmelderTel = $aanmelderTel -replace ' ',''\n\n$ts = New-TimeSpan -Start $verwachteaankomstdag -End $verwachtvertrekdag\n\n\n\n$TotalDays = $ts.Days +1 \n\n\n\n#### GUEST WIFI\nForeach ($account in $Wifi)\n{\n\nIf ($Wifi -eq 'E1532A44-2474-48E0-A540-XXXXXXX')\n{\n\n\nWrite-host GUEST\n$password = Get-RandomCharacters -length 2 -characters 'abcdefghiklmnoprstuvwxyz'\n$password += Get-RandomCharacters -length 2 -characters 'ABCDEFGHKLMNOPRSTUVWXYZ'\n$password += Get-RandomCharacters -length 4 -characters '1234567890'\n#$password += Get-RandomCharacters -length 1 -characters '!\"\u00a7$%&\/()=?}][{@#*+'\n$password = Scramble-String $password\n\n\n$username = Get-RandomCharacters -length 8 -characters 'abcdefghiklmnoprstuvwxyz'\n#$password += Get-RandomCharacters -length 1 -characters 'ABCDEFGHKLMNOPRSTUVWXYZ'\n#$username += Get-RandomCharacters -length 1 -characters '1234567890'\n#$password += Get-RandomCharacters -length 1 -characters '!\"\u00a7$%&\/()=?}][{@#*+'\n$username = Scramble-String $username\n\n\n        $Body = @\"\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<ns2:guestuser xmlns:ns2=\"identity.ers.ise.cisco.com\">\n<customFields>\n<\/customFields>\n<guestAccessInfo>\n<fromDate>$verwachteaankomst<\/fromDate>\n<location><INFO><\/location>\n<toDate>$verwachtvertrek<\/toDate>\n<validDays>$TotalDays<\/validDays>\n<\/guestAccessInfo>\n<guestInfo>\n<company>$bezoekerbedrijf<\/company>\n<emailAddress>abcfd@nvt.com<\/emailAddress>\n<firstName>$Naam<\/firstName>\n<lastName>na<\/lastName>\n<notificationLanguage>English<\/notificationLanguage>\n<password>$password<\/password>\n<phoneNumber>$aanmelderTel<\/phoneNumber>\n<smsServiceProvider>Global Default<\/smsServiceProvider>\n<userName>$username<\/userName>\n<\/guestInfo>\n<guestType>GUEST<\/guestType>\n<personBeingVisited>$aanmelderemail<\/personBeingVisited>\n<portalId><PORTAL ID><\/portalId>\n<reasonForVisit>Requestor $aanmeldernaam<\/reasonForVisit>\n<\/ns2:guestuser>\n\"@\n        $Response = Invoke-RestMethod -Uri $Uri -Method \"Post\" -Credential $Credential -SkipCertificateCheck -Headers $Headers -Body $Body\n        $Response.searchResult.resources.resource\n\n$GuestBody = @\"\n<style>\nh1, h5, th { text-align: center; font-family: Segoe UI; }\n<\/style>\n\n<html>\n    <body>\n    <span lang=NL style='font-size:10.0pt;line-height:106%;color:black'>\nGeachte $aanmeldernaam,<br>\n<br>\nHierbij de Wifi gegevens voor bezoeker $Naam <br>\n\n<br>\nWifi SSID: <b>GUEST<\/b><br>\nUsername: <b>$username<\/b><br>\nPassword: <b>$password<\/b><br>\n<br>\nDeze is geldig tot <b>$verwachtvertrek<\/b><br>\n\n\n<BR><BR>\nThis is an autogenerate message from server $env:computername<br>\n<br>\n<\/span>\n\"@\n\n##################\n## Mail config \n##################\n \n$GuestSubject = \"GUEST Wifi Account voor $Naam\"\n$GuestMessage = New-Object system.net.mail.mailmessage \n$GuestMessage.from = \"\"\n$GuestMessage.To.add(\"$aanmelderemail\")  \n$GuestMessage.To.cc(\"\") \n$GuestMessage.Subject = $GuestSubject  \n$GuestMessage.IsBodyHtml = 1\n$GuestMessage.Body = $GuestBody\n \n\n$SmtpClient.Send($GuestMessage)\n\n}\n\n\n}\n}<\/code><\/pre>\n\n\n\n
This is an example how the requestor gets the emails.
<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[53,50,11,49],"tags":[],"class_list":["post-594","post","type-post","status-publish","format-standard","hentry","category-blogs","category-cisco-ise","category-powershell-script","category-topdesk"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/posts\/594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/van-daag.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=594"}],"version-history":[{"count":7,"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions"}],"predecessor-version":[{"id":634,"href":"https:\/\/van-daag.nl\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions\/634"}],"wp:attachment":[{"href":"https:\/\/van-daag.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/van-daag.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/van-daag.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}