Splunk Search

How to use Macro's and CSV Lookup to combine results into one table based on conditions?

Simple_Search
Path Finder

Hey ya'll - I am attempting to create an efficient search to detect password compromises within some environments, the map command is very intensive and clicking to pass tokens is not automated, I was wondering if there are other solutions. Below I have 2 macros, 1 which will detect failed logons based on the account_name field greater than 13. The second will pull all of the successful logins from a keyboard. Using a saved-search would also be acceptable.

My goal is to have a result from these two macros that will provide the host name where the compromise occurred, the time when the initial password compromise occurred, and which user, if any, within 120 seconds successfully logged onto that same machine where the compromise occurred.  This seems to be a simple solution but cant seem to put two and two together. Appreciate the support!!!

Macro 1 - password_compromise

 

index=wineventlog source="wineventlog:security" "logname=security" (EventCode=4625 Logon_Type=2) 
| eval newtime=_time+120
| eval oldtime=_time
| eval Account_Name = mvfilter(Account_Name!="-" AND NOT match(Account_Name,"\$$"))
| eval number=len(Account_Name)
| eval status=case(number>13,"Potential Password Compromise", number<=9,"OK")
| where status="Potential Password Compromise"
| rename Account_Name as "Potential Password?" 
| sort -_time 
| table _time host "Potential Password?" oldtime newtime status 
| outputcsv passwordcompromise_initial.csv

 


Macro 2 - password_logon

 

index=wineventlog source="wineventlog:security" "logname=security" (EventCode=4624 LogonType=2)
| eval AccountName = mvfilter(NOT match(AccountName, "-") AND NOT match(AccountName, "DWM*") AND NOT match(AccountName,"\$$$$"))
| eval logontime=time
| dedup host, AccountName, logontime
| where AccountName!=""
| rename AccountName as "AuthenticatedUser"
| table time host "AuthenticatedUser" logontime
| outputcsv password_successfullogon.csv

 

 

Labels (3)
Tags (4)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Based on your description, the only information the second search needs from the first search is host, the time the host got compromised, and 120 seconds after that time.  So, if the first search is already run, the most straight-forward solution would be a subsearch using the first CSV file.  Like

index=wineventlog source="wineventlog:security" "logname=security" (EventCode=4624 LogonType=2)
    [| inputlookup passwordcompromise_initial.csv
    | fields host newtime oldtime
    | rename newtime as latest, oldtime as earliest]
| eval AccountName = mvfilter(NOT match(AccountName, "-") AND NOT match(AccountName, "DWM*") AND NOT match(AccountName,"\$$$$"))
| eval logontime=time
| dedup host, AccountName, logontime
| where AccountName!=""
| rename AccountName as "AuthenticatedUser"
| table time host "AuthenticatedUser" logontime
| outputcsv password_successfullogon.csv

Of course, if the first one hasn't run, and you want to take one execution to obtain two CSVs, you can just combine the two.

index=wineventlog source="wineventlog:security" "logname=security" (EventCode=4624 LogonType=2)
    [search index=wineventlog source="wineventlog:security" "logname=security" (EventCode=4625 Logon_Type=2)
    | eval newtime=_time+120
    | eval oldtime=_time
    | eval Account_Name = mvfilter(Account_Name!="-" AND NOT match(Account_Name,"\$$"))
    | eval number=len(Account_Name)
    | eval status=case(number>13,"Potential Password Compromise", number<=9,"OK")
    | where status="Potential Password Compromise"
    | rename Account_Name as "Potential Password?"
    | sort -_time
    | table _time host "Potential Password?" oldtime newtime status
    | outputcsv passwordcompromise_initial.csv
    | fields host newtime oldtime
    | rename newtime as latest, oldtime as earliest]
| eval AccountName = mvfilter(NOT match(AccountName, "-") AND NOT match(AccountName, "DWM*") AND NOT match(AccountName,"\$$$$"))
| eval logontime=time
| dedup host, AccountName, logontime
| where AccountName!=""
| rename AccountName as "AuthenticatedUser"
| table time host "AuthenticatedUser" logontime
| outputcsv password_successfullogon.csv

Hope this helps.

Tags (1)
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...