topic Can you help me compare two searches and then print the difference? in Splunk Search

Can you help me compare two searches and then print the difference?

atyshke1 — Tue, 09 Oct 2018 07:20:53 GMT

Hello,

I am using two searches for seeking two windows events 4732 and 4733. I want to print into a new table events which registered only one of two events. For example, usually when local admin add in any groups at servers windows, a security event with number 4732 is created. And after deleting this user, Windows creates the new event 4733.

I want to try find in events 4732 or 4733, which ones registered only in one of events 4732 or 4733.

I used search:

(source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 ) OR (source="WinEventLog:Sec*" index="wineventlog" EventCode=4733) | rex field=_raw "Message=(?\S+.*)" | eval Description=Descript | rex field=_raw "Subject:\s+.*\s+Account Name:\s+(?\S+)" | eval CDSID=UserName | rex field=_raw "Member:\s+Security ID:\s+(?\S+)" | eval MCDSID=MemberName | rex field=_raw "Group:\s+.*\s+Group Name:\s+(?\S+.*)" | eval LocalGroup=GName | rename host as Host EventCode as "Event Code" MCDSID as "Member Name" LocalGroup as "Local Group" _time as Date | stats Count by Host, "Event Code", Description, CDSID, "Member Name", "Local Group", Date | fieldformat Date =strftime(Date,"%x %X") | table Host, "Event Code", Description, CDSID, "Member Name", "Local Group", Date, Count

How can I compare and print only unique event which not the same account name in both events 4732 and 4733 at the same time?

Re: Can you help me compare two searches and then print the difference?

jlelli — Tue, 09 Oct 2018 10:33:59 GMT

If i understood correctly you need to find the "orphaned" accounts that do not have BOTH events 4732 and 4733.
In this case i can suggest simply to group by account | stats list(EventCode) by "Account Name" | where mvcount(EventCode)<2
Obviously this will work only if the accounts and Event Codes are uniques (no single account can have more than 1 event 4732 and 1 4733 event)

If this is not the case then you need first to group the events in different transactions, then apply the evaluation: something like | transaction Host "Account Name" startswith=4732 endswith=4733 | where mvcount(EventCode)<2 | table _time "Account Name" EventCode

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Tue, 09 Oct 2018 11:55:40 GMT

Yes, you are right, I need to find the "orphaned" accounts. I tried both codes and didn't work

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Tue, 09 Oct 2018 12:56:28 GMT

The first code is work. But the second not.
I use next search:

(source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 ) OR (source="WinEventLog:Sec*" index="wineventlog" EventCode=4733) | rex field=_raw "Message=(?<Descript>\S+.*)" | eval Description=Descript | rex field=_raw "Subject:\s+.*\s+Account Name:\s+(?<UserName>\S+)" | eval CDSID=UserName | rex field=_raw "Member:\s+Security ID:\s+(?<MemberName>\S+)" | eval MCDSID=MemberName | rex field=_raw "Group:\s+.*\s+Group Name:\s+(?<GName>\S+.*)" | eval LocalGroup=GName | rename host as Host EventCode as "Event Code" MCDSID as "Member Name" LocalGroup as "Local Group" _time as Date | fieldformat Date =strftime(Date,"%x %X") | transaction Host "Member Name" startswith="4732" endswith="4733" | where mvcount("Event Code")<2 | table Date, "Member Name", "Event Code"

Re: Can you help me compare two searches and then print the difference?

jlelli — Tue, 09 Oct 2018 12:59:25 GMT

I'm not familiar with Windows Security Logs and your query uses a logic that i don't fully understand; if you need a test string i need a sample of the event.

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Tue, 09 Oct 2018 13:07:58 GMT

This is example of event code 4732:

09/28/2018 02:42:44 PM
LogName=Security
SourceName=Microsoft Windows security auditing.
EventCode=4732
EventType=0
Type=Information
ComputerName=ser02056.servers.fo.com
TaskCategory=Security Group Management
OpCode=Info
RecordNumber=17799424
Keywords=Audit Success
Message=A member was added to a security-enabled local group.

Subject:
Security ID: FOEU1\$LKRUD
Account Name: $LKRUD
Account Domain: FOEU1
Logon ID: 0x72857CA

Member:
Security ID: FOEU1\AAGURI
Account Name: -

Group:
Security ID: BUILTIN\Administrators
Group Name: Administrators
Group Domain: Builtin

Additional Information:
Privileges: -

The log for 4733 is the same. Different only with description "Message=A member was added to a security-enabled local group."

Re: Can you help me compare two searches and then print the difference?

jlelli — Tue, 09 Oct 2018 14:23:02 GMT

I don't understand the reason for all the field extraction, eval and renaming; so i took some liberties in the query. Try instead:

source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 OR EventCode=4733 
| rex "Security ID:\s+(?<MemberName>.*)" 
| rex "Account Name:\s+(?<UserName>.*)"  
| rex "Group Name:\s+(?<GName>.*)"  
| transaction host MemberName startswith="4732" endswith="4733"
| where mvcount(EventCode)<2 
| eval Date=strftime(_time, "%d/%y") 
| table Date MemberName EventCode 
| rename MemberName as "Member Name" EventCode as "EventCode"

If the Windows Security ID has matches this should work, otherwise you can try to match the Account Name replacing the transaction and table lines with:

| transaction host UserName startswith="4732" endswith="4733" 

| table Date, UserName, EventCode

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Tue, 09 Oct 2018 14:23:03 GMT

The first code is working. But the is second not.
I use next search:

(source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 ) OR (source="WinEventLog:Sec*" index="wineventlog" EventCode=4733) | rex field=_raw "Message=(?<Descript>\S+.*)" | eval Description=Descript | rex field=_raw "Subject:\s+.*\s+Account Name:\s+(?<UserName>\S+)" | eval CDSID=UserName | rex field=_raw "Member:\s+Security ID:\s+(?<MemberName>\S+)" | eval MCDSID=MemberName | rex field=_raw "Group:\s+.*\s+Group Name:\s+(?<GName>\S+.*)" | eval LocalGroup=GName | rename host as Host EventCode as "Event Code" MCDSID as "Member Name" LocalGroup as "Local Group" _time as Date | fieldformat Date =strftime(Date,"%x %X") | transaction Host "Member Name" startswith="4732" endswith="4733" | where mvcount("Event Code")<2 | table Date, "Member Name", "Event Code"

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Wed, 10 Oct 2018 05:13:35 GMT

I don't understand the reason for all the field extraction, eval and renaming
I need extract the fields which has contains in only one of two events 4732 or 4733 for accounts
I want report if account in 4732 or 4733 that allow me understand that account was added or deleted. If event for account registered in 4732 and 4733 that told is ok. Account was added and deleted. But if account only in one of 4732 or 4733 that is tell us we need to check why the account registered in only one event 4732 or 4733

I tried the search code:
source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 OR EventCode=4733 | rex "Security ID:\s+(?.*)" | rex "Account Name:\s+(?.*)" | rex "Group Name:\s+(?.*)" | transaction host MemberName startswith="4732" endswith="4733" | where mvcount(EventCode)<2 | eval Date=strftime(_time, "%d/%y") | table Date MemberName EventCode | rename MemberName as "Member Name" EventCode as "EventCode"

but it doesn't show me anything

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Wed, 10 Oct 2018 11:42:47 GMT

Now I use this code:
source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 OR EventCode=4733 | rex field=_raw "Message=(?\S+.*)" | rex field=_raw "Subject:\s+.*\s+Account Name:\s+(?\S+.*)" | rex field=_raw "Member:\s+Security ID:\s+(?\S+.*)" | rex field=_raw "Group:\s+.*\s+Group Name:\s+(?\S+.*)" | transaction host MemberName startswith=4732 endswith=4733 | where mvcount(MemberName)<2 | fieldformat Date=strftime(Date,"%x %X") | table host, EventCode, Descript, UserName, MemberName, GName, _time | rename host as Host EventCode as "Event Code" MemberName as "Member Name" GName as "Local Group" _time as Date Descript as Description UserName as CDSID

And it seems that it's work but it's work incorrect. For example, I know that first event in a pictures has in 4732 and 4733 events, but why search it show me??

Re: Can you help me compare two searches and then print the difference?

jlelli — Wed, 10 Oct 2018 13:03:53 GMT

Please note that in the code i provided the verification of the single event is made by:
| transaction host MemberName startswith="4732" endswith="4733" (this creates a single event from the coupling of member-add / member-remove)
| where mvcount(EventCode)<2 (this excludes all the paired events, showing only the orphaned ones)

In your search your have replaced the where command with | where mvcount(MemberName)<2 and since the MemberName field is unique the filter does not work. Replace this with the original | where mvcount(EventCode)<2 and it should work fine.

If no records are shown is because there are no Orphaned events, so try to change the time slice of the search or to increase the search range.

Re: Can you help me compare two searches and then print the difference?

atyshke1 — Wed, 10 Oct 2018 13:15:12 GMT

In your search your have replaced the where command with | where mvcount(MemberName)<2 and since the MemberName field is unique the filter does not work. Replace this with the original | where mvcount(EventCode)<2 and it should work fine.

Yeah, yeah you are right
Thank you very much for excellent help!

I use the next code and it seems works fine:
source="WinEventLog:Sec*" index="wineventlog" EventCode=4732 OR EventCode=4733 | rex field=_raw "Message=(?\S+.*)" | rex field=_raw "Subject:\s+.*\s+Account Name:\s+(?\S+.*)" | rex field=_raw "Member:\s+Security ID:\s+(?\S+.*)" | rex field=_raw "Group:\s+.*\s+Group Name:\s+(?\S+.*)" | transaction host MemberName | where mvcount(EventCode)<2 | fieldformat Date=strftime(Date,"%x %X") | table host, EventCode, Descript, UserName, MemberName, GName, _time | rename host as Host EventCode as "Event Code" MemberName as "Member Name" GName as "Local Group" _time as Date Descript as Description UserName as CDSID