Uncategorized – Page 26 – ControllingTheInter.net

Cleaning up Duplicate SQL Rows

So recently I’ve been tasking with cleaning up an SQL database for a developing project I’ve been working on for EPIC and User Provisioning. I had two end up merging two table sets together to get the data I needed, However, duplicate rows impact the process, So I used the following commands below to filter …

Continue reading “Cleaning up Duplicate SQL Rows”

Powershell EWS Exchange Calendar Reminder, Email and Text to phone.

So, I’m once in a bluemoon I end up with a 8AM surprise calendar event when I am out of the office the day before. I threw this little gem together using powershell that will send me an email and a text message to my phone throughout the day if one of these meetings appear …

Continue reading “Powershell EWS Exchange Calendar Reminder, Email and Text to phone.”

Alerting on Events with no Reminder.

# https://download.microsoft.com/download/8/9/9/899EEF2C-55ED-4C66-9613-EE808FCF861C/EwsManagedApi.msi
Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host #You cannot use this with Param
# Declare Variables
$EWSDLL = "C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll" $MBX = "YourEmail@Domain.org" $EWSURL = "https://FQDN.Domain.Com/EWS/Exchange.asmx" $StartDate = (Get-Date) $EndDate = [datetime]::ParseExact("23:59","HH:mm",$null)
# Binding of the calendar of the Mailbox and EWS
Import-Module -Name $EWSDLL $mailboxname = $MBX $service = new-object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.Exchangeversion]::exchange2010) $service.Url = new-object System.Uri($EWSURL) $userName="SamAccountIDWithoutDomain" $password="Your Email password"
# Uncomment to use Auth in lieu of passthrough
# $service.Credentials = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials -ArgumentList $userName, $password
$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName) $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)
$Recurring = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Appointment, 0x8223,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Boolean); $psPropset= new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$psPropset.Add($Recurring) $psPropset.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;
$RptCollection = @()
$AppointmentState = @{0 = "None" ; 1 = "Meeting" ; 2 = "Received" ;4 = "Canceled" ; }
# Define the calendar view
$CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate, $EndDate, 1000)
$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView) if($fiItems.Items.Count -gt 0){ $type = ("System.Collections.Generic.List"+'`'+"1") -as "Type" $type = $type.MakeGenericType("Microsoft.Exchange.WebServices.Data.Item" -as "Type") $ItemColl = [Activator]::CreateInstance($type) foreach($Item in $fiItems.Items){ $ItemColl.Add($Item) } [Void]$service.LoadPropertiesForItems($ItemColl,$psPropset)
}
$min = Get-Date '08:00' $max = Get-Date '08:29'
foreach($Item in $fiItems.Items) {
$rptObj = "" | Select StartTime,EndTime,Duration,Type,Subject,Location,Organizer,Attendees,AppointmentState,Notes,HasAttachments,IsReminderSet,ReminderDueBy,ReminderMinutesBeforeStart $rptObj.StartTime = $Item.Start
$rptObj.EndTime = $Item.End
$rptObj.Duration = $Item.Duration $rptObj.Subject = $Item.Subject
$rptObj.Type = $Item.AppointmentType $rptObj.Location = $Item.Location $rptObj.Organizer = $Item.Organizer.Address $rptObj.HasAttachments = $Item.HasAttachments $rptObj.IsReminderSet = $Item.IsReminderSet $rptObj.ReminderDueBy = $Item.ReminderDueBy $rptObj.ReminderMinutesBeforeStart = $Item.ReminderMinutesBeforeStart $aptStat = ""; $AppointmentState.Keys | where { $_ -band $Item.AppointmentState } | foreach { $aptStat += $AppointmentState.Get_Item($_) + " "} $rptObj.AppointmentState = $aptStat $RptCollection += $rptObj foreach($attendee in $Item.RequiredAttendees){ $atn = $attendee.Address + "; "
$rptObj.Attendees += $atn } foreach($attendee in $Item.OptionalAttendees){ $atn = $attendee.Address + "; "
$rptObj.Attendees += $atn } foreach($attendee in $Item.Resources){ $atn = $attendee.Address + "; "
$rptObj.Resources += $atn } $rptObj.Notes = $Item.Body.Text #Display on the screen #"Start: " + $Item.Start
#"Subject: " + $Item.Subject
    if ($rptObj.ReminderMinutesBeforeStart = 0 -or $Item.IsReminderSet -eq $false) {
        $message = New-Object Microsoft.Exchange.WebServices.Data.EmailMessage $Service
        $message.Subject = "REMINDER SET TO ZERO!! " + $Item.Subject
        $message.Body = new-object Microsoft.Exchange.WebServices.Data.MessageBody([Microsoft.Exchange.WebServices.Data.BodyType]::Text, ($rptObj | Format-List | Out-String) -replace '\n(?=")','&lt;br/&gt;')
        foreach ($file in $Attachment) {
            $null = $message.Attachments.AddFileAttachment($file)
        }
        foreach ($recipient in $To) {
            $null = $message.ToRecipients.Add(
                $recipient
            )
        }
        foreach ($recipient in $Cc) {
            $null = $message.CcRecipients.Add(
                $recipient
            )
        }
        foreach ($recipient in $Bcc) {
            $null = $message.BccRecipients.Add(
                $recipient
            )
        }
        $message.ToRecipients.Add("ToWhomeGetsTheReminder@Domain.Com")
            $message.ToRecipients.Add("9998887777@vtext.com")
        $message.SendAndSaveCopy()
        #$message
        #$message.ExtendedProperties  | Format-List | Out-String
        #Write-Host $rptObj | Format-List | Out-String
    }
    else
    {
        $Item.Subject
        $Item.IsReminderSet
        $Item.ReminderDueBy
        $Item.ReminderMinutesBeforeStart
        Write-Host "=-=-=-=-=-="
    }
}
# Export to a CSVFile
# $RptCollection | Export-Csv -NoTypeInformation -Path "c:\$MailboxName-CalendarCSV.csv"
# pause

# https://download.microsoft.com/download/8/9/9/899EEF2C-55ED-4C66-9613-EE808FCF861C/EwsManagedApi.msi

Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host #You cannot use this with Param

# Declare Variables

$EWSDLL = "C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll" $MBX = "YourEmail@Domain.org" $EWSURL = "https://FQDN.Domain.Com/EWS/Exchange.asmx" $StartDate = (Get-Date) $EndDate = [datetime]::ParseExact("23:59","HH:mm",$null)

# Binding of the calendar of the Mailbox and EWS

Import-Module -Name $EWSDLL $mailboxname = $MBX $service = new-object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.Exchangeversion]::exchange2010) $service.Url = new-object System.Uri($EWSURL) $userName="SamAccountIDWithoutDomain" $password="Your Email password"

# Uncomment to use Auth in lieu of passthrough

# $service.Credentials = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials -ArgumentList $userName, $password

$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName) $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)

$Recurring = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Appointment, 0x8223,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Boolean); $psPropset= new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)

$psPropset.Add($Recurring) $psPropset.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;

$RptCollection = @()

$AppointmentState = @{0 = "None" ; 1 = "Meeting" ; 2 = "Received" ;4 = "Canceled" ; }

# Define the calendar view

$CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate, $EndDate, 1000)

$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView) if($fiItems.Items.Count -gt 0){ $type = ("System.Collections.Generic.List"+'`'+"1") -as "Type" $type = $type.MakeGenericType("Microsoft.Exchange.WebServices.Data.Item" -as "Type") $ItemColl = [Activator]::CreateInstance($type) foreach($Item in $fiItems.Items){ $ItemColl.Add($Item) } [Void]$service.LoadPropertiesForItems($ItemColl,$psPropset)

}

$min = Get-Date '08:00' $max = Get-Date '08:29'

foreach($Item in $fiItems.Items) {

$rptObj = "" | Select StartTime,EndTime,Duration,Type,Subject,Location,Organizer,Attendees,AppointmentState,Notes,HasAttachments,IsReminderSet,ReminderDueBy,ReminderMinutesBeforeStart $rptObj.StartTime = $Item.Start

$rptObj.EndTime = $Item.End

$rptObj.Duration = $Item.Duration $rptObj.Subject = $Item.Subject

$rptObj.Type = $Item.AppointmentType $rptObj.Location = $Item.Location $rptObj.Organizer = $Item.Organizer.Address $rptObj.HasAttachments = $Item.HasAttachments $rptObj.IsReminderSet = $Item.IsReminderSet $rptObj.ReminderDueBy = $Item.ReminderDueBy $rptObj.ReminderMinutesBeforeStart = $Item.ReminderMinutesBeforeStart $aptStat = ""; $AppointmentState.Keys | where { $_ -band $Item.AppointmentState } | foreach { $aptStat += $AppointmentState.Get_Item($_) + " "} $rptObj.AppointmentState = $aptStat $RptCollection += $rptObj foreach($attendee in $Item.RequiredAttendees){ $atn = $attendee.Address + "; "

$rptObj.Attendees += $atn } foreach($attendee in $Item.OptionalAttendees){ $atn = $attendee.Address + "; "

$rptObj.Attendees += $atn } foreach($attendee in $Item.Resources){ $atn = $attendee.Address + "; "

$rptObj.Resources += $atn } $rptObj.Notes = $Item.Body.Text #Display on the screen #"Start: " + $Item.Start

#"Subject: " + $Item.Subject

if ($rptObj.ReminderMinutesBeforeStart = 0 -or $Item.IsReminderSet -eq $false) {

$message = New-Object Microsoft.Exchange.WebServices.Data.EmailMessage $Service

$message.Subject = "REMINDER SET TO ZERO!! " + $Item.Subject

$message.Body = new-object Microsoft.Exchange.WebServices.Data.MessageBody([Microsoft.Exchange.WebServices.Data.BodyType]::Text, ($rptObj | Format-List | Out-String) -replace '\n(?=")','<br/>')

foreach ($file in $Attachment) {

$null = $message.Attachments.AddFileAttachment($file)

}

foreach ($recipient in $To) {

$null = $message.ToRecipients.Add(

$recipient

)

}

foreach ($recipient in $Cc) {

$null = $message.CcRecipients.Add(

$recipient

)

}

foreach ($recipient in $Bcc) {

$null = $message.BccRecipients.Add(

$recipient

)

}

$message.ToRecipients.Add("ToWhomeGetsTheReminder@Domain.Com")

$message.ToRecipients.Add("9998887777@vtext.com")

$message.SendAndSaveCopy()

#$message

#$message.ExtendedProperties | Format-List | Out-String

#Write-Host $rptObj | Format-List | Out-String

}

else

{

$Item.Subject

$Item.IsReminderSet

$Item.ReminderDueBy

$Item.ReminderMinutesBeforeStart

Write-Host "=-=-=-=-=-="

}

# Export to a CSVFile

# $RptCollection | Export-Csv -NoTypeInformation -Path "c:\$MailboxName-CalendarCSV.csv"

# pause

Killing LANDesk ISSUser and Access Denied.

So in the last day, A buddy of mine pointed out an issue with LanDesk ISSUR. I got to a stopping point but basically when you try to Kill the process while it is in a hung state it. So my thoughts were the ACL in memory for the process was adjusted to prevent administrators …

Continue reading “Killing LANDesk ISSUser and Access Denied.”

Epic Powershell activation and deactivation of users

Checking the status of active users and disabling them if required. REST is used to create users

    #Param must be the first executable line
Param(
    [string] $ContactComment = "Inital Entry",
    [string] $SystemLoginID = "12345",
    [string] $IsActive = "0",
    [string] $Name = "HALL, NICHOLAS A",
    [string] $DefaultTemplateID = "T1201",
    [string] $AppliedTemplateID = "T1201",
    [string] $LinkedTemplateID1 = "T1201",
    [string] $LinkedTemplateID2 = "T2100303",
    [string] $UserSubtemplateIDs1 = "T063",
    [string] $UserSubtemplateIDs2 = "ST5083",
    [string] $CustomUserDictionaries = "\\\\epic-server\\User_Dictionaries\\12345dictionary.tlx"
 )
#Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host
#https://vm9008.info.sys/Interconnect-REL-EDI/DeveloperView/Main.aspx?clientid=1
$Request = '{"LinkedTemplatesConfig":{"DefaultTemplateID":{"ID":"' + $DefaultTemplateID + '","Type":"external"},"AppliedTemplateID":{"ID":"' + "$AppliedTemplateID" + '","Type":"external"},"AvailableLinkableTemplates":[{"StartDate":"","EndDate":"","LoginTypes":[],"LinkedTemplateID":{"ID":"' + "$LinkedTemplateID1" + '","Type":"external"}},{"StartDate":"","EndDate":"","LoginTypes":[],"LinkedTemplateID":{"ID":"' + "$LinkedTemplateID2" + '","Type":"external"}}]},"UserSubtemplateIDs":[{"Index":"1","Identifier":{"ID":"' + "$UserSubtemplateIDs1" + '","Type":"external"}},{"Index":"2","Identifier":{"ID":"' + "$UserSubtemplateIDs2" + '","Type":"external"}}],"CustomUserDictionaries":[{"Index":"1","Value":"' + $CustomUserDictionaries + '"}],"LinkedProviderID":{"ID":"","Type":""},"IdentityIDs":[],"ExternalIdentifiers":[{"Identifier":"e12345","IdentifierType":"EMC Mckesson Document Imaging","IsActive":"true"}]}'
try
{
    $RestResults = Invoke-RestMethod -Method Put $url -contentType "application/json" -Body $Request
    foreach($id in $RestResults.UserIDs)
    {
        Write-Host $id.ID - $id.Type
    }
    if ($RestResults.UserIDs.Count -eq 4)
    {
        Write-Host "SUCCESS!"
    }
    $RestResults.Messages
}
catch
{
    if ($_.exception.Response.StatusCode -eq "BadRequest")
    {
        #$_.exception.Response.StatusDescription
        if ($_.exception.Response.StatusDescription -match '^.*System login ID must be unique\.')
        {
            Write-Host "Already Exist!"
        }
        else
        {
            Write-Host "BadRequest: " $_.exception.Response.StatusDescription
        }
    }
    else
    {
        $_.Exception | gm
        $_.Exception | Select-Object -Property *
        $_.exception.Response.StatusCode
    }
    #$_.exception.message
    #$_.exception.InnerException
    #$_.Exception.ItemName
}

#Param must be the first executable line

Param(

[string] $ContactComment = "Inital Entry",

[string] $SystemLoginID = "12345",

[string] $IsActive = "0",

[string] $Name = "HALL, NICHOLAS A",

[string] $DefaultTemplateID = "T1201",

[string] $AppliedTemplateID = "T1201",

[string] $LinkedTemplateID1 = "T1201",

[string] $LinkedTemplateID2 = "T2100303",

[string] $UserSubtemplateIDs1 = "T063",

[string] $UserSubtemplateIDs2 = "ST5083",

[string] $CustomUserDictionaries = "\\\\epic-server\\User_Dictionaries\\12345dictionary.tlx"

)

#Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host

#https://vm9008.info.sys/Interconnect-REL-EDI/DeveloperView/Main.aspx?clientid=1

$Request = '{"LinkedTemplatesConfig":{"DefaultTemplateID":{"ID":"' + $DefaultTemplateID + '","Type":"external"},"AppliedTemplateID":{"ID":"' + "$AppliedTemplateID" + '","Type":"external"},"AvailableLinkableTemplates":[{"StartDate":"","EndDate":"","LoginTypes":[],"LinkedTemplateID":{"ID":"' + "$LinkedTemplateID1" + '","Type":"external"}},{"StartDate":"","EndDate":"","LoginTypes":[],"LinkedTemplateID":{"ID":"' + "$LinkedTemplateID2" + '","Type":"external"}}]},"UserSubtemplateIDs":[{"Index":"1","Identifier":{"ID":"' + "$UserSubtemplateIDs1" + '","Type":"external"}},{"Index":"2","Identifier":{"ID":"' + "$UserSubtemplateIDs2" + '","Type":"external"}}],"CustomUserDictionaries":[{"Index":"1","Value":"' + $CustomUserDictionaries + '"}],"LinkedProviderID":{"ID":"","Type":""},"IdentityIDs":[],"ExternalIdentifiers":[{"Identifier":"e12345","IdentifierType":"EMC Mckesson Document Imaging","IsActive":"true"}]}'

try

{

$RestResults = Invoke-RestMethod -Method Put $url -contentType "application/json" -Body $Request

foreach($id in $RestResults.UserIDs)

{

Write-Host $id.ID - $id.Type

}

if ($RestResults.UserIDs.Count -eq 4)

{

Write-Host "SUCCESS!"

}

$RestResults.Messages

}

catch

{

if ($_.exception.Response.StatusCode -eq "BadRequest")

{

#$_.exception.Response.StatusDescription

if ($_.exception.Response.StatusDescription -match '^.*System login ID must be unique\.')

{

Write-Host "Already Exist!"

}

else

{

Write-Host "BadRequest: " $_.exception.Response.StatusDescription

}

else

{

$_.Exception | gm

$_.Exception | Select-Object -Property *

$_.exception.Response.StatusCode

}

#$_.exception.message

#$_.exception.InnerException

#$_.Exception.ItemName

}

WSDL is used to Terminate/SoftDelete / Inactivate users

Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host
$where = 'https://EpicServer/Interconnect-REL-EDI/wcf/Epic.Security.GeneratedServices/PersonnelManagement.svc'
$ws = New-WebServiceProxy -uri $where -UseDefaultCredential
$namespace = $ws.getType().namespace
foreach($line in get-content "c:\termList.txt")
{
    try
    {
        $recipients = $line -split [RegEx]::Escape("|")
        $samAccountName = $recipients[1].trim()
        [Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementViewUserResponse] $ViewUserResponse = $ws.ViewUser($samAccountName, "SystemLogin", $null, $null, $null, $false, $null)
        if ($ViewUserResponse.IsActive)
        {
            Write-Host "Is user active in REL:" $samAccountName
        }
    }
    catch
    {
    }
}
pause
[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementInactivateUserResponse] $response = $ws.InactivateUser($samAccountName, "SystemLogin", $null, $null, $null)
Write-Host "Messages:" $response.Messages
[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementViewUserResponse] $ViewUserResponse = $ws.ViewUser($samAccountName, "SystemLogin", $null, $null, $null, $false, $null)
Write-Host "Is user active:" $ViewUserResponse.IsActive
pause
[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementActivateUserResponse] $ActivateResponse = $ws.ActivateUser($samAccountName, "SystemLogin", $null, $null, $null)
Write-Host "Messages:" $ActivateResponse.Messages
[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementViewUserResponse] $ViewUserResponse = $ws.ViewUser($samAccountName, "SystemLogin", $null, $null, $null, $false, $null)
Write-Host "Is User Active:" $ViewUserResponse.IsActive

Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host

$where = 'https://EpicServer/Interconnect-REL-EDI/wcf/Epic.Security.GeneratedServices/PersonnelManagement.svc'

$ws = New-WebServiceProxy -uri $where -UseDefaultCredential

$namespace = $ws.getType().namespace

foreach($line in get-content "c:\termList.txt")

{

try

{

$recipients = $line -split [RegEx]::Escape("|")

$samAccountName = $recipients[1].trim()

[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementViewUserResponse] $ViewUserResponse = $ws.ViewUser($samAccountName, "SystemLogin", $null, $null, $null, $false, $null)

if ($ViewUserResponse.IsActive)

{

Write-Host "Is user active in REL:" $samAccountName

}

catch

{

}

pause

[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementInactivateUserResponse] $response = $ws.InactivateUser($samAccountName, "SystemLogin", $null, $null, $null)

Write-Host "Messages:" $response.Messages

Write-Host "Is user active:" $ViewUserResponse.IsActive

pause

[Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy61rvices_PersonnelManagement_svc.PersonnelManagementActivateUserResponse] $ActivateResponse = $ws.ActivateUser($samAccountName, "SystemLogin", $null, $null, $null)

Write-Host "Messages:" $ActivateResponse.Messages

Write-Host "Is User Active:" $ViewUserResponse.IsActive