none
Export to CSV not Working RRS feed

  • Question

  • Thanks for the help in advance. I am not a PowerShell expert by any stretch of the imagination. 

    I am trying to combine Configuration Manager WMI classes, SMS_R_User with SMS_UserMachineRelationship, to get the Primary User, Primary User email address, and machine name. I plan to use the info to email a user to schedule their Win 10 upgrade. I can pull the data together but cannot output to a CSV file. The file is either empty or contains gibberish. My code is below. Can you tell me what I am doing wrong?

    The CSV file should list the data like

    User Name, Email, Machine Name 

    User Name, Email, Machine Name 

    Import-Module "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
    
    #Change values between quotes
    $SiteServer = "SCCM01"
    $SiteCode= "DGR"
    
    #Get user name and email
    $PrimaryUserMail = Get-WmiObject -Namespace "root\SMS\site_$($SiteCode)" -Class SMS_R_User -ComputerName $SiteServer 
    
    #Combine objects to include machine name and assigned by UsageAgent and is Active
    $UDARelationShips = @()
    foreach($item in $PrimaryUserMail){
    
    $UDA = Get-WmiObject -Namespace "root\SMS\site_$($SiteCode)" -Class SMS_UserMachineRelationship -ComputerName $SiteServer -Filter "ResourceID='$($item.ResourceID)'" | where {$_.types -like "1"} | where {$_.sources -like "4"}
    
    foreach($Rel in $UDA){
    
    $DObject = New-Object PSObject
    $DObject | Add-Member -MemberType NoteProperty -Name "ResourceName" -Value $Rel.ResourceName
    $UDARelationShips += $DObject
    }
    }
    #$UDARelationShips | Export-CSV C:\temp\whatever.csv -notypeinformation -Encoding UTF8
    #$PrimaryUserMail | Select UniqueUserName, ResourceName, Mail | Export-Csv c:\temp\whatever2.csv -NoTypeInformation
    #$DObject | Select-String UniqueUserName, Mail, ResourceName | Export-CSV C:\temp\whatever.csv -notypeinformation
    #Write-Output $PrimaryUser | Select ResourceName, UniqueUserName, Types, Sources
    #Write-Output $PrimaryUserMail | Select UniqueUserName, Mail


    • Edited by SCCMRanger Tuesday, November 21, 2017 4:50 PM
    • Moved by Bill_Stewart Thursday, January 25, 2018 10:42 PM This is not SCCM forum
    Tuesday, November 21, 2017 4:20 PM

All replies

  • I see no Export anywhere:

    What is the output of this command:

    Get-WmiObject SMS_UserMachineRelationship  -NS "root\SMS\site_$($SiteCode)" -ComputerName $SiteServer -Filter "ResourceID='$($item.ResourceID)'" | 
        Where-Object { $_.types -eq 1 -and $_.sources -eq 4 } |
    Select ResourceName

    We don't quote numbers and it is not necessary to use a separate "Where" for each test.  There is no need for a custom object to display a single property.


    \_(ツ)_/

    Tuesday, November 21, 2017 4:57 PM
  • Thank you for the quick response! I commented out the three export attempts. See below. None of them work. 

    #$UDARelationShips | Export-CSV C:\temp\whatever.csv -notypeinformation -Encoding UTF8
    #$PrimaryUserMail | Select UniqueUserName, ResourceName, Mail | Export-Csv c:\temp\whatever2.csv -NoTypeInformation
    #$DObject | Select-String UniqueUserName, Mail, ResourceName | Export-CSV C:\temp\whatever.csv -NoTypeInformation

    Tuesday, November 21, 2017 5:02 PM
  • What do you see when you run my code?  Forget about the CSV until you get that working and understand it.


    \_(ツ)_/

    Tuesday, November 21, 2017 5:26 PM
  • Okay, I simplified the code thanks to you. See below. The Write-Output screenshot is below the code.

    Your thoughts? 

    Import-Module "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
    
    $SiteServer = "SCCM01"
    $SiteCode= "DGR"
    
    $PrimaryUser = Get-WmiObject -Namespace "root\SMS\site_$($SiteCode)" -Class SMS_UserMachineRelationship -ComputerName $SiteServer | Where-Object { $_.types -eq 1 -and $_.sources -eq 4 } | Select ResourceName
    
    $PrimaryUserMail = Get-WmiObject -Namespace "root\SMS\site_$($SiteCode)" -Class SMS_R_User -ComputerName $SiteServer #| Out-Host
    
    $DObject = New-Object PSObject -Property @{
    UserName      = $PrimaryUserMail.UniqueUserName
    MachineName   = $PrimaryUser.ResourceName
    Email         = $PrimaryUserMail.Mail
    
    }
    
    Write-Output $DObject

    Tuesday, November 21, 2017 5:57 PM
  • If you cannot do what I asked you to do I cannot help you. 

    I will try once more.  Just run the following and tell us what you see.

    Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
    $SiteServer = 'SCCM01'
    $ns = 'root\SMS\site_DGR'
    
    Get-WmiObject SMS_UserMachineRelationship -NS $ns -ComputerName $SiteServer Filter 'types = 1 and $_.sources = 4'

    Understanding this result will dictate how you write the code.

    "types" and "sources" are arrays.  They will not work as you expect.

    See: https://msdn.microsoft.com/en-us/library/hh949100.aspx


    \_(ツ)_/


    • Edited by jrv Tuesday, November 21, 2017 6:25 PM
    Tuesday, November 21, 2017 6:22 PM
  • Thank you again for your help. I ran your code and received the following output. I would like to combine the below info with two SMS_R_User properties.  

    Tuesday, November 21, 2017 6:32 PM
  • That is still not the code I posted.

    Please just copy and use this with no changes.

    Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
    $SiteServer = 'SCCM01'
    $ns = 'root\SMS\site_DGR'
    
    Get-WmiObject SMS_UserMachineRelationship -NS $ns -ComputerName $SiteServer |
        Where {$_.types -contains 1 -and $_.sources -contains 4}
    

    I have also shown how to correctly test array properties.

    The outcome cannot be easily combined with anything because there is no relation between you two queries.  If you just want to list all resources for the users then you will need to enumerate the users and add the remainder of the information.  The names will be added as an array or can be converted into a delimited string.  The objects cannot be merged into a CSV in any meaningful way.


    \_(ツ)_/

    Tuesday, November 21, 2017 6:41 PM
  • To get a delimited list of names you would do this:

    $resoureNames = (Get-WmiObject SMS_UserMachineRelationship -NS $ns -ComputerName $SiteServer |
        Where {$_.types -contains 1 -and $_.sources -contains 4} |
        Select -ExpandProperty ResourceName) -join '|'
    


    \_(ツ)_/

    Tuesday, November 21, 2017 6:43 PM
  • Okay. I ran the code as shown above. I receive all the properties back for each machine. A sample of one machine (DC02) is below.

    Tuesday, November 21, 2017 6:45 PM
  • What happens if more than one object is returned?

    That has to be managed like this:

    $resoureNames = (Get-WmiObject SMS_UserMachineRelationship -NS $ns -ComputerName $SiteServer |
        Where {$_.types -contains 1 -and $_.sources -contains 4} |
        Select -ExpandProperty ResourceName) -join '|'


    \_(ツ)_/

    Tuesday, November 21, 2017 7:21 PM
  • I expect multiple objects back. The code you provided handles it fine.

    You mentioned above it would be difficult to combine the two classes together and export out to a CSV. Creating the new object does output to the screen but never works when exporting to a CSV file. Can you recommend an alternative? 

    The three values will be stored in a SQL DB. 

    Tuesday, November 21, 2017 10:14 PM
  • Now see how many objects this returns:

    Get-WmiObject SMS_R_User -NS $ns -ComputerName $SiteServer


    \_(ツ)_/

    Tuesday, November 21, 2017 10:17 PM
  • That returns all my user accounts with the same format as the other class. In production, the object results will be around 10,000 objects. 
    Tuesday, November 21, 2017 11:24 PM
  • Now explain what it is you want to combine with all of these objects.


    \_(ツ)_/

    Tuesday, November 21, 2017 11:25 PM
  • FYI, both WMI classes hold the UniqueUserName property. I can grab the User Name and the Mail attributes or the Machine Name and the User Name. Then I need to combine one field fromt he other WMI class. 
    Tuesday, November 21, 2017 11:26 PM
  • Then you have to enumerate one or the other and use it to look up the missing information.

    \_(ツ)_/

    Tuesday, November 21, 2017 11:29 PM
  • I need three total properties exported to a file to then import into a SQL DB. CSV comma delimited would be easiest. My ideal format would be

    DGR\User1 User1@domain.com Workstation1

    DGR\User2 User2@domain.com Workstation2

    The particular properties are the User Name, Machine Name, and Email address. 

    SMS_R_User holds the UniqueUserName and Mail properties. 

    SMS_UserMachineRelationship holds the relationship between the user and the machine. Specifically, I need the ResourceName from this class.

    I was successful combining the two classes by creating a new object. If you look at the 5th post in this thread, you will see that version successfully write all the object to the screen. Using Export-CSV does not work though. 

    Hence, I am stuck. 


    • Edited by SCCMRanger Tuesday, November 21, 2017 11:37 PM Added to answer
    Tuesday, November 21, 2017 11:35 PM
  • Also note that "mail" is not a member of either object and ResourceName only exists in one object.

    I recommend posting in the SMS forum for assistance on using the SMS classes to get the information you seek.  The approach you are using cann do what you ask.


    \_(ツ)_/

    Tuesday, November 21, 2017 11:36 PM
  • Mail is a property of SMS_R_User class. See link below for the class full description. The SCCM Admin does need to enable the collection of the email address using AD User Discovery. 

    https://docs.microsoft.com/en-us/sccm/develop/reference/core/clients/manage/sms_r_user-server-wmi-class 

    I have posted to the ConfigMgr forum but no one has responded. :(

    Thank you very much for your time and effort. I've learned a few things form you. I really appreciate it. 

    Tuesday, November 21, 2017 11:42 PM
  • Enumerate all users and retrieve the associated resource using the ResourceID in the user object.


    \_(ツ)_/

    Wednesday, November 22, 2017 12:07 AM