The way I read your premise, this sounds like a transaction logic. So, let me first clarify your use case. You data look like _time id message state 1969-12-31 16:00:00 101 executed started 1969-12-31 16:00:04 102 activity printed started 1969-12-31 16:00:09 101 null in progress 1969-12-31 16:00:10 102 null in progress 1969-12-31 16:00:18 102 none completed 1969-12-31 16:00:24 101 none completed Note I added some time interleave between 101 and 102 to make the transaction nature more obvious. (Never mind the date is from 1969; that is just for ease of emulation.) You want to use some results like _time duration eventcount id message state 1969-12-31 16:00:04 14 3 102 activity printed completed<-in progress<-started 1969-12-31 16:00:00 24 3 101 executed completed<-in progress<-started Here, I ignored the format of the expected output in your earlier comment, just want to clarify that "state" goes through "started", "in progress", and "completed" to form a transaction for each unique "id". Your material requirement is to obtain a single value for "message" that is NEITHER "null" nor "none". Is this correct? The result as illustrated here can be obtained with | transaction id startswith="state=started" endswith="state=completed"
| eval message = mvfilter(NOT message IN ("none", "null"))
| eval state = mvjoin(state, "<-") The first two commands literally implements my interpretation of your intentions. The third line is just a visual element to make state transition obvious for each . In my mind, the above results table is sufficient, and is more representative of the problem. But if you really want to list each event, like _time id message state 1969-12-31 16:00:00 101 executed started 1969-12-31 16:00:04 102 activity printed started 1969-12-31 16:00:09 101 executed in progress 1969-12-31 16:00:10 102 activity printed in progress 1969-12-31 16:00:18 102 activity printed completed 1969-12-31 16:00:24 101 executed completed You can either use eventstats | eventstats values(message) as message by id| eval message = mvfilter(NOT message IN ("none", "null"))
| eval message = mvfilter(NOT message IN ("none", "null")) or streamstats as @bowesmana suggested | streamstats values(message) as message by id| eval message = mvfilter(NOT message IN ("none", "null"))
| eval message = mvfilter(NOT message IN ("none", "null")) To emulate input, I added _time into @bowesmana's formula because it's just simpler. | makeresults format=csv data="id,message,state,_time
101,executed,started,0
102,activity printed,started,4
101,null,in progress,9
102,null,in progress,10
102,none,completed,18
101,none,completed,24"
| eval _raw = "doesn't matter" ``` mock field _raw is important for transaction ```
``` data mockup above ```
... View more
