Advice around statistics from csv

Welcome Forums General PowerShell Q&A Advice around statistics from csv

Viewing 5 reply threads
  • Author
    Posts
    • #196619
      Participant
      Topics: 4
      Replies: 5
      Points: 84
      Rank: Member

      Hi,

      We have a system that logs activities to a csv file and i am looking to read this CSV file and send stats out in the day.

      I import the csv file using `$logTable = Import-Csv $logFile`

      I would like to find the count of many different people, for example if i have 10 users, i can find how many activities they did by running

      $user1 = ($logTable | Where-Object { ($_.date -EQ "06/01/2019") -and $_.user -EQ "user1" }).count
      $user2 = ($logTable | Where-Object { ($_.date -EQ "06/01/2019") -and $_.user -EQ "user2" }).count
      $user3 = ($logTable | Where-Object { ($_.date -EQ "06/01/2019") -and $_.user -EQ "user3" }).count
      ...
      ...
      $user10 = ($logTable | Where-Object { ($_.date -EQ "06/01/2019") -and $_.user -EQ "user10" }).count

      I don't feel this is the best way to be doing it though.

      I need to collect the count for 30-40 variations

      Dept 1

      Total: 0 | 22
      Service1: 0 | 6
      Service2: 0 | 4
      Service3: 0 | 0
      Service4: 0 | 12

      Breakdown By User:
      User1: 0 | 0
      User2: 0 | 15
      User3: 0 | 0

      The first number (before the pipe) is how many that day and the 2nd number is how many for the month so far

      So you can see this would mean me duplicating the code above 40 times which would be messy.

      I have the users and services stored in an array so was thinking there should be a way to loop through the arrays and save the count to a variable but not sure how to do that and also not sure if thats the correct way either as it would mean ending up with 40 variables.

      What are you thoughts on handling such task?

       

      Cheers,

      jamie

    • #196631
      Participant
      Topics: 2
      Replies: 1693
      Points: 3,368
      Helping Hand
      Rank: Community Hero

      It's hard to give a reasonable advice without seeing your CSV data (at least a few lines with the relevant but sanitized data) and without knowing what you're actually after. 😉

      Anyway ... you should take a look at the cmdlet Group-Object. You should read the complete help including the examples to learn how to use it.

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

      You can provide a CSV example like so:

      $csv = @"
      User,Service
      User1,Service1
      User3,Service1
      User4,Service2
      User1,Service10
      User2,Service2
      User1,Service8
      "@ | ConvertFrom-CSV
      

      This gives us a basic object to play with:
      PS

      C:\Users\rasim> $csv
      
      User  Service  
      ----  -------  
      User1 Service1 
      User3 Service1 
      User4 Service2 
      User1 Service10
      User2 Service2 
      User1 Service8 
      

      The you can do see some examples of Olaf's suggestion using Group-Object:

      PS C:\Users\rasim> $csv | Group-Object -Property User -NoElement
      
      Count Name                     
      ----- ----                     
          3 User1                    
          1 User3                    
          1 User4                    
          1 User2                    
      
      
      
      PS C:\Users\rasim> $csv | Group-Object -Property Service -NoElement
      
      Count Name                     
      ----- ----                     
          2 Service1                 
          2 Service2                 
          1 Service10                
          1 Service8   
      
    • #196955
      Participant
      Topics: 4
      Replies: 5
      Points: 84
      Rank: Member

      Hi Olaf and Rob,

      I appreciate your replies, they have helped a lot however my original reply that i tried to post 2 days ago still isn't showing up but i can't post it again as it says i have already said that so hopefully my reply with a detailed response will show or be approved soon.

      Regards,
      Jamie

    • #196958
      Participant
      Topics: 2
      Replies: 1693
      Points: 3,368
      Helping Hand
      Rank: Community Hero

      You may open a ticket about this in the feedback forum: https://powershell.org/forums/forum/community-discussion-site-feedback/

    • #196976
      Participant
      Topics: 4
      Replies: 5
      Points: 84
      Rank: Member
Viewing 5 reply threads
  • You must be logged in to reply to this topic.