topic Re: lookup value based on current search results in Splunk Search

lookup value based on current search results

lbrhyne — Thu, 25 Jul 2019 22:14:16 GMT

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

Re: lookup value based on current search results

jawaharas — Fri, 26 Jul 2019 05:19:28 GMT

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

Re: lookup value based on current search results

lbrhyne — Fri, 26 Jul 2019 13:51:41 GMT

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.

Re: lookup value based on current search results

jawaharas — Sat, 27 Jul 2019 03:51:10 GMT

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'

Re: lookup value based on current search results

woodcock — Sun, 28 Jul 2019 13:13:49 GMT

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