I'm posting this as everything I have been referencing is from years ago.
I need to relate Users to GPO changes. The problem is that the GPO's EventCode=5136 only reports the distinguishedName (DN). So I exported a GPO_GUID_displayName.csv file with the following formats.
GUID,displayName
"CN={XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}CN=Policies,CN=System,DC=xx,DC=xx,DC=xxx,DC=com",Windows 10
CN={XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}*,Default Domain Policy
CN={XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}CN=Policies,CN=System,DC=xx,DC=xx,DC=xxx,DC=com,DefaultDomainPolicy2
my search look like this
index="wineventlog" Class=groupPolicyContainer GUID="CN={XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}" OR GUID="CN={XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}" source="WinEventLog:Security"
| stats count by user,GUID,_time
| lookup GPO_GUID_displayName.csv GUID output displayName
| rename _time as Date
| convert timeformat=%F ctime(Date)
| table Date,user,GUID,displayName
I get everything as expected but the mythical displayName that no matter what format I use seems to always be blank. I figured that the comas on the csv for the FQDN its messing up the file which is that I'm trying to make the wildcard work with the lookup or even if the quotation would. I have looked into this whole transforms thing from past post but I simply don't get it and I'm not totally sure how to access the file as I do everything thru the web GUI. any help its appreciated.
Upon a deeper look into my csv file I noticed a formatting difference. My csv was an export of another table generated from my index=msda. I made the table with displayName and distinguishedName. The distinguishedName field has an extra comma. That my Splunk friends solve it. So moral of the story is make sure there are not format errors and the data to match. I was able to spot it by making a smaller sample to test the output. Hope this helps and apologize for the confusion.
Upon a deeper look into my csv file I noticed a formatting difference. My csv was an export of another table generated from my index=msda. I made the table with displayName and distinguishedName. The distinguishedName field has an extra comma. That my Splunk friends solve it. So moral of the story is make sure there are not format errors and the data to match. I was able to spot it by making a smaller sample to test the output. Hope this helps and apologize for the confusion.
@rororspec please go ahead and accept your own answer to mark this question as answered!
this is because of | convert timeformat=%F ctime(Date) Could you please remove this line and try out and confirm the response
I downvoted this post because no changes to the table
Hi rororspec
Downvoting should only be reserved for suggestions/solutions that could be potentially harmful to a Splunk environment or goes completely against known best practices. Simply commenting with constructive feedback on the post you are concerned with will be more beneficial for the community to learn from.
Some of the most active members in Answers have helped set the standard of how voting etiquette should work in the Splunk community which distinguishes our culture apart from other Q&A forums. Upvote early and often to give credit where it’s due for high-quality posts, comment where you think feedback needs to be given, and only downvote if something potentially dangerous is suggested or people are just being inappropriate.
If you’re interested in seeing how this voting etiquette was developed, check out this Splunk Answers post: https://answers.splunk.com/answers/244111/proper-etiquette-and-timing-for-voting-here-on-ans.htmlon-...
My deepest apologies for any confusion this may have caused. Logic was the only element used to minus the answer as it shows as an answer. I have plus in an attempt to fix the wrong doing. I am eager to keep collaborating with the members on this wealth of knowledge.
Hi rorospec
Thanks for understanding the guidelines and we are excited to see you contributing to the community.
Even without the
| rename _time as Date
| convert timeformat=%F ctime(Date)
the table
| stats count by user,GUID,_time
| lookup GPO_GUID_displayName.csv GUID output displayName
| table user,GUID,displayName
user GUID displayName
XXXXX XXXXXXXX blank
hey rororspec,
Did u check permissions for lookup?
I created the lookup. permissions are set to red for everyone