index=wineventlog host=ATLINFPSAS3 sourcetype="WinEventLog:Security" ApolloClientReports NOT "*Symantec Endpoint Protection*" EventCode="4663" Object_Name != "*~*"
Account_Name!="svcirisadmin"
| fields Object_Name Account_Name
| eval path_segment = split(Object_Name,"\\") | mvexpand path_segment
| lookup NSAMasterListForClientReporting.csv entity_name as path_segment OUTPUTNEW entity_type
| eval entity_type = if(match(path_segment,"\d{4}(_\d{2})?"), "report_period_folder",entity_type)
| eval entity_type = if(match(path_segment,".*\.(txt|xlsx|pdf|csv|xls)"), "file_name",entity_type)
| eval {entity_type} = path_segment
| stats values(areport_type) as areport_type,values(sreport_type) as sreport_type,values(client) as client,values(file_name) as file_name, values(report_category) as report_category,values(report_period_folder) as report_period_folder by Object_Name Account_Name
| search NOT sreport_type="*"
| fillnull value="NotDefined" client
| rex field=file_name "(?[[:alnum:]]+)"
| lookup NewMaster.csv entity_name as compareclient outputnew entity_type as entity_type1
| eval client=if(client="NotDefined",entity_type1,client)
| stats count(file_name) as counttotal by Account_Name areport_type client report_period_folder file_name Object_Name
| table Object_Name areport_type client report_period_folder file_name Account_Name counttotal
How to fix this issue of filtering data in second LOOKUP as here Second lookup is only for checking condition when client is null to search lookup file and compare with filename(compare filename=ExcelClientColumn)
| eval
client=if(client="NotDefined",entity_type1,client)
Idea is : If client column is null then read data from filename (split filename) then compare with CLientMaster(newmaster.csv) if data match with filename and columnname(entity_name) then added condition as client=if(client="NotDefined",entity_type1,client)
... View more