none
Filter output from Select-Object based on contained characters and sort output by comma

    Question

  • Dear PowerShell experts,

    I haven't had time yet to deal with the concepts of PowerShell in more detail yet and have a probably simple question. We are in the process of moving to Office 365 and need to receive a list of Office 365 endpoint from the Office 365 Endpoint Web Service.

    For generating the list, Microsoft provides the following Cmdlet:

    https://github.com/samurai-ka/PS-Module-O365EndpointService/blob/master/O365EndpointFunctions.psm1

    If I run the following Cmdlet I am able to receive a list of all IPv4 and IPv6 addresses of interest:

    PS D:\script> Invoke-O365EndpointService -tenantName test -ForceLatest  -IPv6 | where{($_.Protocol -eq "ip") -and (($_.Category -eq "Optimize"))} |  Select-Object uri -Unique

    uri                 
    ---                 
    13.107.6.152/31     
    13.107.18.10/31     
    52.96.0.0/14        
    131.253.33.215/32   
    2603:1006::/40      
    2603:1016::/40      
    2603:1026::/40      
    2603:1026:200::/39  
    13.107.128.0/22     
    23.103.160.0/20     
    40.96.0.0/13        
    40.104.0.0/15

    I would like to know how it is possible to

    a) separate this output for IPv4 and IPv6. I guess this should be possible by just filtering the output based on whether it contains a dot (IPv4) or a double dot (IPv6).

    b) then transform the output into a string in which each IP address is seperated by a comma, i.e. 40.96.0.0/13,40.104.0.0/15 and so on.

    Can anybody help me with this?

    Thanks
    Michael



    • Edited by sam.bell Saturday, May 4, 2019 9:53 AM
    Saturday, May 4, 2019 9:49 AM

All replies

  • Use [ipaddress] type to get the IP type and components.

    $URIs | ForEach-Object{ [ipaddress]( ($_.uri -split '/')[0]) } | select IPAddressToString, addressfamily

    I recommend that you first learn basic PowerShell.


    \_(ツ)_/


    Saturday, May 4, 2019 10:42 AM
    Moderator
  • Hi jrv,

    thanks! If I do

    $URI = Invoke-O365EndpointService -tenantName test -ForceLatest  -IPv6 | where{($_.Protocol -eq "ip") -and (($_.Category -eq "Optimize"))} |  Select-Object uri -Unique

    and then

    $URI3 | ForEach-Object{ [ipaddress]( ($_.uri -split '/')[0]) } | select IPAddressToString, addressfamily

    I get the following output:

    IPAddressToString  AddressFamily
    -----------------  -------------
    13.107.6.152        InterNetwork
    13.107.18.10        InterNetwork
    2603:1006::       InterNetworkV6
    2603:1016::       InterNetworkV6
    2603:1026::       InterNetworkV6
    40.96.0.0           InterNetwork
    40.104.0.0          InterNetwork

    I guess I could separate items based on AddressFamily now so can separate output for IPv4 and IPv6. However, the output is missing the CIDR information (13.107.6.152/24 or 2603:1026::/32) which I also need so this doesn't help me out unfortunately :(

    Can't we just sort the output from the first command based on whether the data contains a dot or double dot?

    I'm looking into learning more PowerShell but currently this is the only problem I need to solve.

    Thanks
    Michael


    • Edited by sam.bell Saturday, May 4, 2019 11:21 AM
    Saturday, May 4, 2019 11:12 AM
  • Start here: Microsoft Virtual   Academy - Getting Started with Microsoft PowerShell

    You can then customize this as you like.


    \_(ツ)_/

    Saturday, May 4, 2019 11:15 AM
    Moderator
  • Note that the CIDR is: $_.uri -split '/')[1]

    \_(ツ)_/

    Saturday, May 4, 2019 11:17 AM
    Moderator
  • Thanks. But can we maybe just sort the output from the first command based on whether the data contains a dot or double dot?

    Regarding the comma separation I was able to do it the following way:

    $IP = Invoke-O365EndpointService -tenantName test -ForceLatest  -IPv6 | where{($_.Protocol -eq "ip") -and (($_.Category -eq "Optimize"))} |  Select-Object uri -Unique

    ($IP.uri | Sort-Object -Unique) -join ","

    This gives me the following output:

    104.146.128.0/17,13.107.128.0/22,13.107.136.0/22,13.107.18.10/31,13.107.6.152/31,13.107.64.0/18,131.253.33.215/32,132.245.0.0/16,134.170.200.0/21,134.170.208.0/21,150.171.32.0/22,150.171.40.0/22,191.232.0.0/23,191.234.140.0/22,204.79.197.215/32,23.103.160.0/20,2603:
    1006::/40,2603:1016::/40,2603:1026::/40,2603:1026:200::/39,2603:1026:400::/39

    So I only need to manage to separate IPv4 from IPv4 in the first step and then everything looks fine!

    Thanks
    Michael

    • Edited by sam.bell Saturday, May 4, 2019 11:37 AM
    Saturday, May 4, 2019 11:24 AM
  • You can do that if you know how.  My method is easier and you can bet the CIDR in just two more lines of code.


    \_(ツ)_/

    Saturday, May 4, 2019 11:56 AM
    Moderator
  • I have achived it the following way which is just what I wanted:

    $IPv4 = Invoke-O365EndpointService -tenantName test -ForceLatest  -IPv6 | where{($_.Protocol -eq "ip") -and (($_.Category -eq "Optimize"))} |  Select-Object uri -Unique | Where-Object { $_ -like '*.*' }

    ($IPv4.uri | Sort-Object -Unique) -join ","
    104.146.128.0/17,13.107.128.0/22,13.107.136.0/22,13.107.18.10/31,13.107.6.152/31,13.107.64.0/18,131.253.33.215/32,132.245.0.0/16,134.170.200.0/21,134.170.208.0/21,150.171.32.0/22,150.171.40.0/22,191.232.0.0/23,191.234.140.0/22,204.79.197.215/32,23.103.160.0/20,40.10
    4.0.0/15,40.108.128.0/17,40.96.0.0/13,52.104.0.0/14,52.112.0.0/14,52.96.0.0/14

    $IPv6 = Invoke-O365EndpointService -tenantName test -ForceLatest  -IPv6 | where{($_.Protocol -eq "ip") -and (($_.Category -eq "Optimize"))} |  Select-Object uri -Unique | Where-Object { $_ -like '*:*' }

    ($IPv6.uri | Sort-Object -Unique) -join ","
    2603:1006::/40,2603:1016::/40,2603:1026::/40,2603:1026:200::/39,2603:1026:400::/39,2603:1026:600::/44,2603:1026:620::/44,2603:1026:630::/44,2603:1026:800::/44,2603:1026:820::/45,2603:1026:830::/44,2603:1036::/39,2603:1036:200::/40,2603:1036:400::/40,2603:1036:600::/40,2603:1036:800::/38,2603:1036:c00::/40,2603:1039:1001::/48,2603:1039:e01::/48,2603:1046::/37,2603:1046:900::/40,2603:1056::/40,2603:1056:400::/40,2603:1056:600::/40,2603:1096::/38,2603:1096:400::/40,2603:1096:600::/40,2603:1096:a00::/39,2603:1096:c00::/40,2603:10a6:200::/40,2603:10a6:400::/40,2603:10a6:600::/40,2603:10a6:800::/40,2603:10d6:200::/40,2620:1ec:4::152/128,2620:1ec:4::153/128,2620:1ec:8f0::/46,2620:1ec:8f8::/46,2620:1ec:900::/46,2620:1ec:908::/46,2620:1ec:a92::152/128,2620:1ec:a92::153/128,2620:1ec:c::10/128,2620:1ec:c::11/128,2620:1ec:d::10/128,2620:1ec:d::11/128,2a01:111:f400::/48,2a01:111:f402::/48


    Thanks
    Michael



    Saturday, May 4, 2019 12:44 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, May 24, 2019 5:58 AM
    Moderator
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, May 31, 2019 3:20 AM
    Moderator