I am having some troubles with the lookup/inputlookup commands and was hoping someone could lend assistance.
I have a search that I am using to get login/logoff details and duration of each session for virtual sessions. This search works great. Once this information is returned via search, I am attempting to use lookup (I also have tried inputlookup and append but that didn't work) to load additional detail from an Active Directory user list and attach a few columns of data from the AD list that will give additional user details (their email address, cn, description, and userPrincipalName) for our admin team. I am pulling in the lookup details by looking at a field with the Users information (UserDisplayName) and comparing it to the field "name" (in the lookup table). From my understanding doing this then allows me to pull any field from that particular row in the table. However when I go to output the data in a table, it's not working. I feel like I have the right coding, but there is either a conflict with the code not being laid out in the proper order that is resulting in the search not working or commands not interacting as I expect them too. I would appreciate if you could take a look and let me know what you think.
index=* host=vdi sourcetype=syslog (EventType="AGENT_DISCONNECTED" OR EventType="AGENT_CONNECTED") | transaction UserDisplayName maxevents=2 startswith=EventType="AGENT_CONNECTED" endswith=EventType="AGENT_DISCONNECTED" maxspan=-1 | eval Logontime=if(EventType="AGENT_CONNECTED",_time,null()) | eval Logofftime=Logontime+duration | eval duration2=duration | eval h=floor(duration2/3600) | eval m=floor((duration2-(h*3600))/60) | eval s=floor(duration2-(h*3600)-(m*60)) | eval SessionDuration=h."h ".m."m ".s."s" | eval total=TotalLoginLength/60 | eval total=round(total,2) | eval UserDisplayName=mvindex(split(UserDisplayName,"\\"),-1) | convert ctime(Logontime) as Logontime | convert ctime(Logofftime) as Logofftime | eval duration = duration/60 | eval duration=round(duration,2) | lookup AD_User_LDAP_list name AS UserDisplayName OUTPUTNEW cn AS cn description AS description userPrincipalName AS userPrincipalName | table UserDisplayName, MachineName, DesktopDisplayName, DesktopId, Logontime, Logofftime, SessionDuration, duration, total, description, cn, userPrincipalName | sort UserDisplayName, MachineName, -duration | rename duration AS "Splunk Session Duration (Mins)", Logontime AS "Logon", Logofftime AS "Logoff", SessionDuration AS "Total Session Duration", total AS "VDI Logged Session Duration (Mins)"
What I see in my output is attached below, all of the data populating into the table is from the session duration search, the lookup info never seems to get added to the table as expected (the particular columns are empty (description, cn, userPrincipalName). Please note some machine data was blanked out on the left hand columns. How can I add the additional information to my search?
Since grammar is not wrong, I do not understand just by looking at this search sentence.
Is it executed right before the LOOKUP command and the correct value is set for UserDisplayName?
Also check whether it matches the name of the lookup file.
Execute the search so far
| eval UserDisplayName=mvindex(split(UserDisplayName,"\"),-1)
Confirm lookup file
| inputlookup AD_User_LDAP_list