How to create an alert if only the row count of my table output is less than 10 rows?

[prabhu77749]

Hi team,

The below query returns 12 rows

index=test_core sourcetype=test_app  marker=123 |dedup host, instance_id | table host instance_id

host     instance_id
test1   instance_1
test     instance_1
test     instance_2
test1   instance_1
test     instance_1
test1   instance_1
test1   instance_1
test     instance_1
test     instance_1
test1   instance_1
test1   instance_1
test     instance_1

I am trying to create a real time alert only if the count is less than 12 .

Not able to give condition and get a table output format.

In turn i used streamstats which returns the output only when count is less than 12, but the output is not in table format

index=test_core sourcetype=test_app  marker=123 |dedup host, instance_id | table host instance_id| streamstats count as row | fields row *| stats count list(host) list(instance_id)| where count < 12 

Need guidance to have condition and get the result in table format when condition becomes true.
Thanks for your help in advance.

[gokadroid · Accepted solution]

If your current query gives you the data as you need then rather than all the extra stuff that you have put in with streamstats, can you try eventstats in your original query as | eventstats count as rows and then later | search rows<12 to see if it works for you:

index=test_core sourcetype=test_app marker=123 
|dedup host, instance_id 
| eventstats count as rows 
| table host instance_id, rows
| search rows<12
| fields - rows

Adding the space as per commnets

[prabhu77749]

Thanks for evenstats .. but

 index=test_core sourcetype=test_app marker=123 
 |dedup host, instance_id 
 | eventstats count as rows 
 | table host instance_id, rows
 | search rows<12

is returning table format with three columns

host    instance_id rows

But when i add | fields -rows

i am getting 12 rows with no values.. all empty .

Inturn i used and i am able to get results what i expected.

 index=test_core sourcetype=test_app marker=123 
 |dedup host, instance_id 
 | eventstats count as rows 
 | table host instance_id, rows
 | search rows<12
|  | table host instance_id

But please share your thoughts on why | fields -rows is not returning data and is there any other way to write in an optimized way.

Thanks

[prabhu77749]

Hi

| fields -rows -->> "WORKED now i missed a space between - and rows" now the one you gave works as expected"

Thanks for your help

[somesoni2]

The fields command should be like | fields - rows (space after hyphen).