Solved

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

Posted on 2014-02-03
18
678 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
  • 10
  • 6
18 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
Do you still need the CSV file? If yes, may it be modified, adding the DateCreated as above?
0
 
LVL 6

Author Comment

by:castellansolutions
Comment Utility
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
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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 18

Expert Comment

by:suriyaehnop
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In this video we show how to create a User Mailbox 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 Recipients >> Mailb…
how to add IIS SMTP to handle application/Scanner relays into office 365.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now