Link to home
Start Free TrialLog in
Avatar of Sekar Chinnakannu
Sekar ChinnakannuFlag for Singapore

asked on

Customize the powershell script

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
SOLUTION
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

After adding the part which you mentioned I am not getting prompt to enter date. Could you please check and  update
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image
why do you want to enter the date? You mean you want reports to be generated from that date?
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

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.
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image
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

Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image
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

Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

Can you also help on weekly and monthly script
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image
does that work ?
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

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
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

___________________________________________________________________________________________________________________________________
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
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
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

Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

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
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
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
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

@ Phil - script is running and creating the output file, no data inside the output file.
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
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

Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

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.
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
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.
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

@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
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
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

Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

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>
"@
ASKER CERTIFIED SOLUTION
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sekar Chinnakannu
Sekar Chinnakannu
Flag of Singapore image

ASKER

Thanks Phil, Its working Fine, I really appreciate your way of response and help.