Solved

Customize the powershell script

Posted on 2014-09-18
22
466 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
  • 11
  • 6
  • 5
22 Comments
 
LVL 18

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 24

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 18

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
 
LVL 24

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 18

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 18

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 24

Author Comment

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

Expert Comment

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

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 24

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 24

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 24

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 24

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 24

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 24

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 24

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

758 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

21 Experts available now in Live!

Get 1:1 Help Now