topic Need to add the input lookup file in the search in Splunk Search

Need to add the input lookup file in the search

renuka — Fri, 04 Jun 2021 08:53:31 GMT

Hello All

"Good Day"

index="aedc"
| rex field=source "-_(?<source>\S+)"
| rex "(?<ModuleID>MY\d+)"
| rex "(?<Path>/F.\s\S+\s\S+\s\S+\s\S+\s\S{5})"
|search"source"="*" MY22 "CRS_ASIL"="*" *
|rename "TC_D2_Test Result" as Result, TC_D2_Execution_date as verified_Date,"CRS__TestType" as TestType
| rename CRS__implementation_phase as CRS_IP, "TC_Test Result" as result
| eval verified_Date=if((verified_Date == "Attr not found : D2_Execution_date"),null(),verified_Date)
|eval Date=strptime(verified_Date, "%a %d %B %Y %H:%M:%S")|eval date=if(Date>1604600000.000000 OR Date>1602000000.000000,Date,0)
| eval First_Date=verified_Date, sortstring="~"
|eval date=if(Date>1604600000.000000 OR Date>1602000000.000000 ,Date,0)
| append
[|inputlookup DoorsMappingwithDatenopartial.csv |append[| inputlookup DoorsMappingNoDate.csv
|where bPartialResultsEnabled="FALSE"]]
| stats count(eval(Path="/F. System Testing/System Functional Test Cases")) as "Functional Total",count(eval(Result=case(Path="/F. System Testing/System Functional Test Cases" AND date=Date ,Result))) as "Functional Test Conducted",count(eval(Result=case(Path="/F. System Testing/System Functional Test Cases" AND Result="Pass" OR Result="PASS" AND date=Date,Result))) as "Functional Pass",count(eval(Result=case(Path="/F. System Testing/System Functional Test Cases"AND Result="Fail" OR Result="FAIL" AND date=Date,Result))) as "Functional Fail" by "CRS_Customer Requirement Identifier"
|eventstats sum("Functional Total") as Functional_Total,sum("Functional Test Conducted") as "Functional Test Conducted",sum("Functional Pass") as "Functional Pass",sum("Functional Fail") as "Functional Fail"|table Functional_Total,"Functional Test Conducted","Functional Pass","Functional Fail"

Above is my query in the query i am trying to add condition using lookup files
Condition is if bPartialResultsEnabled is False we should consider date in functional count if it is true we should not consider date

I tried all possible ways ,I know

Please help me out..

Thank you in advance

Re: Need to add the input lookup file in the search

kamlesh_vaghela — Fri, 04 Jun 2021 09:41:20 GMT

@renuka

Here you have to use some trick. Please check below search.

In this search I have created a field name 'flag' with required conditions and same flag will be used in stats.

YOUR_FIRST_SEARCH | eval flag = case(bPartialResultsEnabled=="False" AND date=Date,"True", bPartialResultsEnabled=="True","True",1=1,"False") | stats count(eval(Result=case(Path="/F. System Testing/System Functional Test Cases" AND flag="True" ,Result))) as "Functional Test Conducted" YOUR_REST_SEARCH

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Need to add the input lookup file in the search

renuka — Fri, 04 Jun 2021 10:16:06 GMT

@kamlesh_vaghela

Thank you reply
But I couldn't extract inputlookup data into prevvious search query can you please ellaborate in detail
that would help me

Thank you in advance
"Happy Splunking"

Re: Need to add the input lookup file in the search

kamlesh_vaghela — Fri, 04 Jun 2021 11:34:07 GMT

@renuka

Please try this search.

YOUR_SEARCH | eval flag = case(bPartialResultsEnabled=="False" AND date=Date,"True", bPartialResultsEnabled=="True","True",1=1,"False") | stats count(eval(Path=="/F. System Testing/System Functional Test Cases" AND flag="True")) as "Functional Test Conducted" REST_OF_THE_SEARCH

Explanation:

| eval flag = case(bPartialResultsEnabled=="False" AND date=Date,"True", bPartialResultsEnabled=="True","True",1=1,"False")

This flag variable will identify whether particular event should be consider or not for count in stats command.

Condition is if bPartialResultsEnabled is False we should consider date in functional count if it is true we should not consider date

As per our logic, date=Date should only consider when bPartialResultsEnabled="False" else bPartialResultsEnabled=True

So event should be consider when one of this 2 condition matched.

And in our case flag is doing same.

| stats count(eval(Path=="/F. System Testing/System Functional Test Cases" AND flag="True")) as "Functional Test Conducted"

I have changed in eval so you can try that also.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.