How to count with inputlookup?

[thanhnv244]

Hi, I have a search that I have been struggle for a few days.

I have an index that contains two fields: type and Total_Count
I have another CSV that contains similar fields: stype and sTotal_Count

Now the index may have many row with same type and difference Total_Count, same on the CSV

Purpose of the search is: for each row in index, find out how many row in CSV that have stype is equal type, and sTotal_Count is greater than Total_Count, then append the count to the table along side with type and Total_Count

So I have test this inputlookup on CSV and it work fine

| inputlookup typeA.csv where stype="A01" and sTotal_Count > 30 | stats count

Then I would do something like this

index="ktme_v7_measurement"
| join
[|inputlookup typeA.csv where stype=type and sTotal_Count > Total_Count | stats count as type_c]
| table type Total_Count type_c

But the type_c column is always 0

Then I found this question, he had same problem with me on passing field into subsearch

answers/85076/passing-parent-data-into-subsearch.html

but the answer was

an outer search cannot pass values into a subsearch

And now I'm stuck, I'm very new to Splunk so I can not figure out how to do my search here.

Please help me, many thanks

[FrankVl · Accepted solution]

This requires getting creative with eventstats and multivalue functions.

index="ktme_v7_measurement" 
| table type Total_Count
| streamstats count as rownum 
| append 
    [|inputlookup typeA.csv | rename stype as type | table stype sTotal_Count ] 
| eventstats list(sTotal_Count) as cvalues by type 
| search Total_Count=* 
| mvexpand cvalues 
| where cvalues>Total_Count 
| stats count by type,Total_Count,rownum 
| table type,Total_Count,count

This gets the data from the index, keeps the 2 relevant columns and gives each row a unique number. This way the search also works when there are duplicate (type,Total_Count) entries in the index. It then adds the data from the lookup, renaming the stype field to type and again keeping only the relevant columns. It then lists all sTotal_Count values found for the same type. It then drops the rows originating from the lookup. It expands the multivalue field and then filters rows where the cvalue is greater than the Total_Count and then counts the entries by type,Total_count,rownum and finally drops the rownum and cvalues columns.

[thanhnv244]

Thank you very much, I think the result is perfect for me. The query seem overwhelming, I need to study it more.

[FrankVl]

Feel free to drop a comment if you need further explanation. A good way to understand what it does, is just execute it step by step (possibly adding some filtering to reduce the number of rows, so it is easier to see what happens in each step).