Skip to main content

Notifications

Power Automate - Power Automate Desktop
Suggested answer

How are you formatting output from powershell?

Posted on 26 Nov 2024 17:20:56 by
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.
powerauto.png
  • Suggested answer
    Deenuji_Loganathan_ Profile Picture
    Deenuji_Loganathan_ 5,859 on 27 Nov 2024 at 09:38:41
    How are you formatting output from powershell?
    Hi there,
     
    You can refer the below screenshot to convert csv to excel format. Refer from "Read from csv file".
     
     
    Thanks,
    Deenuji Loganathan đź‘©â€Ťđź’»
    Power Automate Desktop Community Champion đź¤–
    Follow me on LinkedIn đź‘Ą

    -------------------------------------------------------------------------------------------------------------
    If I've helped solve your query, kindly mark my response as the solution âś” and like my suggestion ❤️  Your feedback supports future seekers đźš€
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,137

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,813

Leaderboard