I have a inputlookup which have fields like index and count need to create an alert which should trigger when count of indexes given will be exceed given count in lookup, use of sub search will also fine

[bapun18]

I have a inputlookup which have fields like index and count need to create an alert which should trigger when count of indexes given will be exceed given count in lookup, use of sub search will also fine

I wrote a query to get the result but it's simple and it's giving static output I want dynamically use index field and count field.

Following are the query I used.

|inputlookup idx_myvdf.csv | table index | stats count by index | where count < 1000



|tstats c by index where index[|inputlookup idx_myvdf.csv | rename index AS actual_index] AS actual_count | table index count actual_index]
where count < actual_count

I want to modify the query so that it will give the output with actual index count mentioned over inputlookup and alert will be triggered once actual count will be greater than count value provided under count field.

[somesoni2]

Do it like this (assuming the name of field in the lookup which has the count is countFieldInLookup, update per your case)
Updated per other question post

| tstats count as actual_count where [|inputlookup idx_myvdf.csv | table index ] by index 
| lookup  idx_myvdf.csv index OUTPUT count as threshold_count
 | where actual_count > threshold_count

[kamlesh_vaghela]

@bapun18

Can you please try this?

| tstats c  as count by index 
| append 
    [| inputlookup idx_myvdf.csv 
    | table index actual_count] 
| stats values(count) as count values(actual_count) as actual_count by index] 
| where count > actual_count

I have designed rough search. So just execute and provide your feedback.

[bapun18]

Hi Jacobevans/Kamlesh,

Thanks for your quick response, still we don't have any data after executing both the searches. Please find below.

[kamlesh_vaghela]

@bapun18

Can you please execute this search?

| tstats c  as count by index 
 | append 
     [| inputlookup idx_myvdf.csv 
     | table index actual_count] 
 | stats values(count) as count values(actual_count) as actual_count by index] 
 | where count > actual_count

As per your image( https://ibb.co/mFkZ8v1), actual_count field is not coming. Can you please confirm the values available in index OR actual_countagainst the indexes from tstats search?

[bapun18]

We are getting following error

[jacobpevans]

See my comment above. You can only have statistical aggregation function commands between stats and by - the index argument in @kamlesh_vaghela's comment is invalid.

[jacobpevans]

Small correction to these lines (otherwise this looks good to me also).

| stats values(count) as count values(actual_count) as actual_count by index] 
| where count > actual_count

[bapun18]

Hi Jacobevans/Kamlesh,

Thanks for your quick response, still we don't have any data after executing both the searches. Please find below.

https://ibb.co/W5dtjr7
https://ibb.co/mFkZ8v1