topic Re: 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 in Splunk Search

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 — Wed, 04 Sep 2019 12:31:06 GMT

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.

Re: 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

kamlesh_vaghela — Wed, 04 Sep 2019 13:11:21 GMT

@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.

Re: 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

jacobpevans — Wed, 04 Sep 2019 13:32:04 GMT

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

Re: 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

somesoni2 — Wed, 04 Sep 2019 13:58:43 GMT

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

Re: 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 — Wed, 04 Sep 2019 14:03:27 GMT

We are getting following error

Re: 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

jacobpevans — Wed, 04 Sep 2019 14:42:31 GMT

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.

Re: 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 — Wed, 04 Sep 2019 14:59:37 GMT

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

Re: 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 — Wed, 04 Sep 2019 15:00:40 GMT

Hi Jacobevans/Kamlesh,

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

Re: 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

kamlesh_vaghela — Thu, 05 Sep 2019 04:54:12 GMT

@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?