Advice around statistics from csv

PowerShell Help
1

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

2

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. :wink:

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.

3

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
4

Hi,

Thank you for your replies, i really appreciate your support and the directions you give me.

The Group-Object cmdlet i think is the way to go with this, i have done a little bit of playing with it and hopefully will be able to make it fit however here is some extra details / csv that will help you understand what i’m trying to achieve in case there is a better way, i have also added what i currently have to the bottom of this post.

CSV File

$csv = @"
date,time,store,service,user
13/12/2019,10:50:18,Location1,Service1,user1
17/12/2019,16:35:03,Location1,Service2,user2
21/12/2019,15:26:17,Location2,Service1,user4
21/12/2019,15:26:32,Location2,Service1,user4
06/01/2020,15:26:32,Location1,Service2,user1
06/01/2020,15:26:32,Location2,Service1,User3
"@ | ConvertFrom-CSV

 

This is how the statistics are currently output to email (based on a batch file rather than Powershell)
It doesn’t need to stay in this format but just needs to be clear.

The numbers below are random to show you how they output and not a reflection of the test csv data above.

####################### Location 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
User4: 0 | 0

#######################
Location 2

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
User4: 0 | 0

#######################


Forgetting the format of the output for now, i can pretty much get what i’m after with group-object with the following code

$filterDate = Get-Date -Format "dd/MM/yyyy"
$service = $logTable | Where-Object date -eq $filterDate | Group-Object -Property location, service | Select-Object Name, Count
$users = $logTable | Where-Object date -eq $filterDate | Group-Object -Property user | Select-Object Name, Count

This matches today’s date which is good for the first number 0|0 but to get the 2nd number 0|0 i need to search for any records matching $currentMonth

 

Can you advise if this is the best way to collect such data and how i would get currentMonth

 

Regards,

Jamie

5

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

6

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

7

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

[/quote]

I have done so here - https://powershell.org/forums/topic/message-awaiting-review/

Thank you