topic Re: help on time average calculation in Dashboards & Visualizations

help on time average calculation

jip31 — Tue, 28 Sep 2021 05:56:20 GMT

hello

I use the search below in order to calculate the average of the field "diff"

index=toto | eval diff=strptime('Fin',"%d/%m/%Y %H:%M:%S")-strptime('Debut',"%d/%m/%Y %H:%M:%S") | eval diff=round(diff, 2) | stats avg(diff) as diff

I am a little surprised because I have the same results if I add a | search in my search for changing the type of machine

index=toto | eval diff=strptime('Fin',"%d/%m/%Y %H:%M:%S")-strptime('Debut',"%d/%m/%Y %H:%M:%S") | eval diff=round(diff, 2) | search PPOSTE = * | stats avg(diff) as diff

index=toto | eval diff=strptime('Fin',"%d/%m/%Y %H:%M:%S")-strptime('Debut',"%d/%m/%Y %H:%M:%S") | eval diff=round(diff, 2) | VPOSTE = * | stats avg(diff) as diff

Is it the correct way to do that please?

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 06:18:35 GMT

Isn't it possible that your average diffs are simply equal in those cases?

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 06:22:18 GMT

no possible...

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 06:26:14 GMT

If you search gives "strange" results, just start from the beginning and start adding components to the search after you have made sure previous one works as intended.

So start with your strptimes and check if they convert the time correctly, then verifh your diffs and so on.

At first glance the searches look pretty ok.

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 06:36:30 GMT

its what I have done but I dont understand what appen...

If I am doing | stats sum(diff) I have the same results for PPOSTE and VPOSTE

But if I am doing | stats latest("Debut") as "Debut", latest("Fin") as "Fin", last(diff) as diff by VPOSTE, I can that my events are different and the result too!
| sort - diff

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 06:40:06 GMT

You're comparing single values to aggregate stats now. It's natural that they can vary.

Without additional info on your events all I can say is that the search seems ok.

Did you do a simple eval of two fields to strptimed fields and check the values if the resulting timestamps are ok?

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 07:51:18 GMT

yes it is....

what I note is that when I use a by clause, my results are good

| stats avg(diff) as diff by VPOSTE | stats avg(diff) as diff by PPOSTE

But if I try to filter earliest it doesn't works

| search PPOSTE=* | stats avg(diff) as diff

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 07:47:00 GMT

Try to calculate sum(diff) and count(diff) and see whether they change (especially the count) if you do additional filtering with search.

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 07:59:25 GMT

if I am doing :

index=toto | search PPoste=* | eval diff=strptime('Fin chargement Profile',"%d/%m/%Y %H:%M:%S")-strptime('Debut chargement Profile',"%d/%m/%Y %H:%M:%S") | stats sum(diff) as sum, count(diff) as count OR index=toto | search VPoste=* | eval diff=strptime('Fin chargement Profile',"%d/%m/%Y %H:%M:%S")-strptime('Debut chargement Profile',"%d/%m/%Y %H:%M:%S") | stats sum(diff) as sum, count(diff) as count

, the results is the same

But if I am doing

index=toto | eval diff=strptime('Fin chargement Profile',"%d/%m/%Y %H:%M:%S")-strptime('Debut chargement Profile',"%d/%m/%Y %H:%M:%S") | stats sum(diff) as sum, count(diff) as count by PPOSTE OR index=toto | eval diff=strptime('Fin chargement Profile',"%d/%m/%Y %H:%M:%S")-strptime('Debut chargement Profile',"%d/%m/%Y %H:%M:%S") | stats sum(diff) as sum, count(diff) as count by VPOSTE

the result is different..

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 08:13:37 GMT

Do you understand the difference between those commands?

If you do

<<whatever>>
| search PPOSTE=*
| <<whatever>>

You're limiting your search only to events which have their PPOSTE field present (not null).

That's all. It doesn't introduce any aggregation or any other transformation of your data.

If you have this field present in all your data, it effectively doesn't do anything at all. Or if you select a subset of your initial events that happens to have your sought stats equal to that of a whole population, you won't see any differences.

And you showed yourself that filtering your events by adding | search in the middle doesn't change the count of your events.

But if you do

<<whatever>>
| stats agg-stat(field) by PPOSTE

you're calculating separate stats value for each subset of your initial data - one per each value of PPOSTE field. So instead of a single value for your filtered subset of data you're getting as many resulting stats values as your PPOSTE field has distinct values.

You're doing two completely different things so it's only natural that they give other results.

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 08:26:03 GMT

yes of course I understand the difference

But my need is to calculate an average in a single panel from the VPOSTE and the PPOSTE field

So i need to filter the events before my stats instead using a by clause

So what I dont understand is that when I use the search below alone, it works and it filter the events correcty

index=toto | search VPoste=* | table VPoste

But when I use it with the diff calculation it doesnt works

You can see the XML

<panel> <single> <search> <query>index=virtuos_pr_metrics_citrix_connexion sourcetype=bp | search P_Poste=* | eval diff=strptime('Fin chargement Profile',"%d/%m/%Y %H:%M:%S")-strptime('Debut chargement Profile',"%d/%m/%Y %H:%M:%S") | stats avg(diff) as diff</query> <earliest>-24h@h</earliest> <latest>now</latest> </search> <option name="drilldown">none</option> <option name="numberPrecision">0.00</option> <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option> <option name="refresh.display">progressbar</option> <option name="unit">sec</option> </single> </panel>

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 08:47:52 GMT

Sorry mate, but I fail to understand what you're trying to do.

You have some events of which all seem to have fields PPOSTE and VPOSTE (at least that's how it looks from your earlier posts). So every aggregate stat, regardless of whether you do a | search PPOSTE=* or | search VPOSTE=* will result in the same values since you're doing aggregate stats on the whole set.

So if you want to do something else, please explain 🙂

Maybe show some excerpt of your data and desired result.

Because so far I simply don't understand what you're trying to achieve.

Re: help on time average calculation

jip31 — Tue, 28 Sep 2021 11:53:01 GMT

I just tried to explain that it was impossible to filter my results with | search V_Poste=* except if I use a by clause like | stats avg(diff) as diff by V_Poste...

But i have found the solution like this :

| stats avg(diff) as diff by V_Poste | stats avg(diff)

Re: help on time average calculation

PickleRick — Tue, 28 Sep 2021 13:02:03 GMT

avg from avg is kinda strange approach.

Are you sure that's what you need?

And indeed you don't seem to be filtering anything since apparently V_Poste (last time it was called VPOSTE ;-)) seems to be omnipresent so unless you filter by particular values of this field, you'll get your whole set of data back.

I still think it might be quite simple to do what you need but I don't understand what it is 🙂

I don't know your data, I don't know the needed result. I just see your tries at searching.