How to recognize flat patterns in separate time periods?

[yuanliu]

This is a continuation of How to recognize a flat pattern in a given time period which @lguinn solved with a combination of appendpipe, head, and untable. It concerns three patterns in two distinct time periods. Now I realize that my | timechart count by ID contains three distinct time periods, resulting in more possible patterns.

In the graph, four different patterns are observable:

1. Flat, nonzero at beginning, then fluctuate all the way to end.
2. Flat, zero at beginning, then fluctuate all the way to end.
3. Fluctuate from beginning all the way to end.
4. Flat, nonzero at beginning, fluctuate in the middle, then flat, zero at end.

Though not in my case, one could easily extend this to several more "flat vs non-flat, zero vs nonzero" combinations. Using @lguinn's method, I can distinguish patterns at beginning by

mysearch | timechart count by ID
 | appendpipe [ head 24
   | untable _time ID count
   | stats stdev(count) as sdev max(count) as max by ID
   | eval headpattern=case(max==0,"Zero at beginning",
                       max>0 and sdev < .25,"Flat at beginning",
                       1==1,"Random")
   | fields ID headpattern ]
| stats dc(ID) as Count by headpattern

I can also distinguish patterns at end by

mysearch | timechart count by ID
 | appendpipe [ tail 24
   | untable _time ID count
   | stats stdev(count) as sdev max(count) as max by ID
   | eval tailpattern=case(max==0,"Zero at end",
                       max>0 and sdev < .25,"Flat at end",
                       1==1,"Random")
   | fields ID tailpattern sdev max ]
| stats dc(ID) as Count by tailpattern

However, if I try to combine the two in order to do | stats dc(ID) as Count by headpattern tailpattern, the magic disappears. Here is what I have tried:

mysearch | timechart count by ID
 | appendpipe [ head 24
   | untable _time ID count
   | stats stdev(count) as sdev max(count) as max by ID
   | eval headpattern=case(max==0,"Zero at beginning",
                       max>0 and sdev < .25,"Flat at beginning",
                       1==1,"Random")
   | fields ID headpattern ]
 | appendpipe [ tail 24
   | untable _time ID count
   | stats stdev(count) as sdev max(count) as max by ID
   | eval tailpattern=case(max==0,"Zero at end",
                       max>0 and sdev < .25,"Flat at end",
                       1==1,"Random")
   | fields ID tailpattern sdev max ]
| stats dc(ID) as Count by headpattern tailpattern

No result comes out. How can I detect patterns in both time regions?

[Richfez]

What a great search!

I get results when I modified it like so:

mysearch | timechart count by ID
  | appendpipe [ head 24
    | untable _time ID count
    | stats stdev(count) as sdev max(count) as max by ID
    | eval pattern=case(max==0,"Zero at beginning", max>0 and sdev<.25,"Flat at beginning", 1==1,"Random at beginning")
    | fields ID pattern ]
  | appendpipe [ tail 24
    | untable _time ID count
    | stats stdev(count) as sdev max(count) as max by ID
    | eval pattern=case(max==0,"Zero at end", max>0 and sdev<.25,"Flat at end", 1==1,"Random at end")
    | fields ID pattern ]
 | stats dc(ID) as Count by pattern

Really there were only a couple of minor changes: 1) I changed the evals to eval to the same field name, "pattern". 2) I changed the values pattern could take on to all be distinct (because you are using dc later), so hence "Random at beginning" and "Random at end" instead of just "Random" for each. 3) I cleaned up what's being returned by tail end of things (you can add max and sdev back into the output if you want, but if you don't need it...). 4) Then finally, I did the count by pattern.

My test data doesn't fit your criteria so is always "random" but I get this as a result now.

pattern                 Count
Random at beginning     2
Random at end       4 

[yuanliu]

Good move, @rich7177. With the four IDs used in the example chart, I get

Pattern             Count
Flat at beginning       2
Random at beginning 1
Random at end       5
Zero at beginning       1
Zero at end         1

So except for "Random at end" part, it's all right. I'll sleep on why the same field name passes through, and worry about how to dedupe the outputs into sequences tomorrow.

[yuanliu]

except for "Random at end" part

values(ID) reveals field names "Pattern" and "ID" among values of ID in "Random at end" group. Unsure what causes this weird outcome. At least the "real" values are correct.

[Richfez]

Could you paste some examples of that? It seems odd, but probably easily fixable and we can help with that.

If you have straightened this out already, could you paste what fixed it and marked the answer Accepted to help others in the future? Thanks!

[yuanliu]

@rich7177 Thanks for the follow-up. No I haven't solved the problem, and haven't got time to dig too deeper before dragged into other matters. I will come back with examples after this.