Update-TsUser

1.1

Update-TsUser.psm1

                                function Update-TsUser {

<#

    .SYNOPSIS

        Resets Tableau Server users via local authentication, mostly due to either HR data not being in sync or for a one-time/urgent password reset. 

    .DESCRIPTION 

        Takes input from list (or the pipeline) and updates each user so they are reset in the event of a termination or the like. This function also removes the email since 

        a user could easily just go get their password reset via the homepage (provided they have a valid email).  

    .EXAMPLE

        Update-TsUser -TsUser 'vader@deathstar.com'

        'vader@deathstart.com' | Update-TsUser

#>

    [CmdletBinding()]

    param(

        [Parameter(Mandatory=$true

        ,ParameterSetName="User"

        ,Position=0

        ,ValueFromPipeline=$true

        )]

        [string[]]$TsUser

    )

Begin {

    $Kenobi = "your_secrets.json"

    #########################

    # query user info from db

    #########################

    $nsiteConn = "Driver=$($Kenobi.StarkillerDriver);Server=$($Kenobi.StarkillerServer); Port=$($Kenobi.StarkillerPort); Database=$($Kenobi.StarkillerDatabase); Uid=$($Kenobi.StarkillerUid); Pwd=$($Kenobi.StarkillerPwd);"

    $nsiteQuery = @"

select name,url_namespace from sites

"@

    $TsSites = Get-TsExtract -connectionString $nsiteConn -query $nsiteQuery | select -skip 1 -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors

    $TsSiteSQL = @()

    foreach($TsSite in $TsSites) {

        $TsSiteSQL += "when '$($TsSite.name)' then '$($TsSite.url_namespace)'`r"

        }

} #end Begin block

Process {

    #########################

    # UPDATE the users 

    #########################

    $updateUsers = ($PsUser | % {"`'$_`'"}) -join ","

    $UserQry = @"

SELECT DISTINCT

 u.name as "username"

 ,s.luid as "site-id"

, case s.name

    $($TsSiteSQL)

end as "site-name"

 ,uu.luid as "user-id"

 ,elUser.pw

FROM users uu

INNER JOIN _users u on u.system_user_id = uu.system_user_id

  INNER JOIN sites s on s.id = uu.site_id

    JOIN (

        select distinct

        z.username as "username"

        ,md5(random()::text) as "pw"

        from(

            select distinct

            pw.name as "username"

            from _users pw

        ) z

    ) elUser on elUser.username = u.name

WHERE lower(u.name) IN ($($updateUsers))

"@

    $UserConn = "Driver=$($Kenobi.StarkillerDriver);Server=$($Kenobi.StarkillerServer); Port=$($Kenobi.StarkillerPort); Database=$($Kenobi.StarkillerDatabase); Uid=$($Kenobi.StarkillerUid); Pwd=$($Kenobi.StarkillerPwd);"

    $Users=Get-TsExtract -connectionString $UserConn -query $UserQry | select -skip 1 -property username,site-id,site-name,user-id,pw

    foreach($TsUser in $Users) {

    $authtoken=$null

    $SignIn =[xml]@"

<tsRequest> 

 <credentials name="$($Kenobi.VaderUsername)" password="$($Kenobi.VaderPassword)" >

 <site contentUrl="$($TsUser.'site-name')" />

 </credentials> 

</tsRequest>

"@

    $tsResponse = irm -uri "$($Kenobi.VaderRESTSignin)" -Method Post -Body $SignIn

    $authtoken = $tsResponse.tsResponse.credentials.token

[xml]$tsPayload=@"

<tsRequest>

 <user     

    password="$($TsUser.pw)"

    email=""

    />

</tsRequest>

"@

    irm -Uri "$($Kenobi.VaderRESTSignin.Replace('auth/signin',''))sites/$($TsUser.'site-id')/users/$($TsUser.'user-id')" -Headers @{"X-Tableau-Auth"=$authtoken} -Method PUT -Body $tsPayload

    }

} #end process block

end {

    Invoke-RestMethod -Uri "$($Kenobi.VaderRESTSignin)".Replace('signin','signout') -Method POST -Headers @{"X-Tableau-Auth"=$authtoken}    

}

}