lookup value based on current search results

[lbrhyne]

New to Splunk and having a difficult time returning the correct results. The below query works... meaning that it converts the email address to user from the identities index and successfully looks up the value of user in the wineventlog index that contain either EventCode 4624 or 4625 based on the provide critera. However, when the stats are listed in the table after the query has completed, the values(user) as "Login ID" does not line up with values(fullname) as "Full Name", values(department) as Department and the device name does not show. Example: Login ID = tJones Full Name = Sammy Smith The goal is to have all the fields line up simular to Tjone | Tom Jones | Department Name | Device. Thanks in advance for any assistance provided.

index=identities sourcetype="Vantage" ee_status=* earliest=-1d@d latest=-0d@d
| eval user = trim(replace(email, "@domain.com", ""))
| search  [ | search index="wineventlog"  sourcetype="WinEventLog:Security" (EventCode=4624 AND Logon_Type=2) OR EventCode=4625 earliest=-10m latest=now()| fields user| format]
| table user,department,ad_fname,ad_last_name, Workstation_Name
| eval fullname = toString(ad_fname) + " " + toString(ad_last_name)
| stats values(user) as "Login ID", values(fullname) as "Full Name", values(department) as Department, values(Workstation_Name) as Device

[jawaharas · Accepted solution]

To rename a field use 'rename' command not 'stats' which is to categorize data based on a particular field.

index=identities sourcetype="Vantage" ee_status=* earliest=-1d@d latest=-0d@d 
| eval user = trim(replace(email, "@domain.com", "")) 
| search 
    [| search index="wineventlog" sourcetype="WinEventLog:Security" (EventCode=4624 AND Logon_Type=2) OR EventCode=4625 earliest=-10m latest=now() 
    | table user ] 
| eval fullname = toString(ad_fname) + " " + toString(ad_last_name) 
| table user,department,ad_fname,ad_last_name, Workstation_Name
| rename user as "Login ID", fullname as "Full Name", department as Department, Workstation_Name as Device

Reference:
https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Stats
https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Rename

[lbrhyne]

Thank you Jawaharas, that worked! The last thing I'm still not able to pull into the report is the Device name from the wineventlog, as it remains to be blank.

[woodcock]

You should click Accept to close this question and if you have something different (even if related), ask another.

[jawaharas]

Kindly accept the answer it it helped you, so others can refer it.

The field names are case sensitive. Verify the field name 'Workstation_Name'