timechart of events first appearance

[josephpe]

I am trying to find events based on when they were initially logged and grouped by some column. For example,  from the below table, I want find those total count of unique "keyId" that logged first group by "parent1" and using timechart

April 20 | A01 | 2  (DATT-001, DATT-002 first appeared in May )
May 20 | A02 | 1  

Basically first appearance of "keyId" grouped by parent1 and shown by timeline

_time	keyId	parent1	parent2	parent3	status	eventdetails
2020-04-19T23:47:21.000+10:00	DATT-001	A01	B01	C01	Pass
2020-04-20T2:47:21.000+10:00	DATT-001	A01	B01	C01	Fail
2020-05-20T2:47:21.000+10:00	DATT-001	A01	B01	C01	Fail
2020-06-20T2:47:21.000+10:00	DATT-001	A01	B01	C01	Fail
2020-04-20T2:47:21.000+10:00	DATT-002	A01	B01	C01	Fail
2020-05-20T2:47:21.000+10:00	DATT-002	A01	B01	C01	Fail
2020-05-20T2:47:21.000+10:00	DATT-003	A02	B01	C01	Fail

Any help please ?

[ITWhisperer · Accepted solution]

| stats earliest(_time) as _time by keyid parent1
| bin span=1mon _time
| stats count by _time parent1

[josephpe]

Thank you for the query @ITWhisperer , the output has a lot of data even though the bin is specified by 1 mon, is that problem due the _time having the min and second level details. Basically not grouped by month.

2020-10-01 00:11:59

Tried to add timechart to the above query but it comes out empty, am I missing anything here ?

| timechart span=1mon count by name_4

or what should I do to get a month wise group on the data.

[ITWhisperer]

The span=1mon sets all the times to the beginning of the relevant month so minute and second detail is removed. The number of events is down to the combination of keyid and parent1. The stats command has already gathered the data by time (month) keyid parent and count, adding timechart is then charting these results, so for example, there will only be one result for name_4 per month i.e. count equals 1. The problem with timechart is you only have two axis date/time being one axis, count being the other, you then have series based on one field, whereas you as looking for series based on two fields (keyid and parent1). In order to be able to do a timechart, you need to create a new field based on these two fields and chart these. Having said that, if you create the field after the stats in my example, you can then use xyseries _time field count