Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1971
  • Last Modified:

Get-EventLog : how to sort and group by date (not date AND TIME)

Hello,

(I'm quite new to powershell scripting)
I'd like to improve this little script to group my Get-EventLog entries by date (descending) and eventid.
When I say "Date", I mean YYYY/mm/dd, not TimeGenerated because grouping this way is useless (you don't have much entries generated the very same second :)
=> Right now, I have too many repeated lines, which is why I'd like to group

Notice I'm working in a french environment. I don't know if there is a relationship, but my dates are written like "dd/mm/yyyy" and I guess that's why a "sort-object...-descending..." sorts in a strange way :)
#later : $machines=@("127.0.0.1","another_host")
$machines=@("127.0.0.1")
 
 
$format=@{Expression={$_.TimeGenerated};Label="Date";width=21},`
@{Expression={$_.EventID};Label="EventID";width=7},`
@{Expression={$_.Source};Label="Index";width=25},`
@{Expression={$_.Message};Label="Message"}
 
 
foreach ($machine in $machines) {
	echo "***************************************************************"
	echo $machine
 
 
	$logs=[System.Diagnostics.EventLog]::GetEventLogs($machine)
 
 
	Foreach ($log in $logs) {
		$entrees=$log.entries | Where-Object {$_.entryType -match "Error"} | Select-Object -Last 20 | Format-Table -wrap  $format
		if ($entrees.count -gt 0) {
			echo $log.LogDisplayName
			echo "------------------------------------------------------------------------------"
			echo $entrees
		}
	}
}

Open in new window

0
mchkorg
Asked:
mchkorg
  • 10
  • 10
1 Solution
 
mchkorgAuthor Commented:
ok, thank you
That would be strange to have, let say, an powershell expert, not registering the powershell zone...
I'll check tomorrow

If there are some other zones, regarding "microsoft script language", that might help
But to my mind, with these 3 zones, we should be in the right places.
anyway, thank you
0
 
Chris DentPowerShell DeveloperCommented:

Brandon's inactive at the moment otherwise you'd have got a response from him by now.

I did have a play with your code earlier and I can't reproduce an inability to sort by date (also using dd/mm/yyyy). May I ask where in your code you added Sort-Object? Before or after your custom formatting?

Chris
0
 
mchkorgAuthor Commented:
Format-Table was the last one - I don't remember exactly what I tried :)

With the code provided in my message, everything's sorted, you're right.
My problem is when I tried to group by eventid+ date, I think I tried to substring the date or something, I don't exactly remember. In that case, 4/04/2009 is after 20/04/2009

==> Anyway, as I don't remeber exactly the tests I've done, let's focus on the goal, which is to group by (date,eventid) and not (TimeGenerated,eventid) because TimeGenerated is a "full" timestamp. I just want %Y/%m/%d :)

Thank you
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris DentPowerShell DeveloperCommented:

No worries, so you'd like to strip the time from TimeGenerated, leaving only the date?

/me goes to play

Chris
0
 
mchkorgAuthor Commented:
yes, and be able to group by 2 "columns", this_date + eventID
/me goes home soon
0
 
Chris DentPowerShell DeveloperCommented:

This is a spot of sample code then, I think / hope it does what you're after here. It'll need the custom table formatting re-doing since I've changed some of the fields here.

Chris

# For testing, grab all errors from the Application log
$Entrees = ([System.Diagnostics.EventLog]::GetEventLogs("127.0.0.1") | ?{ $_.LogDisplayName -eq "Application" }).Entries | ?{ $_.EntryType -eq "Error" }
 
# Return an Object containing a reformatted date. Then pass it into Sort-Object
# First sort by date in descending order, then by EventID in ascending order.
$Entrees | Select-Object @{n='Date';e={ (Get-Date($_.TimeGenerated)).ToShortDateString() }}, `
  EventID, Source, Message | Sort-Object @{e='Date';Descending=$True}, @{e='EventID';Ascending=$True}

Open in new window

0
 
mchkorgAuthor Commented:
Hi, thank you for this code aggregation and the operation regarding the date :) but:



- everything is sorted by day THEN month THEN year, see:
30/10/2008
29/10/2008
28/10/2008
27/10/2008
27/04/2009 <== yes, 27 comes before 28, but 2009 comes after 2008 :)
26/10/2008
26/09/2008
Maybe because of a french environment (DD/MM/YYYY, not YYYY-DD-MM)
Can we revert the date format or force it to be written like YYYY-MM-DD (then string comparison will work)


- I tried to group the data by (date,eventid) - even if the date-sort is bad now.
I just added " | group-object  Date,EventID" to your 2nd command group, here's what I get:
Count Name                      Group
----- ----                      -----
    1 30/10/2008, 15            {15}
    1 30/10/2008, 1054          {1054}
    6 30/10/2008, 8005          {8005, 8005, 8005, 8005...}
    1 30/10/2008, 8006          {8006}
    1 29/10/2008, 15            {15}
    3 28/10/2008, 15            {15, 15, 15}
    3 27/10/2008, 15            {15, 15, 15}
    2 27/04/2009, 1             {1, 1}

==> Ok, this time I see only one (date,eventID) per line, but I lost my category and message explanation

- About the reformatting, I'll play with it after, ok.

any idea for these 2 bugs ?

Thank you
0
 
Chris DentPowerShell DeveloperCommented:

Yeah, I do, give me a moment to have a play :)

Chris
0
 
Chris DentPowerShell DeveloperCommented:

Okay, date first.

PowerShell is happy with localising the date format, so it's not a problem with English / French date formats vs US date formats. But...

My code converted it to a string, only way to entirely drop the time part. Unfortunately my test set was way too small to notice that string sorting was no help here.

So this modification takes us back to date formatting. We have to put up with the "00:00:00" at least until after the list is fully sorted.

Just need a moment to play with the other half of your question.

Chris
# Return an Object containing a reformatted date. Then pass it into Sort-Object
# First sort by date in descending order, then by EventID in ascending order.
$Entrees | Select-Object @{n='Date';e={ (Get-Date($_.TimeGenerated)).Date }}, `
  EventID, Source, Message | Sort-Object @{e='Date';Descending=$True}, @{e='EventID';Ascending=$True}

Open in new window

0
 
Chris DentPowerShell DeveloperCommented:

For the second, I take it the output from this one isn't helpful?

$Entrees | Group-Object Date, EventID, Source, Message

It's not the  nicest format, might be better off building your own object for that one (unless you happen to find a better CmdLet to use).

Chris
0
 
Chris DentPowerShell DeveloperCommented:

If you want to try a custom object, this truncates the list and adds a count to each identical entry.

Note that the message also has to be unique. Not sure whether that's something you want or not.

Chris
$Temp = @()
ForEach ($Object in ($Entrees | Select-Object -Property Date, EventID, Source, Message -Unique)) {
  $Temp += $Object | Select-Object *, `
    @{n='Count';e={ $Entries = $Entrees | ?{ $_.EventID -eq $Object.EventID -And $_.Date -eq $Object.Date }; `
      If ($Entries.Count) { $Entries.Count } Else { 1 } } }
}
$Entrees = $Temp

Open in new window

0
 
mchkorgAuthor Commented:
Mmm, yes it does the trick.
I don't care about the 5th column named "Group" - what's this by the way ? it seems to be all the eventIDs (the same each time) repeated "Count" times, quite useless. I'm might be able to skip this with a correct Reformatting stuff.

I tried to merge this with your previous modification with the Date, it seems alright - with reformatting, I'll be able to truncate the Time I guess

Then I need to apply this all to every Log, not only Application.

Thank you, I'm just finishing this when I have a moment (maybe not today) - just to be sure there's no hidden exception somewhere :) then I'll get back to you.

Thank you!
0
 
mchkorgAuthor Commented:
mmm, are you sure I didn't loose the "message" explanation?
It's maybe just a formatting problem, again. Because right now it looks like this:

Count Name                      Group
----- ----                      -----
    2 27/04/2009 00:00:00, 1... {1, 1}
    2 27/04/2009 00:00:00, 1... {1030, 1030}
    2 27/04/2009 00:00:00, 1... {1058, 1058}
    1 27/04/2009 00:00:00, 8... {8006}
    3 23/04/2009 00:00:00, 1... {1030, 1030, 1030}
    2 23/04/2009 00:00:00, 1... {1058, 1058}
    1 22/04/2009 00:00:00, 1... {1000}

==> The 3rd column is the concatenation of every grouped field
0
 
Chris DentPowerShell DeveloperCommented:

The Name column is a concatenation as well, it's messy. I like the custom object better :)

Chris
0
 
mchkorgAuthor Commented:
Ok so let me just a moment to merge this all :)
Thank you, again
0
 
Chris DentPowerShell DeveloperCommented:

Here's a merged version, figured it was getting quite complicated with bits everywhere ;)

I modified the formatting a bit to line up the Event IDs (ToString rather than numeric) and switched the date back to a short date string.

I'll let you play with the rest ;)

Chris
#later : $machines=@("127.0.0.1","another_host")
$machines=@("127.0.0.1")
  
$Format=@{Expression={($_.Date).ToShortDateString()};Label="Date";width=21},`
  @{Expression={($_.EventID).ToString()};Label="EventID";width=7},`
  @{Expression={$_.Source};Label="Index";width=25},`
  @{Expression={$_.Message};Label="Message"}, `
  @{Expression={$_.Count};Label="Count";width=7}
 
ForEach ($machine in $machines) {
  echo "***************************************************************"
  echo $machine
 
  $logs=[System.Diagnostics.EventLog]::GetEventLogs($machine)
 
  Foreach ($log in $logs) {
    $Entrees = $log.Entries | Where-Object {$_.EntryType -eq "Error"}
 
    # Sort the List by Date then Event ID
    $Entrees = $Entrees | Select-Object @{n='Date';e={ (Get-Date($_.TimeGenerated)).Date }}, `
      EventID, Source, Message | Sort-Object @{e='Date';Descending=$True}, @{e='EventID';Ascending=$True}
 
    $Temp = @()
    ForEach ($Object in ($Entrees | Select-Object -Property Date, EventID, Source, Message -Unique)) {
      $Temp += $Object | Select-Object *, `
        @{n='Count';e={ $Entries = $Entrees | ?{ $_.EventID -eq $Object.EventID -And $_.Date -eq $Object.Date }; `
          If ($Entries.Count) { $Entries.Count } Else { 1 } } }
    }
    $Entrees = $Temp
 
    If ($Entrees) {
 
      Echo $log.LogDisplayName
      Echo "------------------------------------------------------------------------------"
 
      # Pick the first 10 in the list (will be the most recent)
      $Entrees | Select-Object -First 10 | FT -Wrap $Format
    }
  }
}

Open in new window

0
 
mchkorgAuthor Commented:
Creating an Object sounds good.

But the Date is empty is this created Object, even if all sort/group activities seem OK.

I tried to play with TimeGenerated insted and thus got back to the beginning :) grouped by date+time :(
Sorry I'm too new to powershell scripting :)

Look at this code, sorry I've merged this all in several loops


#longer list in the future
$machines=@("127.0.0.1")
 
foreach ($machine in $machines) {
	$logs=[System.Diagnostics.EventLog]::GetEventLogs($machine)
	Foreach ($log in $logs) {
		$entrees = $log.entries | ?{ $_.EntryType -eq "Error"} | Select-Object -Last 20
		if ($entrees.count -gt 0) {
			echo $machine $log.LogDisplayName
			echo "-----------------------------------"
			$Temp = @()
			ForEach ($Object in ($Entrees | Select-Object -Property Date, EventID, Source, Message -Unique)) {
				$Temp += $Object | Select-Object *, `
					@{n='Count';e={ $Entries = $Entrees | ?{ $_.EventID -eq $Object.EventID -And $_.Date -eq ($Object.Date) }; `
					If ($Entries.Count) { $Entries.Count } Else { 1 } } }
			}
			$entrees = $Temp
			echo $entrees
		}
	}
}

Open in new window

0
 
mchkorgAuthor Commented:
oops, I posted it a bit late....
0
 
mchkorgAuthor Commented:
Simply perfect! Thank you!
0
 
Chris DentPowerShell DeveloperCommented:

Glad it helped out :)

Chris
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now