how to have 2 stats

[felipesewaybric]

How can i have those 2 stats?

| dbquery PROD-UOL7-MANUT-MONITORACAO
"select
dat_collect_transaction as \"data\",
TO_CHAR(dat_collect_transaction, 'DD/MM') as \"Date\",
dat_update as \"update\",
idt_inscription_account as \"conta\"
from collect_transaction
where idt_payment_method = 221 and dat_collect_transaction > sysdate -5 and dat_collect_transaction < sysdate-1"
| eval media=update-data
| stats avg(media) as Media
| eval Media = Media*2
| eval MediaTotal=tostring(Media, "duration")
| stats count(eval(media<MediaTotal)) as "Down" count(eval(media>MediaTotal)) as "Up" by Date
| table Date, Down, Up

[richgalloway]

You're comparing a numeric value, media, to a string value, MediaTotal, which doesn't work. Try comparing media to Media, although that should always yield "Down". BTW, your search does not account for media==MediaTotal.

[woodcock · Accepted solution]

Your question is so vague it is impossible to say but I am reasonably sure that you will need to use eventstats to insert your first pass of stats values into your events and then use a final stats later, maybe like this:

| dbquery PROD-UOL7-MANUT-MONITORACAO 
"select 
dat_collect_transaction as "data",
TO_CHAR(dat_collect_transaction, 'DD/MM') as "Date",
dat_update as "update",
idt_inscription_account as "conta"
from collect_transaction
where idt_payment_method = 221 and dat_collect_transaction > sysdate -5 and dat_collect_transaction < sysdate-1"
| eval media=update-data
| eventstats avg(media) as Media
| eval Media = Media*2
| eval MediaTotal=tostring(Media, "duration")
| stats count(eval(media<MediaTotal)) as "Down" count(eval(media>MediaTotal)) as "Up" by Date
| table Date, Down, Up

[felipesewaybric]

Thanks, this is the complete search, works like a charm:

| dbquery PROD-UOL7-MANUT-MONITORACAO
"select
dat_collect_transaction as \"data\",
TO_CHAR(dat_collect_transaction, 'DD/MM') as \"Date\",
dat_update as \"update\",
idt_inscription_account as \"conta\"
from collect_transaction
where idt_payment_method = 221 and dat_collect_transaction > sysdate -15 and dat_collect_transaction < sysdate-1"
| eval intervalo=update-data
| eventstats avg(intervalo) as Intervalo
| eval Intervalo = Intervalo*2
| stats count(eval(intervaloIntervalo)) as "Acima do tempo medio" values(conta) as Contas by Date
| table Date, "Acima do tempo medio", "Dentro do tempo medio", Contas