How to match append result?

[graceojo34]

index=os process=sshd name="session opened" action=success
| eval user=upper(user)
| lookup all_svc_samaccountname.csv SamAccountName as user OUTPUT Match
| search Match =1
| eval dest=upper(dest)
| fields dest user
| lookup cmdb_all_assets.csv name as dest Output sys_class_name
| search sys_class_name=cmdb_ci*server
| eval User=mvindex(user,-1)
| eval AccountUsed=upper(User)
| search AccountUsed IN (*)
| fillnull value="Not Provided" AccountUsed
| lookup user_info_all.csv Samaccountname as AccountUsed OUTPUT department Samaccountname Owner_Samaccountname
| stats values(department) as Department latest(_time) as Time count by Owner_Samaccountname AccountUsed dest
| convert ctime(Time)
| rename dest as Target_Computer
| append [search index=wineventlog EventID IN (4648) ProcessName="C:\\Windows\\System32\\lsass.exe" source="XmlWinEventLog:Security" action=success |stats count by user ComputerName]
| table Time Owner_Samaccountname AccountUsed Department Target_Computer user ComputerName
| sort - Time

I I have these search query that returned the append fields values at the bottom to the the main search. My question is how can I match the fields?

[richgalloway]

The trick is to use the stats command to regroup the results by a common field.  In this case, however, there is no common field.  If the 'user' field is the same as either 'Owner_Samaccountname' or 'AccountUsed' then you can use rename to create a common field.

index=os process=sshd name="session opened" action=success
| eval user=upper(user)
| lookup all_svc_samaccountname.csv SamAccountName as user OUTPUT Match
| search Match =1
| eval dest=upper(dest)
| fields dest user
| lookup cmdb_all_assets.csv name as dest Output sys_class_name
| search sys_class_name=cmdb_ci*server
| eval User=mvindex(user,-1)
| eval AccountUsed=upper(User)
| search AccountUsed IN (*)
| fillnull value="Not Provided" AccountUsed
| lookup user_info_all.csv Samaccountname as AccountUsed OUTPUT department Samaccountname Owner_Samaccountname
| stats values(department) as Department latest(_time) as Time count by Owner_Samaccountname AccountUsed dest
| convert ctime(Time)
| rename dest as Target_Computer
| append [search index=wineventlog EventID IN (4648) ProcessName="C:\\Windows\\System32\\lsass.exe" source="XmlWinEventLog:Security" action=success 
  | stats count by user ComputerName
  | rename user as Owner_Samaccountname]
| stats values(*) as * by Owner_Samaccountname
| table Time Owner_Samaccountname AccountUsed Department Target_Computer ComputerName
| sort - Time

[graceojo34]

Thanks for this, but it doesn't work because I can't match the fields because I don't have a common field. I have a lookup table for both logs. How can I use a lookup table to create a common field to regroup the fields.