MV(filter) with Logon Events Info

[sbgoldberg13]

I'd like to filter all records with NULL SID.
For every valid record returned a NULL SID record is returned as well. When I try to filter with !="NULL SID" it filters both records.
I've tried working with MV but not getting anywhere.

Any suggestions appreciated! Thanks!

Search:
index="wineventlog" EventCode=4624
| stats count as "Number of Logins" by Security_ID Source_Network_Address
| rename Security_ID as "Account Name", Source_Network_Address as "Login IP"
| table "Account Name" "Login IP" "Number of Logins"
| sort "Account Name"

Results:
Account Name Login IP Number of Logins
XYZ\John.Doe ip address1 1
XYZ\Jane.Doe ip address2 1
XYZ\Tom.Smith ip address3 1
NULL SID ip address1 1
NULL SID ip address2 1
NULL SID ip address3> 1

Raw Event Excerpt:
Keywords=Audit Success
Message=An account was successfully logged on.

Subject:
Security ID: NULL SID
Account Name: -
Account Domain: -
Logon ID: 0x0

Logon Type: 3

Impersonation Level: Impersonation

New Logon:
Security ID: XYZ\John.Doe
Account Name: John.Doe
Account Domain: XYZ
Logon ID: 0x38xxx53F
Logon GUID: {4CC2EC1A-F0A1-A0F1-E7AE-XXXXXXXXXXXX}

[lakshman239]

A 4624 can have "NULL SID" for Security ID under "Subject", but have a non-null value for the same Security ID under "New Logon" for the same event. In such cases, "Account Name"!="NULL SID" will show as records removed for 'stats' command, but the underlying raw data is same.

index=wineventlog EventCode=4624 Logon_Type=3 "take a particular src_ip here" | your search | addcoltotals | search "Account Name" !="NULL SID"

you can test the count with addcoltotals to see that the underlying raw event is same

[woodcock]

I think that what is happening is that the Account field is ending up multi-valued. If so, this will work:

| makeresults 
| eval Account="XYZ\John.Doe,XYZ\Jane.Doe,XYZ\Tom.Smith,NULL SID,NULL SID,NULL SID" 
| makemv Account delim="," 
| mvexpand Account 
| appendcols 
    [| makeresults 
    | eval Login="ip address1,ip address2,ip address3,ip address1,ip address2,ip address3" 
    | makemv Login delim="," 
    | mvexpand Login] 
| appendcols 
    [| makeresults 
    | eval NoOfLogins="1,1,1,1,1,1" 
    | makemv NoOfLogins delim="," 
    | mvexpand NoOfLogins] 
| stats values(Account) AS Account BY Login

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval Account=mvfilter(NOT match(Account, "NULL SID"))
| where isnotnull(Account)

[renjith_nair]

@sbgoldberg13,

If you just want the first 3 records from your results , below should work - assuming "NULL SID" is a string and not real null()

index="wineventlog" EventCode=4624
| stats count as "Number of Logins" by Security_ID Source_Network_Address
| rename Security_ID as "Account Name", Source_Network_Address as "Login IP"
| table "Account Name" "Login IP" "Number of Logins"
| where "Account Name" != "NULL SID"

If the above does not work, please lets know what the expected output. Used below search to test it

|makeresults|eval Account="XYZ\John.Doe,XYZ\Jane.Doe,XYZ\Tom.Smith,NULL SID,NULL SID,NULL SID"|makemv Account delim=","|mvexpand Account
|appendcols [|makeresults|eval Login="ip address1,ip address2,ip address3,ip address1,ip address2,ip address3"|makemv Login delim=","|mvexpand Login]
|appendcols [|makeresults|eval NoOfLogins="1,1,1,1,1,1"|makemv NoOfLogins delim=","|mvexpand NoOfLogins]
|rename COMMENT as "---All above is for dummy data generation---"
|where Account!="NULL SID"

[sbgoldberg13]

I'll try, but I'm not looking for the first 3. There's hundreds of records that are duplicated with a NULL SID record. I want to eliminate all the duplicate records but not all of the records. Thanks.