Export specific attributes from security log

This topic contains 19 replies, has 2 voices, and was last updated by  Meddy Med 1 year, 12 months ago.

  • Author
    Posts
  • #31128

    Meddy Med
    Participant

    Hi guys, so basically my overall goal is to query 5 servers and export a list of users who accessed the machine out : Targetusername, IP address/source computer/ time and out to spread sheet. it would be helpful to only get back the last time a user connected.

    I have this following script that I have been hammering out for a while now (I am trying to learn powershell) you will see that from my script as I am building on it.

    _________________________________________________________

    Write-Host """

    Welcome
    Using this tool can help you search Event Logs!
    you can search by Machine, Logs, Event ID
    """

    #Variables______________________________

    $file= '\\gtnyfile1\shared\Old Data\export1.csv'
    $server = 'GTHQENGREMOTE1'
    $logs = 'security'
    $eventid = '4624'

    #Variables______________________________

    write-host ""
    write-host ""
    Write-Host 'checking ' $logs 'logs on ' $server ' for eventid' $eventid

    $event = Get-EventLog -LogName $logs -InstanceId $eventid -ComputerName $server | Select-Object TimeGenerated,`
    @{Name='TargetUserName';Expression={$_.ReplacementStrings[5]}},`
    @{Name='LogOnType';Expression={$_.ReplacementStrings[8] -eq 10}},`
    @{Name='Ip Address';Expression={$_.ReplacementStrings[18]}},`
    @{Name='Source Computer';Expression={$_.ReplacementStrings[13]}}

    foreach ($i in $event){
    if(($i.ReplacementStrings[10] -eq '10') -Or ($i.ReplacementStrings[7] -eq '7')){
    write-host 'found a 10 or 7'

    }
    else{Continue}

    }

    ______________________________________________

  • #31140

    Cyril Muffat
    Participant

    hi,

    i don't quite get what you want to achive with your foreach.

    here un example of a simple query :

    $query=@{
    date = (Get-Date).AddHours(-3)
    id = 4624
    level = 0
    logname = "security"
    pc = $env:COMPUTERNAME
    }
    Get-WinEvent -ComputerName $query.pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 1

    a good way to deeply search event log is to use xml

    $log = Get-WinEvent -ComputerName $query.pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 1
    [xml]$xmllog = $log.toXml()
    

    to acces the data

    $xmllog.event.eventdata.data
    $xmllog.event.eventdata.data[0].SubjectUserSid

    you can find more information about handling event and xml event here
    http://blogs.technet.com/b/ashleymcglone/archive/2013/08/28/powershell-get-winevent-xml-madness-getting-details-from-event-logs.aspx

  • #31154

    Meddy Med
    Participant

    Hi Cyril, what i was trying to do was filter and return only logon types of 7 or 10 for interactive remote logins, now when i use get-winevent I get cannot RPC for a remote machine however when I use get-eventlog that works.

    Okay so I tried the following code from you:
    ______
    $query=@{
    date = (Get-Date).AddHours(-3)
    id = 4624
    level = 0
    logname = "security"
    pc = $env:COMPUTERNAME
    }

    $log = Get-WinEvent -ComputerName $query.pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 10
    [xml]$xmllog = $log.toXml()

    ______

    Here was the error I got:

    Cannot convert value "System.Object[]" to type "System.Xml.XmlDocument". Error: "This document already has a 'DocumentElement' node."
    At C:\Users\amoustafada\Desktop\SecELexport.ps1:10 char:1
    + [xml]$xmllog = $log.toXml()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

  • #31158

    Cyril Muffat
    Participant

    the xml error must be because your query return no object, try to increase the time

    date = (Get-Date).AddDays(-3)
  • #31159

    Meddy Med
    Participant

    it only works when I have -maxevents set to 1 if I increase it, it returns that error no matter how many -hours I include tried -40! is that neccesarry? I don't want to limit the date.
    I only want to return the time when the event was generated. Also what does Level do?

    by the way Thank you very much for taking your time to help me! I owe you a lunch if you are or every in NYC!

  • #31160

    Meddy Med
    Participant

    Here is what I am using now and still same error:

    $query=@{
    id = 4624
    level = 0
    logname = "security"
    pc = $env:COMPUTERNAME
    }

    $log = Get-WinEvent -ComputerName $query.pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id} -MaxEvents 10
    [xml]$xmllog = $log.toXml()

  • #31172

    Cyril Muffat
    Participant

    ok the probleme lies with the fact that by defining -maxevent 10 you have to treat this as an array

    $query=@{
    date = (Get-Date).AddHours(-10)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 10)
    
    [xml[]]$xmllog = $log.toXml()
    
    $xmllog[0].event.eventdata.data
    
    

    and thanks for the offer but i think it will be a bit difficult, i ll have to take a flight form Paris ;o

  • #31175

    Meddy Med
    Participant

    Haha Maybe Ill order food and ship to you from your local restaurant! we will make it work! "if there is a will, there is a way" haha

    So – this worked but leads me to a few other questions:

    1- when I try to output : $xmllog.event.eventdata.data ( it lists ALL the events one after another). I want it to return only logon types 7/10 and be able to export this data into a spreadsheet : timegenerated, userwhologedin, machinetheyused

    * if you can provide me good sources to learn how to do this stuff to be a master like you 🙂 would greatly appreciate it!

  • #31176

    Cyril Muffat
    Participant

    i m absolutly no master, just a padawan learner.

    a good trick is "show-object" when you want to find details it works with API,Xml,.....

    $xmllog[0].event.eventdata.data give you the first event
    $xmllog[0].event.eventdata.data[0] give you the first property of the first event
    $xmllog[0].event.eventdata.data[0].'#text' will give you the VALUE of the first property of the first event

    so if you want to test the logon type you retrieve it with
    $xmllog[0].event.eventdata.data[8].'#text'

    then just do a loop on $xmllog[$i] with the test inside and wahtever you want to do

  • #31177

    Meddy Med
    Participant

    Here is my take on it! which didn't work because it kept saying 'found a 7 or 10 for all 10' which i know is false:

    $query=@{
    date = (Get-Date).AddHours(-40)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 10)

    [xml[]]$xmllog = $log.toXml()

    foreach ($i in $xmllog) {
    $a = $xmllog[0].event.eventdata.data[8].'#text'
    if(($a = '7') -or ($a = '10')){
    write-host 'found a 7 or 10'

    }else{write-host 'couldnt find a 7 or 10'}
    }

  • #31179

    Cyril Muffat
    Participant

    test this

    foreach ($i in $xmllog) {
    [int32]$a = $i.event.eventdata.data[8].'#text'
    if(($a -eq '7') -or ($a -eq '10')){
    write-host 'found a 7 or 10'
    
    }else{write-host 'couldnt find a 7 or 10'}
    }
    
  • #31182

    Meddy Med
    Participant

    okay so this work now I am trying to make it write to a file and append the data, I wrote the desired data that I want to pull out however I think the time will give me an issue because I need to format it.

    $query=@{
    date = (Get-Date).AddHours(-40)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date})

    [xml[]]$xmllog = $log.toXml()

    foreach ($i in $xmllog) {
    [int32]$a = $i.event.eventdata.data[8].'#text'
    if(($a -eq '7') -or ($a -eq '10')){
    $i.Event.System.Computer
    $i.Event.System.TimeCreated
    $i.Event.EventData.Data[5].'#text'
    $i.Event.EventData.Data[8].'#text'
    $i.Event.EventData.Data[18].'#text'
    }else{continue}

    }

    I am looking into a different issue not relating to the script, I think because when I actually run the script on the server that I need to run on gives me an error, but it works on the machine I am creating it on. I cannot type a remote PC name because RPC is disabled and get-winevent doesn't work get-eventlog does though.

  • #31184

    Cyril Muffat
    Participant

    Q1. Do you have the same WMF on the server and your test machine ?

    Q2. Try this for remote (replace $pc = servername and Administrator by our account) and execute the script on your local shell

    $query=@{
    date = (Get-Date).AddHours(-10)
    id = 4624
    level = 0
    logname = "security"
    }
    #$pc = $env:COMPUTERNAME
    $pc ="ServerName"
    
    $cred = Get-Credential administrator
    
    $log = (Get-WinEvent -ComputerName $pc -Credential $cred -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 10)
    
    [xml[]]$xmllog = $log.toXml()
    
    foreach ($i in $xmllog) {
        [int32]$a = $i.event.eventdata.data[8].'#text'
        if(($a -eq '7') -or ($a -eq '10')){
            write-host 'found a 7 or 10'
    
        }else{write-host 'couldnt find a 7 or 10'}
    }
    
    
    
    		
    	
  • #31185

    Meddy Med
    Participant

    well one the server i am running script on its version 4.0 and the server I need to run it on is 2.0 could that be a factor?

    I tried the code you provided with the changes I needed to make and same issue.

    I also modified my prior comment about the script code, sorry for the confusion sir!

  • #31190

    Cyril Muffat
    Participant

    well, sad news for you, .toXml won't work on your target server due to the PSversion.

    so, you have two choice :
    1. update the WMF on the target server
    2. use get-event and forget the xml part

    Why forget the xml part with get-event because the main info you are looking for is in message property which is a bulk string and to my knowlege convertto-xml won't be able to split the info.

    so what to do : use the get-event and then use a -match test on .message property of your get-event.

    it will be a dirty output but at least you will still get your info. the great thing with powershell is to be able to handle object, when it comes to strings prepare to suffure a bit .

       $v=@(get-eventlog -LogName security -instanceID 4624 -newest 10)
       foreach ($eventlog in $v){
        
            if ($eventlog.message -match "Type d'ouverture de session :		7"){
                write-host "remote"
                }else{
                    Write-Host " not remote"
            }
        }
    
  • #31193

    Meddy Med
    Participant

    yeah, which probably will not work for me I think, because I just tried to run what you just gave me and it returned nothing...which is weird.

    so I think I will be updating all to v4 anyway as they need and should be updated.

    so going back to the original script.

    I am trying to make it write to a file and append the data, I wrote the desired data that I want to pull out however I think the time will give me an issue because I need to format it.

    $query=@{
    date = (Get-Date).AddHours(-40)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date})

    [xml[]]$xmllog = $log.toXml()

    foreach ($i in $xmllog) {
    [int32]$a = $i.event.eventdata.data[8].'#text'
    if(($a -eq '7') -or ($a -eq '10')){
    $i.Event.System.Computer
    $i.Event.System.TimeCreated
    $i.Event.EventData.Data[5].'#text'
    $i.Event.EventData.Data[8].'#text'
    $i.Event.EventData.Data[18].'#text'
    }else{continue}

    }

    what do you think?

  • #31195

    Cyril Muffat
    Participant

    for get-eventlog

    you need to change the string "Type d'ouverture de session : 7" as its in french

    
    if(($a -eq '7') -or ($a -eq '10')){
    $array += New-object  PSObject -Property([ordered]@{"comp" = $i.Event.System.Computer;
    "Time"=$i.Event.System.TimeCreated;
    "5"=$i.Event.EventData.Data[5].'#text';
    "8"=$i.Event.EventData.Data[8].'#text';
    "18"=$i.Event.EventData.Data[18].'#text'
    })
    
    }else{continue}
    
    $array | Export-Csv -NoTypeInformation -Delimiter ";" -Path ./test.csv
    
    
  • #31196

    Meddy Med
    Participant

    I got the following Error:

    Method invocation failed because [System.Management.Automation.PSObject] does not contain a method named 'op_Addition'.
    At C:\Users\amoustafada\Desktop\SecELexport.ps1:15 char:9
    + $array += New-object PSObject -Property([ordered]@{"comp" = $i.Event.Sy ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (op_Addition:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

    :: this is my code::
    ___________________
    ___________________
    $query=@{
    date = (Get-Date).AddHours(-40)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 5000 )

    [xml[]]$xmllog = $log.toXml()

    foreach ($i in $xmllog) {
    [int32]$a = $i.event.eventdata.data[8].'#text'
    if(($a -eq '7') -or ($a -eq '10')){
    $array += New-object PSObject -Property([ordered]@{"comp" = $i.Event.System.Computer;
    "Time"=$i.Event.System.TimeCreated;
    "5"=$i.Event.EventData.Data[5].'#text';
    "8"=$i.Event.EventData.Data[8].'#text';
    "18"=$i.Event.EventData.Data[18].'#text'
    })}else{continue}

    $array | Export-Csv -NoTypeInformation -Delimiter ";" -Path ./test.csv

    }

  • #31203

    Meddy Med
    Participant

    Hmm okay so I removed the + sign, and that made the script run without errors....but 🙁

    it outputted one line only so I added the -append to the export-csv

    which worked however ALL is in one column -_-!
    not sure why..

  • #31217

    Meddy Med
    Participant

    Soooo I tried a lot of things lol.....been at this for a while now here is my final code and I think its working when I took out the delimiter it seperated the file so my final code is this below...now two more last tasks that I need to work on, is the time!! it shows up in excel like this "System.Xml.XmlElement" and I need it to show the correct time. the other thing is I only want it to show the user one time, so it grabs only the first event for the user and if it repeats to ignore it and keep going.

    here is my code:

    $query=@{
    date = (Get-Date).AddHours(-40)
    id = 4624
    level = 0
    logname = "security"
    }
    $pc = $env:COMPUTERNAME
    $log = (Get-WinEvent -ComputerName $pc -FilterHashTable @{LogName=$query.logname; Level=$query.level; id=$query.id; starttime = $query.date} -MaxEvents 5000 )

    [xml[]]$xmllog = $log.toXml()

    foreach ($i in $xmllog) {
    [int32]$a = $i.event.eventdata.data[8].'#text'
    if(($a -eq '7') -or ($a -eq '10')){
    $array = New-object PSObject -Property([ordered]@{`
    "Target Computer" = $i.Event.System.Computer
    "Time"=$i.Event.System.TimeCreated
    "User Logged In"=$i.Event.EventData.Data[5].'#text'
    "Logon Type"=$i.Event.EventData.Data[8].'#text'
    "Ip Address"=$i.Event.EventData.Data[18].'#text'
    })

    }
    else{continue}

    $array |Export-Csv -NoTypeInformation C:\test.csv -Append

    }

    Thank you very much! if you have a paypal email please send me 🙂

You must be logged in to reply to this topic.