topic Re: Why is Splunk search returning additional results not specified in Lookup table? in Splunk Search

Why is Splunk search returning additional results not specified in Lookup table?

hillsmtb7 — Fri, 01 Apr 2022 18:06:54 GMT

I have a query to search particular event id's from Active Directory and see what Targets these apply to. Instead of listing 100 different AD groups, I chose to use a lookup table. My query is as follows:

index=<index name> EventID IN (4728,4729) TargetUserName IN [| inputlookup Test_Splunk_Lookup_Table_v2.csv | return 200 "$Group_Name"] | eval EventID=case(EventID=="4728","Added",EventID=="4729","Removed")| rename Computer AS "Domain Controller",TargetUserName AS "Group",EventID AS "Action"| table "_time","SubjectUserName","Action","MemberName","Group","Domain Controller"

The search works well as long as the Group Names in the lookup tables are unique. But if there is an entry in the lookup table that has derivatives(i.e. AD_Group), it returns all the derivatives also instead of what is in the lookup table only.

EX. Lookup Table

Group_Name column contains "AD_Group", "AD_Group_1", "AD_Group_2"

The search returns all the above groups plus additional groups not in the lookup table; AD_Group_3, AD_Group_4, etc...

I need to know how I can just return the entries in the list and not the derivatives of AD_Group.

Re: Splunk search returning additional results not specified in Lookup table

somesoni2 — Fri, 01 Apr 2022 18:04:56 GMT

Does your lookup table data has any wildcard "*" in them? An actual example would be better.

Re: Splunk search returning additional results not specified in Lookup table

hillsmtb7 — Fri, 01 Apr 2022 18:38:31 GMT

Group_Name

AD_Group

AD_Group_2

AD_Group_3

AD_Group_Addon

AD_Group_Test

AD_Group_Watch

This is the lookup table example. The issue is with the first group since it brings back results for row 2 and 3 correctly, but also other groups that meet the beginning criterion "AD_Group" but are not part of the list.

My search without the first group produce 29 entries for the past 30 days; and correctly so. But if I were to add the first line to the Lookup table, 800+ entries return because of the derivatives of the first part AD_Group.

Re: Splunk search returning additional results not specified in Lookup table

hillsmtb7 — Fri, 01 Apr 2022 18:40:01 GMT

AD_Group_4, AD_Group_5, AD_Group_6, are not part of the list but show up because AD_Group is part of the lookup table.

Re: Why is Splunk search returning additional results not specified in Lookup table?

somesoni2 — Fri, 01 Apr 2022 18:57:29 GMT

Give this a try

index=<index name> EventID IN (4728,4729) TargetUserName IN [| inputlookup Test_Splunk_Lookup_Table_v2.csv | head 200 | rename Group_Name as search | table search | format "(" "" "" "" "" ")" ] | eval EventID=case(EventID=="4728","Added",EventID=="4729","Removed")| rename Computer AS "Domain Controller",TargetUserName AS "Group",EventID AS "Action"| table "_time","SubjectUserName","Action","MemberName","Group","Domain Controller"

Re: Why is Splunk search returning additional results not specified in Lookup table?

hillsmtb7 — Fri, 01 Apr 2022 19:47:25 GMT

somesoni2 - Great Job....This worked. Thank you!!!