SOLVED

Compare two csv files

%3CLINGO-SUB%20id%3D%22lingo-sub-2693689%22%20slang%3D%22en-US%22%3ECompare%20two%20csv%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2693689%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20an%20idea%20of%20devices%20that%20have%20not%20enrolled%20in%20Intune%2C%20but%20are%20accessing%20exchange%20online.%20I%20am%20using%20two%20.csv%20files.%20The%20intune%20file%20contains%20all%20devices%20that%20have%20enrolled%20in%20intune%20and%20the%20Exchange%20file%20contains%20all%20devices%20that%20are%20currently%20found%20in%20Exchange%20online.%20I%20want%20to%20compare%20the%20two%20files%20and%20export%20the%20differences%20to%20another%20.csv%20file.%20The%20two%20files%20share%20a%20common%20attribute%20%22deviceid%22%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20any%20guidance%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24Intune%20%3D%20import-csv%20.%5Cintune.csv%20%7C%20Group-Object%20-AsHashTable%20-AsString%20-Property%20'DeviceID'%3CBR%20%2F%3E%24Exchange%20%3D%20import-csv%20.%5Cmobiledevicereport.csv%20%7C%20Group-Object%20-AsHashTable%20-AsString%20-Property%26nbsp%3B'DeviceID'%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2693689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Automation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Intune%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMobile%20Device%20Management%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2693943%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20two%20csv%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2693943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1096811%22%20target%3D%22_blank%22%3E%40Skipster311-1%3C%2FA%3E%26nbsp%3BGive%20this%20a%20try.%20It'll%20output%20two%20files%2C%20one%20containing%20the%20devices%20that%20are%20only%20in%20the%20Intune%20file%2C%20and%20the%20other%20with%20devices%20that%20only%20exist%20in%20the%20Exchange%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24Intune%20%3D%20Import-CSV%20.%5Cintune.csv%20%7C%20Group-Object%20-AsHashTable%20-AsString%20-Property%20'DeviceID'%0A%24Exchange%20%3D%20Import-CSV%20.%5Cmobiledevicereport.csv%20%7C%20Group-Object%20-AsHashTable%20-AsString%20-Property%20'DeviceID'%0A%0A%24OnlyInIntune%20%3D%20%40()%0A%24OnlyInExchange%20%3D%20%40()%0A%0AForEach%20(%24Device%20in%20%24Intune.Values)%20%7B%0A%20%20%20%20if%20(!%24Exchange%5B%24Device.DeviceID%5D)%20%7B%0A%20%20%20%20%20%20%20%20%24OnlyInIntune%20%2B%3D%20%24Device%0A%20%20%20%20%7D%0A%7D%0A%0AForEach%20(%24Device%20in%20%24Exchange.Values)%20%7B%0A%20%20%20%20if%20(!%24Intune%5B%24Device.DeviceID%5D)%20%7B%0A%20%20%20%20%20%20%20%20%24OnlyInExchange%20%2B%3D%20%24Device%0A%20%20%20%20%7D%0A%7D%0A%0A%24OnlyInIntune%20%7C%20Export-CSV%20-NoTypeInformation%20DevicesOnlyInIntune.csv%0A%24OnlyInExchange%20%7C%20Export-CSV%20-NoTypeInformation%20DevicesOnlyInExchange.csv%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello

 

I am trying to get an idea of devices that have not enrolled in Intune, but are accessing exchange online. I am using two .csv files. The intune file contains all devices that have enrolled in intune and the Exchange file contains all devices that are currently found in Exchange online. I want to compare the two files and export the differences to another .csv file. The two files share a common attribute "deviceid" 

Thank you in advance for any guidance 

 

$Intune = import-csv .\intune.csv | Group-Object -AsHashTable -AsString -Property 'DeviceID'
$Exchange = import-csv .\mobiledevicereport.csv | Group-Object -AsHashTable -AsString -Property 'DeviceID'

2 Replies
best response confirmed by Skipster311-1 (Frequent Contributor)
Solution

@Skipster311-1 Give this a try. It'll output two files, one containing the devices that are only in the Intune file, and the other with devices that only exist in the Exchange file.

 

$Intune = Import-CSV .\intune.csv | Group-Object -AsHashTable -AsString -Property 'DeviceID'
$Exchange = Import-CSV .\mobiledevicereport.csv | Group-Object -AsHashTable -AsString -Property 'DeviceID'

$OnlyInIntune = @()
$OnlyInExchange = @()

ForEach ($Device in $Intune.Values) {
    if (!$Exchange[$Device.DeviceID]) {
        $OnlyInIntune += $Device
    }
}

ForEach ($Device in $Exchange.Values) {
    if (!$Intune[$Device.DeviceID]) {
        $OnlyInExchange += $Device
    }
}

$OnlyInIntune | Export-CSV -NoTypeInformation DevicesOnlyInIntune.csv
$OnlyInExchange | Export-CSV -NoTypeInformation DevicesOnlyInExchange.csv
Worked perfectly. Thank you again