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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...