Rename Field From Input File And Perform Search

[aakashshah]

Hello! I am attempting to find events based on names in a CSV file (I am attempting to build a search to identify security group name changes). However, I appear to be missing something since I do not get any results.

Here is the search I am trying, but it is not presenting any results:

(EventCode=4781)
[inputlookup Groups.csv | rename Security_ID AS Old_Account_Name]

Here is what I have and have tried:

I have a Groups.csv file that looks like this that contains the groups I would like to search against:

Security_ID
*\Group1
*\Group2
*\Group3    

I have tested renaming the header and this correctly shows the contents of my CSV file with the renamed header as expected:

| inputlookup Groups.csv | rename Security_ID AS Old_Account_Name

I am also able to successfully get results when I do this:

(EventCode=4781)
(Old_Account_Name="*\Group1")

However, I am not able to perform the original search, which is to search for events that contain any of the groups in the CSV file.

I appear to be missing something - can someone please help correct my search query?

Thanks!

Update 1:
With micahkemp's help below, I have identified that my original search was faulty since the field "Old_Account_Name" in the event does not contain a domain prefix, i.e. "Old_Account_Name" in the Splunk event shows as "Group1" instead of "Domain\Group1", and therefore, I should not be looking for "*\Group1", but rather "Group1" only. What I need help with now is how to either create a substring in the table, or replace the characters "*" with "" in the table - can anyone help me with this?

Thanks!

[micahkemp · Accepted solution]

Based on your recent comment, try this:

(EventCode=4781)
[|inputlookup Groups.csv | rex field=Security_ID "[^\\\]+\\\(?<Old_Account_Name>.*)" | table Old_Account_Name]

This will extract a new field from the Groups.csv file which is the string after the first \ in the Security_ID field.

[aakashshah]

Perfect, thank you that worked!

[493669]

to remove first prefix from csv file use substr command as below:

(EventCode=4781)[inputlookup Groups.csv | rename Security_ID AS Old_Account_Name|eval Old_Account_Name=substr(Old_Account_Name,3)]

[aakashshah]

Thanks that works! This is also easier to read. If you can edit your original reply to include this information, or perhaps add a new reply with this information, I would be happy to mark that as an answer too. I'm new to Splunk Answers, so if this is not appropriate or not the correct way to do this, please let me know.

[493669]

If you are satisfied with answer then do upvote..

[493669]

Can you try below:

(EventCode=4781)|join type=inner max=0 [|inputlookup Groups.csv | rename Security_ID AS Old_Account_Name]

[aakashshah]

Thanks for your reply. Unfortunately this did not help.

[micahkemp]

I can't see any flaws in your search, and I just tested created test data and lookup file to match your question and it works exactly as you have it in your question.

Can you check "silly" things like your time period for the search that works and the search that doesn't?

I'm also assuming your Groups.csv file isn't exactly what you have included in the question (due to privacy reasons). If the CSV file has more than one field in it you would need to limit the output of your subsearch to just the field you care about, like:

(EventCode=4781)
[inputlookup Groups.csv | rename Security_ID AS Old_Account_Name | table Old_Account_Name]

[aakashshah]

Hello and thanks for replying michahkemp! I did confirm that my time period is the same as the other searches - 4 hours (I had made some changes recently that I was using as my test).

The CSV file is named different and correct does have other entries, but otherwise the structure is the same, i.e. it only has a single column in it named "Security_ID" and each of the entries also has a "*\" prefixed as displayed above.

I also tried adding "| table Old_Account_Name", but that didn't help the search.

I'm not sure if this matters, but we are currently on v6.5.2 in case the rename feature is only included on later versions (the inputlookup definitely works since I use that successfully in some other searches).

Please let me know if I can provide any other information.

Thanks!

[micahkemp]

When you run your search that returns no results, open up the search.log (Job -> Inspect Job -> click search.log link) and look for a line that displays the expanded search that was run. When I ran it, for instance, I see this in my search.log:

01-06-2018 02:19:54.063 INFO  UnifiedSearch - Expanded index search = (EventCode=4781 (Old_Account_Name="*\\Group1" OR Old_Account_Name="*\\Group2" OR Old_Account_Name="*\\Group3"))

[aakashshah]

Ahh thanks for showing me how to view the details of the search.

I just realized that one of the problems with my search is that the CSV file contains "*\Group1", but the event in Splunk contains the "Old_Account_Name" field WITHOUT the domain prefix, and therefore is only "Group1" and not "Domain\Group1".

So with this in mind, I need to find a way to find the substring of each of the CSV file's rows to remove the first 2 characters (or use a replace) - do you know how this can be accomplished?

Thanks!