Splunk Search

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

griffinpair
Path Finder

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"
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
0 Karma

griffinpair
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? &#x1f680; We invite you to join our elite squad ...