Adding a Function and grouping data output

Welcome Forums General PowerShell Q&A Adding a Function and grouping data output

Viewing 1 reply thread
  • Author
    Posts
    • #198239
      Participant
      Topics: 1
      Replies: 0
      Points: 11
      Rank: Member

      SysOps Forum Team

      Just to let you know I am kind for new (newbie) to PowerShell scripting. I have put together this script below to retrieve data from Active Directory (Name, OperatingSystem, IPV4, Last Login, & calculate and output Days Since Last Logon).

      That being said, it does exactly what I initially built it to do. Since I originally wrote this we have grown a bit and I need to make a couple of changes that I am not sure how to complete.

      > First I would like to make the "Retrieve AD Data" into a function so that I apply requests for multiple locations (i.e. San Diego, San Antonio, Charlotte)

      > Second I would like to group each location by

      – (i.e. San Diego's – SD02, SD03, SD04)

      – (i.e. San Antonio's – SA01, SA03, SA04, SA05)

      – (i.e. Charlotte's – CL01, CL02)

       

      So the output that I would like is

      1. One report that outputs each: (preferred)
      • Grouped by: San Diego, San Antonio, & Charlotte
      • Grouped by: Server Groupings
      • Sorted by: Days Since Last Logon

      OR

      1. Separate reports for each (may be easier)
      • Grouped by: Server Groupings
      • Sorted by: Days Since Last Logon

       

      I did make an attempt to user Group-Object (probably incorrectly) and while it did group them, it ended up adding a lot of extraneous information to the right of the “Name” column (pushing it all the way to the right) and excluding all previous columns to the right that was previously there (Shown below).

      #####################################################
      
      # Path to localhost system (run from a server)
      
         $LH="C:\Temp\SvrUpTime\"
      
      # Define Systems to be checked (by location)
         Get_ADComputer -Sever us.contoso.com -Filter {(Name -Like 'SD02*') -or (Name -Like 'SD03*') -or (Name -Like 'SD04*')} |
         Where-Object {($_.Name -notlike '*NAPP*') -and ($_.Name -notlike '*SBU*')} |
      
      # Retrieve AD Data (name, OS, IP, Last Boot) (want to turn this into a function)
         Select-Object Name,OperatingSystem,IPv4Address,@{Name="Last Log On"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp).ToString("MM/DD/YYYY")}},
         @{Name="Days Since Last Logon"; Expr={ ([timespan]((Get-Date) - ([DateTime]$_.LastLogonTimestamp).AddYears (1600))).Days +1; }} |
         Sort-Object "Days Since Last Logon" - Descending |
      
      # Export CVS   (added to each process after function call?)
         Export-CVS $LH\SanDiego-Stats.csv -NoTypeInformation
      
      # Import to view out
         Import-Csv -Path $LH\SanDiego-Stats.csv | Format-Table
      
      ###########################################################
      
      
      

      PowerShell output:

      Name                  OperatingSystem                    IPv4Address         Last Log On (M/D/Y)       Days Since Last Logon          ----                  ---------------                    -----------         -------------------       ---------------------          
      SD03B19-DoCo-01       Windows Server 2016 Datacenter     xxx.xxx.xxx.xxx     01/04/2020                11                                  
      SD03B19-ACAS-01       Windows Server 2012 R2 Standard    xxx.xxx.xxx.xxx     01/05/2020                10                                  
      SD04B20-DoCo-01       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/06/2020                9                                  
      SD03B19-SCCM-0        Windows Server 2012 R2 Standard    xxx.xxx.xxx.xxx     01/09/202                 6                                  
      SD03B19-ORCH-01       Windows Server 2012 R2 Standard    xxx.xxx.xxx.xxx     01/10/2020                5                                  
      SD03B19-DoCo-02       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/10/2020                5                                    
      SD02B19-DoCo-01       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/10/2020                5                                  
      SD04B20-SCCM-01       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/12/2020                3                                  
      SD04B20-ORCH-01       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/12/2020                3                                  
      SD04B20-DoCo-02       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/13/2020                2                                    
      SD04B20-ACAS-01       Windows Server 2016 Standard       xxx.xxx.xxx.xxx     01/15/2020                1
      
      
      

       

      Attempt to use Group-Object:

      Values                                   Count             Group                                           Name                       
      ------                                   -----             -----                                           ----   
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA03B19-ACAS-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA03B19-SCCM-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA03B19-ORCH-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA03B19-DoCo-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA03B19-DoCo-02                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA04B20-ACAS-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA04B20-SCCM-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA04B20-ORCH-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA04B20-DoCo-01                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA04B20-DoCo-02                              
      System.Collections.ArrayList             1                 System.Collections.ObjectModel.Collection`1[... SA02B19-DoCo-01

      I apricate any assistance that can be provided

    • #198275
      Participant
      Topics: 10
      Replies: 1381
      Points: 1,509
      Helping Hand
      Rank: Community Hero

      Before you can do grouping, you need to parse out what you want to Group a bit, take something like this:

      $csv = @"
      Name;OperatingSystem;IPv4Address;Last Log On (M/D/Y);Days Since Last Logon;
      SD03B19-DoCo-01;Windows Server 2016 Datacenter;xxx.xxx.xxx.xxx;01/04/2020;11;
      SD03B19-ACAS-01;Windows Server 2012 R2 Standard;xxx.xxx.xxx.xxx;01/05/2020;10;
      SD04B20-DoCo-01;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/06/2020;9;
      CL02B19-SCCM-01;Windows Server 2012 R2 Standard;xxx.xxx.xxx.xxx;01/09/2020;6;
      SD03B19-ORCH-01;Windows Server 2012 R2 Standard;xxx.xxx.xxx.xxx;01/10/2020;5;
      SA01B19-DoCo-02;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/10/2020;5;
      SD02B19-DoCo-01;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/10/2020;5;
      CL01B20-SCCM-01;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/12/2020;3;
      SD04B20-ORCH-01;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/12/2020;3;
      SA04B20-DoCo-02;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/13/2020;2;
      SD04B20-ACAS-01;Windows Server 2016 Standard;xxx.xxx.xxx.xxx;01/15/2020;1;
      "@ | ConvertFrom-Csv -Delimiter ';'
      
      $results = $csv | 
                 Select *,
                    @{Name='Prefix';Expression={$_.Name.SubString(0,4)}},
                    @{Name='Site';
                      Expression={
                          switch -Regex ($_.Name ) {
                              'SD02|SD03|SD04' {'San Diego'}
                              'SA01|SA03|SA04|SA05' {'San Antonio'}
                              'CL01|CL02' {'Charlotte'}
                              default {'UKNOWN'}
                          }
                     }
                    }
      

      This will give you output like this:

      PS C:\Users\rasim> $results | Sort-Object -Property Site, Name, 'Last Log On (M/D/Y)' | Format-Table -AutoSize *
      
      
      Name            OperatingSystem                 IPv4Address     Last Log On (M/D/Y) Days Since Last Logon Prefix Site       
      ----            ---------------                 -----------     ------------------- --------------------- ------ ----       
      CL01B20-SCCM-01 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/12/2020          3                     CL01   Charlotte  
      CL02B19-SCCM-01 Windows Server 2012 R2 Standard xxx.xxx.xxx.xxx 01/09/2020          6                     CL02   Charlotte  
      SA01B19-DoCo-02 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/10/2020          5                     SA01   San Antonio
      SA04B20-DoCo-02 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/13/2020          2                     SA04   San Antonio
      SD02B19-DoCo-01 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/10/2020          5                     SD02   San Diego  
      SD03B19-ACAS-01 Windows Server 2012 R2 Standard xxx.xxx.xxx.xxx 01/05/2020          10                    SD03   San Diego  
      SD03B19-DoCo-01 Windows Server 2016 Datacenter  xxx.xxx.xxx.xxx 01/04/2020          11                    SD03   San Diego  
      SD03B19-ORCH-01 Windows Server 2012 R2 Standard xxx.xxx.xxx.xxx 01/10/2020          5                     SD03   San Diego  
      SD04B20-ACAS-01 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/15/2020          1                     SD04   San Diego  
      SD04B20-DoCo-01 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/06/2020          9                     SD04   San Diego  
      SD04B20-ORCH-01 Windows Server 2016 Standard    xxx.xxx.xxx.xxx 01/12/2020          3                     SD04   San Diego
      

      Then you can do grouping like so:

      PS C:\Users\rasim> $results | Group-Object -Property Site -NoElement
      
      Count Name                     
      ----- ----                     
          7 San Diego                
          2 Charlotte                
          2 San Antonio              
      
      PS C:\Users\rasim> $results | Group-Object -Property Prefix -NoElement
      
      Count Name                     
      ----- ----                     
          3 SD03                     
          3 SD04                     
          1 CL02                     
          1 SA01                     
          1 SD02                     
          1 CL01                     
          1 SA04                     
      
Viewing 1 reply thread
  • You must be logged in to reply to this topic.