Use count from first search in the Where Clause of the subsearch

[griffinpair]

I want to use the count from the first search "FilesImported" as criteria in the where clause of the subsearch. FilesImported is 0 and "File Missed" needs to be 1, but "File Missed" is currently returning 0 which shows me that the subsearch Where Clause is not working as I expected. So, how does one use the count of the first search as criteria in the Where Clause of the subsearch?

source=*D:\\gfd\\import* source=*Daily\\Debug* Moved earliest=-36h@h
| eval time=strftime(round(strptime(file_Time, "%I:%M:%S %P")), "%H:%M:%S")
| eval dow=strftime(strptime(file_Date, "%m/%d/%Y"), "%A")
| rex field=source "importhelpers\\\+(?<ClientID>[^\\\]+)"
| where ClientID="NAB"
| where (like(source,"%"."NAB"."%") AND (dow!="Sunday" AND dow!="Monday") AND (time>"07:57:00" AND time<"08:27:00") AND FileImported="Record")
| stats count as FilesImported 

| appendcols [ search source=*D:\\gfd\\import* source=*Daily\\Debug* "Could not find a file in the" OR Moved earliest=-36h@h
| eval time=strftime(round(strptime(file_Time, "%I:%M:%S %P")), "%H:%M:%S")
| eval dow=strftime(strptime(file_Date, "%m/%d/%Y"), "%A")
| rex field=source "importhelpers\\\+(?<ClientID>[^\\\]+)"
| where ClientID="NAB"
| where ((like(source,"%"."NAB"."%")  AND FilesImported!=1) AND (dow!="Sunday" AND dow!="Monday") AND (time>"07:27:00"AND time<"08:27:00") AND (file_Missing="Position"))
| stats count as "File Missed" ]

| table "File Missed"

[DalJeanis]

First, subsearches is happen FIRST, and in a kind-of top-to-bottom order. So, the subsearch can't use a value that doesn't exist, unless you use the map command, which is seldom the right option.

Second, appendcols is almost never the right answer. In this case, you have two nearly identical searches, and you are going back to the well twice for no good reason. Just grab all the records once, calculate what you want to know from each kind of record, and then stats it.

Third, the test for %NAB% was redundant since you could only get to that portion of the code if the source contained NAB.

Fourth, in refactoring the code, I wondered why the file arrival could only count after 7:57 but the file missing could count after 7:27. That seems logically off, but the below code should approximate the prior apparent functioning.

 earliest=-36h@h source=*D:\\gfd\\import* source=*Daily\\Debug* ("Moved" OR "Could not find a file in the" )
 | rex field=source "importhelpers\\\+(?<ClientID>[^\\\]+)"
 | where ClientID="NAB"

 | eval dow=strftime(strptime(file_Date, "%m/%d/%Y"), "%A")
 | where (dow!="Sunday" AND dow!="Monday") 

 | eval time=strftime(round(strptime(file_Time, "%I:%M:%S %P")), "%H:%M:%S")
 | where (time<"08:27:00" AND time>"07:27:00") 
 | eval FilesImported=case(time>"07:57:00" AND FileImported="Record", 1)
 | eval FilesMissed=case(file_Missing="Position", 1)    

 | stats sum(FilesImported) as FilesImported sum(FilesMissed) as FilesMissed by file_Date
 | where FilesMissed>FilesImported

[griffinpair]

I do not think this is the logic I am looking for. Thank you for your input.