group by date?

[theeven]

Hi folks,

Given: In my search I am using stats values() at some point. I am not sure, but this is making me loose track of _time and due to which I am not able to use either of timechart per_day(eval()) or count(eval()) by date_hour

Part of search:

| stats values(code) as CODES by USER

Current state:
USER CODES(Multi-value)

a    11, 12, 13
b    14, 19, 13
c    15, 12, 13
d    18, 12, 14
e    11, 14, 17

Desired: count CODES by date.
CODES COUNT

11    2
12    3
13    3
14    3
15    1
17    1
18    1
19    1

If I am not wrong, values(code) is making me loose track of _time. Is there a way to get this back?

OR

Can I group on custom timestamp obtained from logs?

| stats values(code) as CODES by USER values(timestamp) as TS 
| eval TSN = mvindex(TS, 0) 

Some how can I use TSN for group by date?

[theeven · Accepted solution]

here's how I have resolved this problem, considering I am loosing _time field after values(). I am now using timestamp from logs and grouping over them.

| stats values(code) as CODES by USER values(timestamp) as TS 
| eval TSN = mvindex(TS, 0) 
| eval HOUR=strftime(TSN,"%H:00") 
| stats count(eval(CODES="11")) by HOUR

above will give hourly counts for CODE="11"

[wpreston]

Yes, I think values() is messing up your aggregation. I would suggest a different approach. Use mvexpand which will create a new event for each value of your 'code' field. Then just use a regular stats or chart count by date_hour to aggregate:

...your search... | mvexpand code | stats count as "USER CODES" by date_hour, USER
             or
...your search... | mvexpand code | chart count as "USER CODES" by date_hour, USER

[wpreston]

_time be propagated to each new event, meaning that each new event will retain the _time information from its parent event. I'm unsure why you wouldn't be able to group by date_hour or use a timechart per_day()... Are you using a fields command earlier in your search? Can you post your complete search?

[theeven]

This approach looks like on the right track as it gives me back line by line entries. But after mvexpand its not able to recover _time field, hence not able group by date_hour OR timechart per_day().

In other case, I was wondering if its possible to use my log timestamp field for grouping?