Solved

Customize the powershell script

Posted on 2014-09-18
22
501 Views
Last Modified: 2014-09-30
Experts, need help on customize the below the script to get reports for weekly\hourly\daily.

SETUP : We have forwarded all the AD security event to collector server and on collector we run this script to pull the report. The below script generate reports for whole event list. I want report for  weekly\hourly\daily. Also need to ignore the service account changes I need only changes made by the Admins. Because we use service account to perform account management.

# Grab the events from a DC          
$Events = Get-WinEvent -FilterHashtable @{Logname='forwardedevents'}          
           
# Parse out the event message data          
ForEach ($Event in $Events) {          
    # Convert the event to XML          
    $eventXML = [xml]$Event.ToXml()          
    # Iterate through each one of the XML message properties          
    For ($i=0; $i -lt $eventXML.Event.EventData.Data.Count; $i++) {            
        # Append these as object properties          
        Add-Member -InputObject $Event -MemberType NoteProperty -Force -Name  $eventXML.Event.EventData.Data[$i].name -Value $eventXML.Event.EventData.Data[$i].'#text'          
    }          
}          

# View the results with your favorite output method          
$Events | Select-object timecreated,machinename,ID,SubjectUserName,SubjectDomainName,DSNAME,ObjectDN,ObjectClass,AttributeLDAPDisplayName,AttributeValue,Message | Export-Csv D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv
0
Comment
Question by:Sekar Chinnakannu
[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
  • 11
  • 6
  • 5
22 Comments
 
LVL 19

Assisted Solution

by:Raheman M. Abdul
Raheman M. Abdul earned 100 total points
ID: 40333281
try changing the "#View the results with your...." part to the following and see if that works.

$freq = Read-host "Enter frequency of report generation (1 for daily; 7 for weekly; 30 for monthly  OR any number of days) :"

$frequency = (get-date) - (new-timespan -day $freq)

# View the results with your favorite output method          
$Events | where {$_.timecreated -ge $frequency} | Select-object timecreated,machinename,ID,SubjectUserName,SubjectDomainName,DSNAME,ObjectDN,ObjectClass,AttributeLDAPDisplayName,AttributeValue,Message | Export-Csv D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv
0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40334274
After adding the part which you mentioned I am not getting prompt to enter date. Could you please check and  update
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 40334482
why do you want to enter the date? You mean you want reports to be generated from that date?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40334884
can you help to split in to three, one for hours, daily and weekly, monthly, So that i can run daily weekly monthly reports by scheduling.
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 40335437
For daily reports:

$frequency = (get-date) - (new-timespan -day 1)    # 1 for daily  ; replace 1 with 7 for weekly
, 
# View the results with your favorite output method          
$Events | where {$_.timecreated -ge $frequency} | Select-object timecreated,machinename,ID,SubjectUserName,SubjectDomainName,DSNAME,ObjectDN,ObjectClass,AttributeLDAPDisplayName,AttributeValue,Message | Export-Csv D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv 

Open in new window

0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 40335444
for hourly:
$frequency = (get-date) - (new-timespan -hours 1)  
, 
# View the results with your favorite output method          
$Events | where {$_.timecreated -ge $frequency} | Select-object timecreated,machinename,ID,SubjectUserName,SubjectDomainName,DSNAME,ObjectDN,ObjectClass,AttributeLDAPDisplayName,AttributeValue,Message | Export-Csv D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv 

Open in new window

0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40335448
Can you also help on weekly and monthly script
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 40335514
does that work ?
0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40335950
script is running and creating the output file, no data inside the output file.

Also is there any options to sort USERNAME only to admin account. Because we use service account to create\delete account in AD. So I don't want that to in report I want to track only changes done by Administrators
0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40338263
___________________________________________________________________________________________________________________________________
PS C:\Users\esdfg> D:\Reports\TestWeekly.ps1
New-TimeSpan : Cannot bind parameter 'End'. Cannot convert value "day" to type "System.DateTime". Error: "The string was not recognized as a valid DateTime. There is a unknown word starting at index 0."
At D:\Reports\TestWeekly.ps1:65 char:40
+ $frequency = (get-date) - (new-timespan <<<<  - day 1)    # 1 for daily  ; replace 1 with 7 for weekly
    + CategoryInfo          : InvalidArgument: (:) [New-TimeSpan], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.NewTimeSpanCommand
 

__________________________________________________________________________________________________________________________________________________________________________________________________________________________________
PS C:\Users\sdfg> D:\Reports\TestWeekly.ps1
New-TimeSpan : Cannot bind parameter 'End'. Cannot convert value "day" to type "System.DateTime". Error: "The string was not recognized as a valid DateTime. There is a unknown word starting at index 0."
At D:\Reports\TestWeekly.ps1:65 char:40
+ $frequency = (get-date) - (new-timespan <<<<  - day 7)    # 1 for daily  ; replace 1 with 7 for weekly
    + CategoryInfo          : InvalidArgument: (:) [New-TimeSpan], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.NewTimeSpanCommand
0
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40338295
I typically use the Event Viewer and the filter function to limit what gets pulled from the event log.  This allows me to graphically see the events I want, then copy the XPath filter into PowerShell.  Once PowerShell is pulling just the events I want, I can then do all the parsing and object manipulation I need.  This is much more efficient, especially when dealing with large security logs.

This procedure will also allow you to filter out any new events you don't want.  For example, you mentioned that you need to filter out the events from a specific service account.  You simply need to change the XMLFilter to meet your specific need, by building and testing the filter using the Event Viewer.  When complete, copy the XML Filter from the XML Tab of the Event Viewer and the same script just works.

Here is a link to a good article about building the XPath
http://blog.backslasher.net/filtering-windows-event-log-using-xpath.html

Param(
  [Parameter(ParameterSetName='Hourly',
                Mandatory=$true)]
  [switch]$Hourly,
  [Parameter(ParameterSetName='Weekly',
                Mandatory=$true)]
  [switch]$Weekly,
  [Parameter(ParameterSetName='Monthly',
                Mandatory=$true)]
  [switch]$Monthly

)

# Use EventViewer and the Filter Current Log option to build the XML Filter,
#    then Transpose the XML into the appropriate XML hash

$Past60MinXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 3600000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past7DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 604800000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past30DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 2592000000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

If ($Hourly) {
    $EventXPath = $Past60MinXMLFilterHash
} ElseIf ($Weekly) {
    $EventXPath = $Past7DayXMLFilterHash
} Else {
    $EventXPath = $Past30DayXMLFilterHash
}


# Grab the events from a DC           
$Events = Get-WinEvent -FilterXPath $EventXPath -LogName forwardedevents        

# Parse out the event message data           
ForEach ($Event in $Events) {           
    # Convert the event to XML           
    $eventXML = [xml]$Event.ToXml()           
    # Iterate through each one of the XML message properties           
    For ($i=0; $i -lt $eventXML.Event.EventData.Data.Count; $i++) {            
        # Append these as object properties           
        Add-Member -InputObject $Event -MemberType NoteProperty -Force -Name  $eventXML.Event.EventData.Data[$i].name -Value $eventXML.Event.EventData.Data[$i].'#text'           
    }           
}    

, 
# View the results with your favorite output method          
$Events |
    where {$_.timecreated -ge $frequency} | 
        Select-object timecreated,`
            machinename,`
            ID,`
            SubjectUserName,`
            SubjectDomainName,`
            DSNAME,`
            ObjectDN,`
            ObjectClass,`
            AttributeLDAPDisplayName,`
            AttributeValue,Message | 
            Export-Csv D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv 
  

Open in new window

0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40338304
Error

PS D:\Reports> C:\Users\esdf\Desktop\sql.ps1
Parameter set cannot be resolved using the specified named parameters.
    + CategoryInfo          : InvalidArgument: (:) [sql.ps1], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : AmbiguousParameterSet,sql.ps1
0
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40338920
You need to pass one of the parameters to my script. It requires at least one and can not have multiple

C:\users\esdf\desktop\sql.ps1 -hourly

C:\users\esdf\desktop\sql.ps1 -weekly

C:\users\esdf\desktop\sql.ps1 -monthly
0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40340644
@ Phil - script is running and creating the output file, no data inside the output file.
0
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40342414
The code is working for me.   I removed the where {$_.timecreated -ge $frequency} | in the final section, but that should not have had an issue, unless you had $frequency defined.

I would open the Powershell ISE and use the Run Selection (F8) to try and see what you are getting inside of $events.

The $events array may be empty.  The file is getting created, but you're not passing anything into the pipeline.

I've removed the stray "Where" and also added a few Verbose lines.  if you run this from the console you should be able to get more information about what's happening

Param(
  [Parameter(ParameterSetName='Hourly',
                Mandatory=$true)]
  [switch]$Hourly,
  [Parameter(ParameterSetName='Weekly',
                Mandatory=$true)]
  [switch]$Weekly,
  [Parameter(ParameterSetName='Monthly',
                Mandatory=$true)]
  [switch]$Monthly

)

# Use EventViewer and the Filter Current Log option to build the XML Filter,
#    then Transpose the XML into the appropriate XML hash

$Past60MinXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 3600000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past7DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 604800000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past30DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 2592000000]
            ]
        ]
    </Select>
  </Query>
</QueryList>
"@

If ($Hourly) {
    $EventXPath = $Past60MinXMLFilterHash
} ElseIf ($Weekly) {
    $EventXPath = $Past7DayXMLFilterHash
} Else {
    $EventXPath = $Past30DayXMLFilterHash
}


# Grab the events from a DC           
$Events = Get-WinEvent -FilterXPath $EventXPath -LogName forwardedevents    

Write-Verbose "Found $($events.Count) events"     -Verbose

# Parse out the event message data           
ForEach ($Event in $Events) {           
    # Convert the event to XML           
    $eventXML = [xml]$Event.ToXml()           
    # Iterate through each one of the XML message properties           
    For ($i=0; $i -lt $eventXML.Event.EventData.Data.Count; $i++) {            
        # Append these as object properties           
        Add-Member -InputObject $Event -MemberType NoteProperty -Force -Name  $eventXML.Event.EventData.Data[$i].name -Value $eventXML.Event.EventData.Data[$i].'#text'           
        Write-verbose "Adding property $($eventXML.Event.EventData.Data[$i].name)"  -Verbose
    }           
}    

$EventsFileName = "D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv"

# View the results with your favorite output method          
$Events |
        Select-object timecreated,`
            machinename,`
            ID,`
            SubjectUserName,`
            SubjectDomainName,`
            DSNAME,`
            ObjectDN,`
            ObjectClass,`
            AttributeLDAPDisplayName,`
            AttributeValue,Message | 
            Export-Csv -Path $EventsFileName -NoTypeInformation

Write-Verbose "$( (Import-Csv -Path $EventsFileName | measure) ) records written" -Verbose

Open in new window

0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40343168
Thanks Phil, ITs working.

 I need to get report only changes made by admin not by any service accounts. All the service account are in alphabetical and admin account starts are SGC12345, BDC12345, ADC12345, 12345. I want only changes done by these account in report.
0
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40343882
I'm not exactly sure which events you want and If you're asking for help creating the filter.  I would need to see a sample of a "good" event that you want.  and a "bad" event that you DO NOT want.
0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40349236
@Phil, PFA, In attachment you can see the SubjectUserName, I dont want service account and generic account changes in report.
I need to get report report only the changes made by ADA1234, DAD5869 & 1323453 admins. Let me know if you have queries
SampleData.xlsx
0
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40349935
Here are my edits,  You'll need to make a few modifications to meet you needs,  I also added a few examples so you can see how to further filter down to list

Param(
  [Parameter(ParameterSetName='Hourly',
                Mandatory=$true)]
  [switch]$Hourly,
  [Parameter(ParameterSetName='Weekly',
                Mandatory=$true)]
  [switch]$Weekly,
  [Parameter(ParameterSetName='Monthly',
                Mandatory=$true)]
  [switch]$Monthly

)

# Use EventViewer and the Filter Current Log option to build the XML Filter,
#    then Transpose the XML into the appropriate XML hash

$Past60MinXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 3600000]
            ]
            and
		    EventData[
			    Data
                    [@Name="SubjectUserName"] != "GENRIC ACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "SERVICEACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "SOME_OTHER_ACCOUNT"
		    ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past7DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 604800000]
            ]
            and
		    EventData[
			    Data
                    [@Name="SubjectUserName"] != "GENRIC ACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "SERVICEACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "stonealm"
		    ]
        ]
    </Select>
  </Query>
</QueryList>
"@

$Past30DayXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [TimeCreated
                [timediff(@SystemTime) &lt;= 2592000000]
            ]
            and
		    EventData[
			    Data
                    [@Name="SubjectUserName"] != "GENRIC ACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "SERVICEACCOUNT"
			    or
			    Data
                    [@Name="SubjectUserName"] != "SOME_OTHER_ACCOUNT"
		    ]
        ]
    </Select>
  </Query>
</QueryList>
"@


$Example1 = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [
            (
                (EventID=5145)
                or
                (EventID=5152)
            )
			and
            TimeCreated
                [timediff(@SystemTime) &lt;= 2592000000]
           ]
        and
		EventData[
			Data
                [@Name="SubjectUserName"] != "GENRIC ACCOUNT"
			or
			Data
                [@Name="SubjectUserName"] != "SERVICEACCOUNT"
			or
			Data
                [@Name="SubjectUserName"] != "SOME_OTHER_ACCOUNT"
		]
        ]
    </Select>
  </Query>
</QueryList>
"@



$Example2 = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [
            (EventID=5145)
			and
            TimeCreated
                [timediff(@SystemTime) &lt;= 2592000000]
           ]
        and
		EventData[
			(
            Data
                [@Name="SubjectUserName"] != "GENRIC ACCOUNT"
			)
            and
            (
            Data
                [@Name="IpAddress"] != "10.1.1.1"
            )                
		]
        ]
    </Select>
  </Query>
</QueryList>
"@
If ($Hourly) {
    $EventXPath = $Past60MinXMLFilterHash
} ElseIf ($Weekly) {
    $EventXPath = $Past7DayXMLFilterHash
} Else {
    $EventXPath = $Past30DayXMLFilterHash
}


# Grab the events from a DC           
$Events = Get-WinEvent -FilterXPath $EventXPath -LogName forwardedevents    

Write-Verbose "Found $($events.Count) events"     -Verbose

# Parse out the event message data           
ForEach ($Event in $Events) {           
    # Convert the event to XML           
    $eventXML = [xml]$Event.ToXml()           
    # Iterate through each one of the XML message properties           
    For ($i=0; $i -lt $eventXML.Event.EventData.Data.Count; $i++) {            
        # Append these as object properties           
        Add-Member -InputObject $Event -MemberType NoteProperty -Force -Name  $eventXML.Event.EventData.Data[$i].name -Value $eventXML.Event.EventData.Data[$i].'#text'           
        Write-verbose "Adding property $($eventXML.Event.EventData.Data[$i].name)"  -Verbose
    }           
}    

$EventsFileName = "D:\Cleanevents_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv"

# View the results with your favorite output method          
$Events |
        Select-object timecreated,`
            machinename,`
            ID,`
            SubjectUserName,`
            SubjectDomainName,`
            DSNAME,`
            ObjectDN,`
            ObjectClass,`
            AttributeLDAPDisplayName,`
            AttributeValue,Message | 
            Export-Csv -Path $EventsFileName -NoTypeInformation

Write-Verbose "$( (Import-Csv -Path $EventsFileName | measure) ) records written" -Verbose
  
 

Open in new window

0
 
LVL 25

Author Comment

by:Sekar Chinnakannu
ID: 40351457
Thanks Phil, I tried the below script for last one hour but still I am getting full report as per my sample data.

$Past60MinXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [
                (EventID=5156)
                or
                (EventID=51456)
                or
                (EventID=514)
                or
                (EventID=5156)
                or
                (EventID=5345)
            and
            TimeCreated
                [timediff(@SystemTime) &lt;= 3600000]
            ] and
                EventData[
                      (
                Data
                    [@Name="SubjectUserName"] != "SACCOUNT"
                      or
                      Data
                    [@Name="SubjectUserName"] != "DACCOUNT"
                      or
                      Data
                    [@Name="SubjectUserName"] != "AACCOUNT"
                      or
                      Data
                    [@Name="SubjectUserName"] != "EACCOUNT"
                 )
                ]
        ]
    </Select>
  </Query>
</QueryList>
"@
0
 
LVL 3

Accepted Solution

by:
Phil Bossman earned 400 total points
ID: 40352083
Sorry, its an "AND" to for negation of the usernames not an OR.   My Logic design is rusty

Get event with ( this eventID ) and ( within this time ) and ( IS NOT this username ) AND ( IS NOT this other username )

The previous one said....
Get event with ( this eventID ) and ( within this time ) and ( ( IS NOT this username ) OR ( IS NOT this other username ) )

Which would match each event as every event IS NOT at least one of the usernames

$Past60MinXMLFilterHash = @"
<QueryList>
  <Query Id='0' Path='forwardedevents'>
    <Select Path='forwardedevents'>*
        [System
            [
                (EventID=5156)
                or
                (EventID=51456)
                or
                (EventID=514)
                or
                (EventID=5156)
                or
                (EventID=5345)
            and
            TimeCreated
                [timediff(@SystemTime) &lt;= 3600000]
            ] and
                EventData[
                      (
                Data
                    [@Name="SubjectUserName"] != "SACCOUNT"
                      and
                      Data
                    [@Name="SubjectUserName"] != "DACCOUNT"
                      and
                      Data
                    [@Name="SubjectUserName"] != "AACCOUNT"
                      and
                      Data
                    [@Name="SubjectUserName"] != "EACCOUNT"
                 )
                ]
        ]
    </Select>
  </Query>
</QueryList>
"@
0
 
LVL 25

Author Closing Comment

by:Sekar Chinnakannu
ID: 40353855
Thanks Phil, Its working Fine, I really appreciate your way of response and help.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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