Go Premium for a chance to win a PS4. Enter to Win

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

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

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
castellansolutions
Asked:
castellansolutions
  • 10
  • 6
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
castellansolutionsAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
Do you still need the CSV file? If yes, may it be modified, adding the DateCreated as above?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
castellansolutionsAuthor Commented:
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
 
castellansolutionsAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
castellansolutionsAuthor Commented:
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
 
castellansolutionsAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
castellansolutionsAuthor Commented:
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
 
suriyaehnopCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
castellansolutionsAuthor Commented:
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
 
castellansolutionsAuthor Commented:
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
 
castellansolutionsAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
castellansolutionsAuthor Commented:
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

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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