#Export0 discussing

I have the below Microsoft SQL code which basically creates two separate tables (one with index operations and one with ECMOs) and then joins on the ID between them where the date of the the ECMO is after the date of the Index Operation to get a list of hospitalizationIDs that are post-index operation. How do I translate this to Tableau? I started trying to make a custom SQL query but was unsure how to do that and then was thinking a series of calculated fields? Not exactly sure where to start.

--create index and ECMO table

select distinct HospitalizationID, IndexOperation, SurgDt

INTO ⌗index

from ⌗export

where IndexOperation=1

group by hospitalizationID, IndexOperation, SurgDt

select distinct HospitalizationID, Mechanical_Support, IndexOperation, SurgDt

INTO ⌗ECMO

from ⌗export

where IndexOperation=0 and Mechanical_Support='ECMO'

group by hospitalizationID, Mechanical_Support, IndexOperation, SurgDt

--post index operation/surgery ECMO during same hospitalization

select distinct E.HospitalizationID, I.PrimaryPx

from ⌗ECMO E

LEFT JOIN ⌗index I

on I.hospitalizationID=E.HospitalizationID

where E.SurgDt > I.SurgDt 

How to update / create users coming from Active Directory in Tableau?🙂

I have been asked this question many times in last few months in many forums, how to automatically inactive the users in Tableau server if they are getting inactive in Active Directory or how to create Users in Tableau Server in case any users are newly on boarded in AD users groups available in Tableau, hence writing this solution here

I will not write full code here, it will give you fair enough idea how to achieve the other things

Requirements

1. Active Directory details
2. PowerShell
3. Obdc Driver to reach to Tableau Postgres data base
4. Make sure Tableau database should be enabled
5. Administrative privileges on the Tableau server

Script:

Clear

⌗Get users from ad server

Get-ADUser -Filter * -Properties Name, SamAccountName, EmailAddress, UserPrincipalName, Enabled, Company | Select-Object Name, SamAccountName,UserPrincipalName, EmailAddress, Enabled, Company | where {$_.company -eq "ABHICL"} | Export-Csv "E:\Sandip_SQL\ADUsers_group.csv"

⌗export all the users from Tableau Server

⌗importing my tableau server postgres details kept in csv file

$PostgreSQL_Connection_String = import-csv -path "E:\Sandip_SQL\PostgreSQL_Connection_String.csv"

foreach ($Row in $PostgreSQL_Connection_String)

{

$connectionString = "Driver=$($Row.Driver);Server=$($Row.Server);Port=$($Row.Port);Database=$($Row.Database);Uid=$($Row.User);Pwd=$($Row.Password);"

}

$connection = New-Object System.Data.Odbc.OdbcConnection

$connection.ConnectionString = $connectionString

$connection.Open()

$query = "SELECT name AS UserID,friendly_name AS UserName,licensing_role_name AS Role FROM public._users where name not in ('_system','guest')”

$command = $connection.CreateCommand()

$command.CommandText = $query

$command.CommandTimeout = 0 

$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”

$table.Load($result)

$table |Export-Csv -path "E:\Sandip_SQL\tableauusers.csv" ⌗ROWS_INSERTED

$connection.Close()

⌗importing both files ad users and Tableau users

$adusers = import-csv -path "E:\Sandip_SQL\ADUsers_group.csv"

$tabusers = import-csv -path "E:\Sandip_SQL\tableauusers.csv"

#I will first check the records only if exist in both csv and then will mark them unlicensed in Tableau server if their status is not active in AD and License type is still allocated to user

⌗matching counter and monitoring of time taken by the script

$matchcounter

$start = [system.datetime]::Now

# create new CSV file

foreach ($order1 in $adusers){

  $matched = $false

  foreach ($order2 in $tabusers){

    $obj = "" | select "SamAccountName","Ad_Status","Tableau_Role"

    if(($order1.SamAccountName ) -eq $order2.userid ){

      $matchCounter++

      $matched = $true

      $obj.SamAccountName = $order1.SamAccountName

      $obj.Ad_Status = $order1.Enabled

      $obj.Tableau_Role = $order2.role

      Write-Host "Match Found Orders " "$matchCounter"

      $obj | Export-Csv -Path E:\Sandip_SQL\AD_Tableau_Users_Match.csv -Append -NoTypeInformation

    }

  }

}

$end = [system.datetime]::Now

$resultTime = $end - $start

Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."

$updateuser = import-csv -path "E:\Sandip_SQL\AD_Tableau_Users_Match.csv"

# Setup to connect

$server = "your tableau server url"

$s = Invoke-RestMethod -Uri $server/api/3.7/serverinfo -Method get ⌗works on server version 10.1 and later

$api = $s.tsResponse.serverInfo.restApiVersion #2020.1 server

echo $api

⌗make sure you should use either site admin or administrator account only to perform below activity

$username = “your tableau user”

$password = “your tableau password”

$sitelogin = "" ⌗keep site as blank in case of default site

# generate body for sign in

$signin_body = (’<tsRequest>

 <credentials name=“’ + $username + ’” password=“’+ $password + ’” >

  <site contentUrl="'+$sitelogin +'" />

 </credentials>

</tsRequest>’)

$response = Invoke-RestMethod -Uri $server/api/$api/auth/signin -Body $signin_body -Method post

# save the auth token, site id and my user id

$authToken = $response.tsResponse.credentials.token

$siteID = $response.tsResponse.credentials.site.id

$myUserID = $response.tsResponse.credentials.user.id

$siteURL = $response.tsResponse.credentials.site.contentUrl

echo $siteID

# set up header fields with auth token

$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]”

# add X-Tableau-Auth header with our auth token

$headers.Add(“X-Tableau-Auth”, $authToken)

⌗tests whether logged in user is an Administrator (site or server)

$loginUserid = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$myUserID -Headers $headers -Method Get

$admin = $loginUserid.tsResponse.user.siteRole -like "*Administrator"

if($admin)

{

foreach ($line in $updateuser )

  {

    if($line.Ad_Status -eq "FALSE" -and $line.Tableau_Role -ne "Unlicensed")

      {

        $userid = $line.SamAccountName

        $SiteRole = "Unlicensed"

        ⌗user update body

        $userupdate = ( '<tsRequest> <user siteRole="'+$SiteRole+'" /> </tsRequest>' )

        $response = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$userid -Headers $headers -Method Put -Body $userupdate

        write-host $response.tsResponse.user

      }

  }

}

@Ciara Brennan​  @David Browne​  @Veronica Simoes​