How to sort out values greater that a certain number in a table?

[chinmay25]

Hello,

I have the following query that I am working with and it generates a table with multiple counts for various ports at 15 min intervals.

index=abc source=xyz  SMF119HDSubType=2
| timechart span=15m count by SMF119AP_TTLPort_0001 usenull=f useother=f | stats values(*) as * by _time
| table _time Port1 Port2

The result is the following table. I only want to display results more that 5000 counts. I am trying to use the where Port 2>5000 command. But it does not work. I am only displaying 2 port columns. However, I have several other ports to monitor as well.

_time	Port1	Port2
2023-08-09 09:30:00	800	2700
2023-08-09 09:45:00	1200	4800
2023-08-09 10:00:00	1300	5300
2023-08-09 10:15:00	600	8000
2023-08-09 10:30:00	400	13500

I would appreciate your inputs.

Thank you,

Chinmay.

[chinmay25]

The solution worked perfectly for the count over 5000.

How can I combine the query for count greater than 5000 with the following query that generates the percent data. I want to set an alert only when the count is greater that 5000 and EDCD > 90

index=abc source=xyz (SYSNAME=EDCD) ((date_wday=tuesday AND date_hour=*) OR (date_wday=wednesday AND date_hour=*) OR (date_wday=thursday AND date_hour=*) OR (date_wday=friday AND date_hour=*) OR (date_wday=monday AND date_hour=10) OR (date_wday=monday AND date_hour=11) OR (date_wday=monday AND date_hour=12) OR (date_wday=monday AND date_hour=13) OR (date_wday=monday AND date_hour=14) OR (date_wday=monday AND date_hour=15) OR (date_wday=monday AND date_hour=16) OR (date_wday=monday AND date_hour=17) OR (date_wday=monday AND date_hour=18) OR (date_wday=monday AND date_hour=19) OR (date_wday=monday AND date_hour=20) OR (date_wday=monday AND date_hour=21) OR (date_wday=monday AND date_hour=22) OR (date_wday=monday AND date_hour=23) OR (date_wday=saturday AND date_hour=0) OR (date_wday=saturday AND date_hour=1) OR (date_wday=saturday AND date_hour=2) OR (date_wday=saturday AND date_hour=3) OR (date_wday=saturday AND date_hour=4) OR (date_wday=saturday AND date_hour=5) OR (date_wday=saturday AND date_hour=6) OR (date_wday=saturday AND date_hour=7))
| bin _time span=1h
| eval CPU = round(RCVCPUA/16,2), Hour = date_hour
| chart eval(round(avg(CPU),2)) as "CPU" over Hour by SYSNAME

This query generates a table as follows

Hour	EDCD
8	43.5
9	76.2
10	91.5

Thank you,

Chinmay.

[ITWhisperer]

It is not at all clear how you want these two searches combined.

[chinmay25]

I am having that trouble too. So we want to be alerted when both conditions meet. The count is over 5000 and the EDCD percent is above 90%.

Right now I have 2 separate alerts one for count over 5000 and another for EDCD percent above 90%.

Is there a way we can combine the two using a AND clause.

[ITWhisperer]

It depends on what you want to compromise - you have different time spans, different time ranges(?), different filters.

You could try appending one search to the other but you might run into subsearch limitations.

To be honest, I think it would complicate it too much for the sake of not have two alerts. It doesn't seem worth it to me.

[chinmay25]

I have the following query that gives the count for port and CPU percent. 

index=abc source=xyz SMFID=EDCD SMF119HDSubType=2
| timechart span=60m count by SMF119AP_TTLPort_0001 usenull=f useother=f | stats values(*) as * by _time
| untable _time Port Count
| where Count > 4000 | eval DATE = strftime(_time,"%m/%d/%y %H:%M:%S.%2N")
| eval Date = substr(DATE,1,9) | eval Hours = substr(DATE, 11,18)
| appendcols [search index=abc source=xyz (SYSNAME=EDCD) ((date_wday=tuesday AND date_hour=*) OR (date_wday=wednesday AND date_hour=*) OR (date_wday=thursday AND date_hour=*) OR (date_wday=friday AND date_hour=*) OR (date_wday=monday AND date_hour=10) OR (date_wday=monday AND date_hour=11) OR (date_wday=monday AND date_hour=12) OR (date_wday=monday AND date_hour=13) OR (date_wday=monday AND date_hour=14) OR (date_wday=monday AND date_hour=15) OR (date_wday=monday AND date_hour=16) OR (date_wday=monday AND date_hour=17) OR (date_wday=monday AND date_hour=18) OR (date_wday=monday AND date_hour=19) OR (date_wday=monday AND date_hour=20) OR (date_wday=monday AND date_hour=21) OR (date_wday=monday AND date_hour=22) OR (date_wday=monday AND date_hour=23) OR (date_wday=saturday AND date_hour=0) OR (date_wday=saturday AND date_hour=1) OR (date_wday=saturday AND date_hour=2) OR (date_wday=saturday AND date_hour=3) OR (date_wday=saturday AND date_hour=4) OR (date_wday=saturday AND date_hour=5) OR (date_wday=saturday AND date_hour=6) OR (date_wday=saturday AND date_hour=7))
| bin span=1h@h _time | eval "Hours"=strftime('_time',"%H:%M:%S.%2N") | eval DATE = strftime('_time',"%m/%d/%y %H:%M:%S.%2N") | eval Date = substr(DATE, 1,9)
| eval CPU = round(RCVCPUA/16,2)
| stats avg(CPU) as "CPU" by Hours Date | eval CPU=round(CPU,2)
]
| table Date Hours Port Count CPU

This generates the following result. I want to set an alert only when the count is >5000 and CPU >80. What combined statement can be used to get the desired result?

Date	Hours	Port	Count	CPU
08/22/23	7:00:00.00	23050	75787	38.42
08/22/23	8:00:00.00	23050	19854	84.56
08/22/23	9:00:00.00	23008	4126	37.16
08/22/23	9:00:00.00	23050	20121	35.71

[ITWhisperer · Accepted solution]

| untable time port count
| where count > 5000
| xyseries time port count