topic Re: Keep Last Daily Activity Per User in Getting Data In

Keep Last Daily Activity Per User

HeinzWaescher — Thu, 31 Oct 2013 14:32:49 GMT

Hi,

i would like to keep the last event/activity of a day for every user (so filter out all other events).

I used this command:

bucket _time span=1d | dedup user, _time | timechart span=1d dc(user)

This is just an testexample I tried out. The same result should appear, when I only use | timechart span=1d dc(user). But there a very small differences.

Is there a better function that can be used here?

Thanks in advance

Heinz

Re: Keep Last Daily Activity Per User

martin_mueller — Thu, 31 Oct 2013 18:17:06 GMT

I'm not sure why you're trying to answer "last daily activity" with a distinct count... here's a thought:

index=_internal earliest=-h@h latest=@h | timechart span=15m first(_raw) by sourcetype

This looks at an hour of _internal data and gives you the last activity per sourcetype. I'm sure you can adapt this for your specific needs.

PS: If you need German-language help (judging by your name) we can certainly work something out.

Re: Keep Last Daily Activity Per User

somesoni2 — Thu, 31 Oct 2013 18:25:28 GMT

You can try the following

"base search"| stats last(*) as * by user,date_mday

This should give you all the fields from the last event for each user for each day.

Re: Keep Last Daily Activity Per User

kristian_kolb — Thu, 31 Oct 2013 19:01:48 GMT

While the answers below seem ok, you already have something similar; perhaps you could expand this with more fields;

... | bucket _time span=1d | dedup user, _time, sourcetype, host

if you want have events on a per sourcetype/host basis.

..and dc(user) will not produce anything near what the title of the question says.

Re: Keep Last Daily Activity Per User

HeinzWaescher — Mon, 28 Sep 2020 15:10:34 GMT

Hi everybody and thanks for your answers.

Unfortunately, I dont think it's possible to adjust your ideas to my search.
To avoid misunderstandings, i will post my whole search and try to explain the goals.

What i want to achieve is a timechart, which shows the daily active (unique) users and the average age of these users on that day. The age (in days) of user A should be defined in every event as the timespan between his current eventtimestamp and his first activity in the dataset. The first activity (in seconds) of each user is defined in a lookuptable "first_activity.csv".

To achieve, that every user is only taken into account once per day to calculate the average age ,i just want to use the age of each users last daily event.

So what I've done is this:

Best
Heinz

Re: Keep Last Daily Activity Per User

martin_mueller — Fri, 01 Nov 2013 13:38:19 GMT

So... does your search now solve your problem? I'm not quite sure if I understand you correctly in that regard 😞

My offer for German-language help still stands if necessary, even beyond this single question.

Re: Keep Last Daily Activity Per User

HeinzWaescher — Mon, 28 Sep 2020 15:10:37 GMT

Hi Martin,

im Grunde tut die Suche was ich möchte. Ich habe ein paar Stichproben für einzelne User gemacht. Allerdings scheint mein Ansatz "das letzte Event des Tages pro User" zu behalten" nicht optimal zu sein. Wenn ich

"| bucket _time span=1d| dedup user,_time"

aus meiner Suche entferne, dürfte sich ja eigentlich nichts an der Anzahl der Unique_Users pro Tag ändern. Aber es kommt zu vereinzelnten (sehr geringen) Veränderungen.

Re: Keep Last Daily Activity Per User

martin_mueller — Fri, 01 Nov 2013 13:54:06 GMT

Moin Heinz,

die Kombination von bucket und dedup gibt keine Garantien darüber, welches Event behalten wird. Schließlich klopft das bucket alle Zeitstempel flach, bevor aussortiert wird.

Am dc(user) sollte das in der Tat nichts ändern. Bei avg(age) habe ich aber Bedenken - ist pro User und Tag vor bucket das age identisch? Falls in der Lookup-Tabelle "krumme" Daten stehen, kann es untertägig den Sprung von einem Tag auf den nächsten geben.

Viele Grüße aus Kiel,
Martin

For the English speakers: In principle the issue is solved with HeinzWaescher's query.

Re: Keep Last Daily Activity Per User

HeinzWaescher — Fri, 01 Nov 2013 14:04:55 GMT

Ich habe gedacht die Zeitstempel werden flachgeklopft, aber die Reihenfolge der Events bleibt bestehen. Entsprechend würde durch "dedup" dann das letzte gewählt. (Falls nicht, wäre das jetzt auch nicht sooo dramatisch. Hauptsache es bleibt erstmal ein einziges pro Tag übrig)

In der Lookuptabelle steht der timestamp der "first activity" in sekunden. Das kann ja aber keinen Einfluss auf die Unique_Users haben.

Das avg_age wird sich durch "dedup" ändern, da pro User nur noch ein age verwendet wird.