topic Re: Lookup doesn't return any data in Splunk Search

Lookup (KVStore) doesn't return any data?

thoma1 — Thu, 10 Nov 2022 15:23:35 GMT

Can't seem to get this lookup(KVstore) to function.
The dataset is from active directory in some cases in the same event the user field isn't populated with the sam account data but rather with the accounts sid.
I'm trying to enrich the data by applying a lookup that has many fields of information regarding each user so that a table has useable information from the event such as the user name.
below is the search query:

index=wineventlog source=WinEventLog:Security [| inputlookup AD_Audit_Change_EventCodes WHERE change_category="Group Membership" | stats values(EventCode) AS EventCode by obj_type | format | table search] src_user_type="user" | rex field=member_obj_sam "(?<account_sid>\C-\C-\C-\C\C-[0-9]+-[0-9]+-[0-9]+-[0-9]+)" | lookup AD_Obj_User sAMAccountName as src_user output displayName as "Admin Display Name" | lookup AD_Obj_User sid_lookup as account_sid output displayName as "Account Display Name" | lookup AD_Obj_User sAMAccountName as member output displayName as "Member Display Name" | lookup CORP_Monitored_Security_Groups_def CORP_group_name as Group_Name output CORP_group_name | search CORP_group_name=* | table _time, "Admin Display Name", src_user, Group_Name, msad_action, member_obj_sam, "Member Display Name", MSADGroupClass, MSADGroupType, src_nt_domain, host | rename src_user as "Admin Account",MSADGroupClass as "Type",MSADGroupType as "Scope",src_nt_domain as "Domain",Group_Name as "Group Modified",msad_action as "Action",member_obj_sam as "Member" | sort -_time

The lookups and rex:

#This works correctly and a new field(account_sid) has the expected data: e.g s-0-0-00-0000000000-0000000000-00000000-000000

| rex field=member_obj_sam "(?<account_sid>\C-\C-\C-\C\C-[0-9]+-[0-9]+-[0-9]+-[0-9]+)"

#this works as expected

| lookup AD_Obj_User sAMAccountName as src_user output displayName as "Admin Display Name"

# this does not work!

| lookup AD_Obj_User sid_lookup as account_sid output displayName as "Account Display Name"

#this works as expected

| lookup AD_Obj_User sAMAccountName as member output displayName as "Member Display Name"

#if i perform the following i get results i expect in a new search window

| inputlookup AD_Obj_User | search sid_lookup="s-0-0-00-0000000000-0000000000-00000000-000000"

I'm not sure if I've met a lookup limit or if there is an obvious error in the query itself, but i can't see anything in offical litreture as to what is going wrong

Re: Lookup doesn't return any data

yuanliu — Thu, 10 Nov 2022 07:42:52 GMT

The first piece of information you need to provide is how does AD_Obj_User look like, i.e., illustrate the headers and some sample rows (anonymize as necessary but illustrate any data pattern you use in your search). Secondly, your question appears to be related to AD_Obj_User. But why do you have

[| inputlookup AD_Audit_Change_EventCodes WHERE change_category="Group Membership"
| stats values(EventCode) AS EventCode by obj_type
| format
| table search]

in the search? You didn't explain anything about AD_Audit_Change_EventCode, either. What does it contain? Is this subsearch even relevant to the main question? (Remember, this is a Splunk forum, not an AD forum. Do not expect others to be familiar with any application specific information.)

Third, "doesn't work" is a phrase to be avoided. It conveys no information in the best of situations. What is to be expected from those commands, and what is the actual outcome? Again, illustration of actual data is critical. If that subsearch involving AD_Audit_Change_EventCodes is essential to troubleshoot your difficulty with AD_Obj_User, you need to illustrate data after applying the subsearch.

Speaking of something obvious, SPL uses PCRE; your regular expression contains XML-specific notation "\C-\C-\C-\C\C-[0-9]+-[0-9]+-[0-9]+-[0-9]+". Do you mean to say "\w-\w-\w-\w\w-[0-9]+-[0-9]+-[0-9]+-[0-9]+"?

Re: Lookup doesn't return any data

bowesmana — Thu, 10 Nov 2022 08:24:24 GMT

Also try this, just to prove that the lookup command as written works and you get a "Account Display Name" field with the appropriate name

| makeresults | eval account_sid="s-0-0-00-0000000000-0000000000-00000000-000000" | lookup AD_Obj_User sid_lookup as account_sid output displayName as "Account Display Name"

so when you say that doesn't work - are you saying you just don't get the name?

Re: Lookup doesn't return any data

thoma1 — Thu, 10 Nov 2022 14:51:02 GMT

Thanks both for your posts:
To add more context this lookup is created and maintained by the splunk app "MS Windows AD Objects"

@bowesmana:
Apologies i know "it doesn't work" is non-descriptive. I get no errors executing the search with any of the lookup statements, it just does not return any fields from the lookup from the below:

| lookup AD_Obj_User sid_lookup as account_sid output displayName as "DisplayName1"

I performed your search suggestion and the results are what I see with my previous search query. I added a new field with a value that i know exists and works with this lookup and extracted the sid_lookup field to compare the results.

| makeresults | eval account_sid="S-0-0-00-0000000000-0000000000-00000000-000001" | eval account_sam="doej" | lookup AD_Obj_User sid_lookup as account_sid output displayName as "DisplayName1" | lookup AD_Obj_User sAMAccountName as account_sam output displayName as "DisplayName2", sid_lookup as "lookup_account_sid"

Results:

DisplayName1	DisplayName2	_time	account_sam	account_sid	lookup_account_sid
(empty)	John Doe	00:00:00	doej	S-0-0-00-0000000000-0000000000-00000000-000001	S-0-0-00-0000000000-0000000000-00000000-000001

@yuanliu:
For the sake of posterity, please just ignore the complete search query in my original post. I decided to include it so that the entire picture was shared.

There are a total of 26 headers in this lookup that is built by "MS Windows AD Objects".
Here is an example of how some of those fields are populated.

badPwdCount	cn	whenChanged	whenCreated	isDeleted	displayName	sAMAccountName	sid_lookup
0	john doe	00:00:00	00:00:00	no	John Doe	doej	S-0-0-00-0000000000-0000000000-00000000-000001

Below is a simplified search, quering just the lookup in question with the same result:

index=wineventlog source=WinEventLog:Security EventCode=4728 | rex field=member_obj_sam "(?<account_sid>\w-\w-\w-\w\w-[0-9]+-[0-9]+-[0-9]+-[0-9]+") | lookup AD_Obj_User sAMAccountName as src_user output displayName as "Admin Display Name" | lookup AD_Obj_User sid_lookup as account_sid output displayName as "Account Display Name 1" | lookup AD_Obj_User sAMAccountName as member output displayName as "Account Display Name 2"

src_user & member both map correctly and extract the fields requested and rename them. sid_lookup fails to return anything, the field exists in the lookup and has data such as "S-0-0-00-0000000000-0000000000-00000000-000002" performing the below resturns the results in the aforementioned lookup fields example.

|inputlookup AD_Obj_User | search sid_lookup="S-0-0-00-0000000000-0000000000-00000000-000002"

The examples of the events that this search is quering is below, it is the same event type however the data in some of the events is different, one has a human-readable username the other the user's sid(security identifier). The "member_obj_sam" field will be populated with both username and sid, I am using the lookup to return some useful information such as the users full name.

Example 1)

11/10/2022 12:38:46 PM LogName=Security SourceName=Microsoft Windows security auditing. EventCode=4728 EventType=0 Type=Information ComputerName=domain-controller.corp TaskCategory=Security Group Management OpCode=Info RecordNumber=0000000000 Keywords=Audit Success Message=A member was added to a security-enabled global group. Subject: Security ID: CORP\admin Account Name: admin Account Domain: corp Logon ID: 0x000000000 Member: Security ID: CORP\doej Account Name: CN=John Doe,OU=corp-user,OU=Users,DC=corp,DC=com Group: Security ID: CORP\power_users Group Name: power_users Group Domain: CORP

Example 2)

1/04/2022 10:10:53 AM LogName=Security EventCode=4728 EventType=0 ComputerName=domain-controller2.corp SourceName=Microsoft Windows security auditing. Type=Information RecordNumber=0000000000 Keywords=Audit Success TaskCategory=Security Group Management OpCode=Info Message=A member was added to a security-enabled global group. Subject: Security ID: S-0-0-00-0000000000-0000000000-00000000-000002 Account Name: admin Account Domain: CORP Logon ID: 0x000000003 Member: Security ID: S-0-0-00-0000000000-0000000000-00000000-000001 Account Name: N=John Doe,OU=corp-user,OU=Users,DC=corp,DC=com Group: Security ID: S-0-0-00-0000000000-0000000000-00000000-000003 Group Name: auditors Group Domain: CORP Additional Information: Privileges: -

Re: Lookup doesn't return any data

bowesmana — Fri, 11 Nov 2022 00:16:21 GMT

@thoma1

One thing sticks out is that the account_sid you use in your earlier example had a lower case "s", whereas this example and the data have an upper case "S".

If you do "search sid="s-..." then search is case insensitive, whereas the lookup may or may not be case insensitive, depending on how it's configured.

Just a thought...

But if you "search sid="s..." works, then the data is there, but if the lookup itself doesn't, the data is not exactly as being searched.

Also, in your example here, if you do an equality test after the second lookup (for sanity's sake)

| eval same=if(account_sid=lookup_account_sid AND len(account_sid)=len(lookup_account_sid), "SAME", "DIFF")

does it appear to be the same?

I wonder if there are any odd characters in the data in the lookup that are causing it not to match in the lookup.

Re: Lookup doesn't return any data

yuanliu — Fri, 11 Nov 2022 06:09:01 GMT

@thoma1 Based on the results from your emulated lookup, I am convinced that AD_Obj_User is either corrupt, or its sid_lookup column contains characters that is not printed to you but affects outcome. Is it possible that the lookup table is using a different character set from the data source? Maybe the Splunk instance is running in an operating system that doesn't take DOS format nicely?

This is how I reached this conclusion: I took the illustrated portion of the lookup table into a CSV,

badPwdCount,cn,whenChanged,whenCreated,isDeleted,displayName,sAMAccountName,sid_lookup 0,john doe,00:00:00,00:00:00,no,John Doe,doej,S-0-0-00-0000000000-0000000000-00000000-000001

then set this CSV as a lookup named AD_Obj_User. Your emulated lookup gives a different output from what you get using the real table: sid_lookup and account_sid matches as expected.

DisplayName1	DisplayName2	_time	account_sam	account_sid	lookup_account_sid
John Doe	John Doe	2022-11-10 21:46:28	doej	S-0-0-00-0000000000-0000000000-00000000-000001	S-0-0-00-0000000000-0000000000-00000000-000001

Does this make sense? (I am running Splunk 9.0.2 in MacOS; the lookup file is created with vi then uploaded via Splunk Web.)

Re: Lookup doesn't return any data

thoma1 — Fri, 11 Nov 2022 11:27:13 GMT

Thanks for going through the effort. I'm not sure what is occuring. the lookup is a KV Store. I decided to export it with the "splunk app for lookup file editing" as a csv and re-import it as CSV attempting to maintain the same data. To my surprise as a csv lookup that contains the same data and fields. The lookup works as expected.

| inputlookup AD_Obj_User_TEST.csv | search sid_lookup=S-0-0-00-0000000000-0000000000-00000000-000002 | lookup AD_Obj_User_TEST.csv sAMAccountName as sAMAccountName output sid_lookup as "SID via SAM" | lookup AD_Obj_User_TEST.csv sid_lookup as sid_lookup output sid_lookup as "SID via SID" | table "SID via SAM", "SID via SID"

SID via SAM	SID via SID
S-0-0-00-0000000000-0000000000-00000000-000002	S-0-0-00-0000000000-0000000000-00000000-000002

The SH/Idx are both cloud instances so I have little say about the underlying OS

Re: Lookup doesn't return any data

yuanliu — Fri, 11 Nov 2022 11:41:16 GMT

Glad you get to the bottom of the problem. All I can say is that no cloud loves MS-DOS, not even Asure. You should contact the developer of that application.

Re: Lookup doesn't return any data

thoma1 — Fri, 11 Nov 2022 11:43:21 GMT

I thought about it the case sensitivty and wiether it's lower or upper case s doesn't seem to impact the results from the lookup with the search. Performing your equality test with the upper case is the result is SAME with the lower case s the result is DIFF

Re: Lookup doesn't return any data

thoma1 — Fri, 11 Nov 2022 11:46:55 GMT

It's splunkcloud so I believe it's hosted on AWS not Azure.

I think i'm just going to have to bruteforce it and create a CSV with outputlookup that runs regularlly. And see if the dev of that app has any insights as to the issue.

Re: Lookup doesn't return any data

yuanliu — Fri, 11 Nov 2022 11:50:52 GMT

Possible alternative: configure/hack that app to write to CSV file as opposed to KV store to see if that saves some effort.

Re: Lookup doesn't return any data

thoma1 — Fri, 11 Nov 2022 13:04:02 GMT

I would look at that but as it's splunkcloud i have no terminal access to the instance to do that. Roll back would be easier as i could copy the entire app directory and then hack away