?
Solved

Exchange 2010, Log Parser, Windows Event Logs, CSV File Format.

Posted on 2014-02-03
18
Medium Priority
?
714 Views
Last Modified: 2014-06-17
Hey Guys

Hoping for some query assistance here. I am using log parser to read a bung of events from several different servers all dumped into 1 CSV file.

So far everything works correctly. Here is the query i am using:

/*  New Query  */

SELECT TimeCreated AS TimeCreated, Count (ID) as EventID, LevelDisplayName as Severity
FROM c:\temp\Data-Test-JanFull.csv
Where Severity LIKE '%Critical%' OR Severity LIKE '%Warning%'
GROUP BY TimeCreated,Severity,ID
Order By TimeCreated Asc

The only problem i have is that each date/time is counted separately, so for example, I get the following:

TimeCreated      EventID      Severity
1/1/2014 10:01:46 PM      3      Warning
1/1/2014 10:02:36 AM      1      Warning
1/1/2014 10:02:50 PM      1      Warning
1/1/2014 10:15:23 PM      1      Warning
1/1/2014 10:17:37 AM      1      Warning
1/1/2014 10:17:51 PM      1      Warning
1/1/2014 10:18:15 PM      1      Warning
1/1/2014 10:30:29 AM      1      Warning
1/1/2014 10:32:37 AM      1      Warning
1/1/2014 10:32:52 PM      1      Warning

What i want to do is break down the events by day and not by time. So i would want to see all events that occurred on say 1/1/2014, but not further separated by time on that same date.

I am trying to create a graph that shows the number of events for each day of the month. I have tried using the TO_Date(Timestamp) function but its not working for me.

Please note: I need to pull this data from a flat CSV file and not from each server itself.

Any ideas?

Thanks,

Robert
0
Comment
Question by:castellansolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
18 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39831093
I assume, since you added the PS TA, that you are not restricted to using a Jet CSV file query. Doing it completely in PS instead:
import-csv c:\temp\Data-Test-JanFull.csv |
  select *, @{n='DateCreated'; e={get-date $_.TimeCreated -format 'yyyy-MM-dd'}} |
  group DateCreated | select Name, Count

Open in new window

If you want to stay with Jet, use CDate(CLng(TimeCreated)).
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39831104
Ok here is one for you. I am using this powershell command to generate the log file, another EE user was very helpful with this command.

get-content -Path c:\servers.txt | ForEach `
{
    $server = $_
    Get-WinEvent -FilterHashTable @{LogName='Application'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ |
     Select-Object @{n="Server";e={$server}},TimeCreated,LogName,ProviderName,Id,LevelDisplayName,Message
    Get-WinEvent -FilterHashTable @{LogName='System'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ |
     Select-Object @{n="Server";e={$server}},TimeCreated,LogName,ProviderName,Id,LevelDisplayName,Message
} | Export-CSV -Path C:\Temp\Data-Test-JanFull.csv -NoTypeInformation

How do i take your command and add it to my command without messing up what i have? The above command needs to be single action, i.e. not running 2 separate powershell commands.

Thanks,

Robert
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39831116
Do you still need the CSV file? If yes, may it be modified, adding the DateCreated as above?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 6

Author Comment

by:castellansolutions
ID: 39831119
This is the other command i was trying to use:

/*  New Query  */

SELECT TO_Date(timestamp) = timestamp('mm-dd-yyyy', 'mm-dd-yyyy') (TimeCreated) AS TimeCreated, Count (ID) as EventID, LevelDisplayName as Severity
FROM c:\temp\Data-Test-JanFull.csv
Where Severity LIKE '%Critical%' OR Severity LIKE '%Warning%'
GROUP BY TimeCreated,Severity,ID
Order By TimeCreated Asc
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39831123
Well there are 2 ways i can do this. Let me state my original goal. I want to be able to query all servers exchange organization (so not dc's) and gather all event logs that are Critical, Error, or Warning.

Using the power shell command i am able to do that as long as i supply the server list and it works everytime. Its a great command. I am using log parser to generate charts, from the CSV file that i created.

Needless to say this has definitely been a learning process for me.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39831144
I'm not positive this answers my question. I assume you need the CSV files unchanged. This adds effort for the script ...
get-content -Path c:\servers.txt | ForEach `
{
    $server = $_
    Get-WinEvent -FilterHashTable @{LogName='Application'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ |
     Select-Object @{n="Server";e={$server}},TimeCreated,LogName,ProviderName,Id,LevelDisplayName,Message
    Get-WinEvent -FilterHashTable @{LogName='System'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ |
     Select-Object @{n="Server";e={$server}},TimeCreated,LogName,ProviderName,Id,LevelDisplayName,Message
} | tee EventLog |Export-CSV -Path C:\Temp\Data-Test-JanFull.csv -NoTypeInformation

$EventLog | 
  select *, @{n='DateCreated'; e={get-date $_.TimeCreated -format 'yyyy-MM-dd'}} |
  group DateCreated | select Name, Count

Open in new window

0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39831154
So it looks like you took my command, added a "tee" and then simply appended your command after my command? I would have thought the Export-CSV would be last (after your command).
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39831157
also it doesnt matter if the original csv file has the original date/time in fact removing the time portion of the date is actually preferred. Because if i have to make specific charts in Excel the time portion just gets in the way.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39831193
Tee-Object (short tee) stores the pipeline content into a variable (here EventLog), and passes the pipeline objects to the next command. The result is that we have a copy of the pipeline now, and can work on that.

However, removing the time portion makes it easier - but you are loosing the exact sequence of eventlog entries that way, so I recommend to have the DateCreated column in addition to the original TimeCreated.
get-content -Path c:\servers.txt | ForEach `
{
    Get-WinEvent -FilterHashTable @{LogName='Application'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ 
    Get-WinEvent -FilterHashTable @{LogName='System'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ 
} |
  Select-Object @{n='Server'; e={$server}}, TimeCreated,
    @{n='DateCreated'; e={get-date $_.TimeCreated -format 'yyyy-MM-dd'}},
    LogName, ProviderName, Id, LevelDisplayName,Message |
  tee EventLog | Export-CSV -Path C:\Temp\Data-Test-JanFull.csv -NoTypeInformation

$EventLog |  group DateCreated | select Name, Count

Open in new window

0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39831330
This is working, however the last server is the only server that shows up in the CSV file. It looks like the events from all the other servers are being overwritten by the final server.

Previously it was showing all the events from all the servers.

Any ideas?
0
 
LVL 19

Expert Comment

by:suriyaehnop
ID: 39831564
I recommended you a log parser studio, a GUI version of log parser. There are existing query where you can make use of it.

There a query name "Report count all errors and warning per day". I had modify little bit

SELECT QUANTIZE(TimeGenerated, 86400) AS Day, COUNT(*) AS [Total Errors]
FROM c:\temp\Data-Test-JanFull.csv
Where Severity LIKE '%Critical%' OR Severity LIKE '%Warning%'
GROUP BY Day
ORDER BY Day ASC

Open in new window


Please explore Log Parser Studio
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39831766
No, it were all logs, but the Server name was wrong, I forgot to change that part:
get-content -Path c:\servers.txt | ForEach `
{
    Get-WinEvent -FilterHashTable @{LogName='Application'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ 
    Get-WinEvent -FilterHashTable @{LogName='System'; Level=1,2,3; StartTime="1/1/2014"} -ErrorAction SilentlyContinue -Computer $_ 
} |
  Select-Object @{n='Server'; e={$_.MachineName}}, TimeCreated,
    @{n='DateCreated'; e={get-date $_.TimeCreated -format 'yyyy-MM-dd'}},
    LogName, ProviderName, Id, LevelDisplayName,Message |
  tee EventLog | Export-CSV -Path C:\Temp\Data-Test-JanFull.csv -NoTypeInformation

$EventLog |  group DateCreated | select Name, Count

Open in new window

0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39833308
Qlemo, Thanks for clarification. I will test the command shortly, however your previous command was exactly what I needed so I am sure the improved command will be even better.

Suriya, When I try your command log parser gives some random error, which I am sure makes complete sense to you but none to me.
Log Parser ErrorsThanks,

Robert
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 39835763
Suriya

Do you have any suggestions to get the log parser portion to work? I would like to have it available to use.

Thanks,

Robert
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 40087145
Qlemo,

I need to get some additional assistance on a project related to the above query that you helped write for me. Here is what I want to do:


1) Generate a graph (I can provide examples) similar to what pivot tables in excel look like. I would like to graph Critical, Warning and Error

2) Generate a list of successful backups compared to the number of backups that started and to show the difference, broken out by incremental and full

3) Connect to a SQL Database and pull Performance metrics (30 day period) for several different items (CPU, Disk, Network, Memory Usage Percentage, RPC Latency, Amount of messages sent/received per sec - average).

Naturally, I would like to just hit "Go" and have this report the data I need. I realize this a bug request. How difficult is the above to accomplish?

Thanks,

Robert
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40087619
Just ask that as three (!) new questions, and put more details and some examples in. Those questions are connected very remotely, and cannot get asked as one (and not as part of this question either). Each part requires different skills. You might post a link to this question resp. the accepted comment http://www.experts-exchange.com/Q_28355479.html#a39831766 so you won't have to explain everything again.
0
 
LVL 6

Author Comment

by:castellansolutions
ID: 40140124
Hey Qlemo,

Would you consider looking at this post here:

http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28452056.html#a40135881

Vielen Danke,

Robert
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question