Power Platform Community Forum Thread Details

How are you formatting output from powershell?

I'm looking for information on how to format powershell into a table in excel online for business. Right now, I am using a powershell script to collect computer information from AD. There's two columns of data. I wasn't having any luck formatting it with data table in pad. instead, the script outputs a .csv file. I then pull in the .csv file to pad. How can I put this csv into an excel for business table?

Here's the script I'm running:

#Connect to Azure
Connect-AzureAD
# Set date to 45 days ago
#use this one            
$date = (Get-Date).AddDays(-45)

# Initialize an empty array for device data
$Devices = @()

# Fetch AD computers not logged into within the last 45 days
$ADComputers = Get-ADComputer -Filter { LastLogonTimeStamp -lt $date }

# Process each AD computer
foreach ($ADComputer in $ADComputers) {
    # Get the computer name
    $Name = $ADComputer.Name

    # Attempt to find matching Azure AD device
    $DeviceDetail = Get-AzureADDevice -All $true | Where-Object { $_.DisplayName -eq $Name } | Select-Object ObjectId, DisplayName

    if ($DeviceDetail) {
        # Try to get the primary user of the device
        try {
            $Owner = Get-AzureADDeviceRegisteredOwner -ObjectId $DeviceDetail.ObjectId -ErrorAction Stop
            $PrimaryUser = if ($Owner) { $Owner.UserPrincipalName } else { "No owner found" }
        }
        catch {
            $PrimaryUser = "Error retrieving owner"
        }
    }
    else {
        # Fallback if the Azure AD device is not found
        $PrimaryUser = "Device not found in Azure AD"
    }

    # Retrieve potential owners if no owner is found
    if ($PrimaryUser -eq "No owner found" -or $PrimaryUser -eq "Error retrieving owner") {
        try {
            # Extract initials from the computer name (removing first character and last 6)
            if ($Name.Length -ge 7) {
                $Initials = $Name.Substring(1, $Name.Length - 7)
            }
            else {
                $Initials = ""
            }

            # Initialize potential users array
            $PossibleUsers = @()

            # Search for users in Active Directory with matching initials
            $Users = Get-ADUser -Filter "*" -Properties Initials | Where-Object { 
                $_.Initials -like $Initials -and $Initials -ne "" 
            }

            # Add potential users' SamAccountNames to the list
            foreach ($User in $Users) {
                $PossibleUsers += $User.SamAccountName
            }

            # Update PrimaryUser with possible users, or fallback if none found
            $PrimaryUser = if ($PossibleUsers.Count -gt 0) {
                $PossibleUsers -join ", "
            }
            else {
                "No potential owners found"
            }
        }
        catch {
            $PrimaryUser = "Error retrieving potential owners"
        }
    }

    # Create a custom object with the device information and the primary user
    $Devices += [PSCustomObject]@{
        ComputerName = $Name
        PrimaryUser  = $PrimaryUser
    }
}

# Output the results
$Devices | export-csv 'c:\temp\inactivecompuers.csv' -NoTypeInformation

Even if you could point me to webpages about formatting powershell outputs or datatables or strings, that'd be helpful. I can't find much.

Categories:

Licensing

Process Advisor

Quick Links