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

[shishirkumar]

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)

[shishirkumar]

Please Help me to resolve this issue as it not resolved ,

Second LookUP file filtering values as its matching with lookupfile with filename and populating only which is matched value

[richgalloway]

Try replacing

 | 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

with

 | search NOT sreport_type="*" 
 | rex field=file_name "(?[[:alnum:]]+)"
 | lookup NewMaster.csv entity_name as compareclient outputnew entity_type as entity_type1
 | eval client=coalesce(client, entity_type1)
 | 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

[shishirkumar]

Thanks Rich,

But its not working

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=""
| rex field=file_name "(?[[:alnum:]]+)"
| lookup NewMaster.csv entity_name as compareclient outputnew entity_type as entity_type1
| eval client=coalesce(client, entity_type1)
| 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

its Still giving me count of 47 records as it should come to 121 this is because of 2nd Lookup is filtering client name which match with FileName but as per this logic it should only work where client is null then this if condition should work ?

[richgalloway]

When you post SPL, please highlight the code and click the Binary (101010) button so the code is displayed properly.
The lookup command does not filter events. It simply searches for the given field(s) in the lookup and returns the requested field(s) from the matching row. Since the second lookup command contains neither 'client' nor 'FileName' field, those fields cannot be influencing the lookup.
Have you run the second lookup independently to verify it returns the expected output for specific inputs?

[shishirkumar]

Yes I do,

I ReRun below as your request : But its still happening same , only giving me those report which are belonging to excel file means that are match (46 records) but its should be those also which are not null in case of client column
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
| fillnull value="N/A" areport_type
| fillnull value="NotDefined" client
| fillnull value="N/A" report_period_folder
| fillnull value="N/A" file_name
| fillnull value="N/A" Account_Name
| fillnull value="N/A" Object_Name
| 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=""
| 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

[shishirkumar]

NO buddy its not working still its filtering data upon second LookUP File

Count coming as 46 but its should be 123 as we are only replacing null value on client with file name

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="" | rex
field=file_name
"(?[[:alnum:]]+)" |
lookup NewMaster.csv entity_name as
compareclient outputnew entity_type as
entity_type1 | eval
client=coalesce(client, entity_type1)
| 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