search stats

[asncari]

Hi,

I have a log with several transactions, each one have some events. All event in one transaction share the same ID. The other events contains some information each one, for example, execution time, transact type, url. login url, etc.... This fields can be in one or several of the events.

I want to obtain the total transactions of each type in spanned time, for example each 5m.

I need to group the events of each trasaction for extract the info for it.

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID

This is Ok, if i want count transactType then i do:

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID
|stats count by transactType

The problem is if i want to obtain that in a span time:
I cant do this because there is some events with the transactType field in one transaction:

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| timechart span=5m count by transactType

And following query dont give me any result:

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID
| timechart span=5m count by transactType

Im tried too (but i dont get results):

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| bucket Fecha span=5m
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID
|stats count by transactType

Or:

index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID
| bucket Fecha span=5m
|stats count by transactType

How can i obtain what i want?

[asncari]

I've tried this, but without de "as _time" Now works perfect.

Thank you very much!!!!!

[dtburrows3 · Accepted solution]

The field _time needs to be available at the time of using the "| timechart " command

you example of:
index=prueba source="*blablabla*" 
| eval Date=strftime(_time,"%Y/%m/%d")
| eval Time=strftime(_time,"%H:%M:%S")
| eval Fecha=strftime(_time,"%Y/%m/%d %H:%M:%S")
| rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
| stats values(Fecha) as Fecha, values(transactType) as transactType by ID
| timechart span=5m count by transactType

is not carrying over the_time field from the raw events.
In the bolded SPL above the stats transformation will need some sort of method of carrying over the _time field

I would recommend either a

    | stats 
        earliest(_time) as _time,
        values(Fecha) as Fecha, 
        values(transactType) as transactType 
            by ID
    | timechart span=5m 
        count as count 
            by transactType

OR a 

    | stats 
        latest(_time) as _time,
        values(Fecha) as Fecha, 
        values(transactType) as transactType 
            by ID
    | timechart span=5m 
        count as count 
            by transactType

(depending on what makes more sense for your scenario) 

So example of your Full SPL would look something like this:

index=prueba source="*blablabla*" 
    ``` The field ID is assumed to already be extracted ```
    ``` regex extraction of transactType field ```
    | rex "^.+transactType:\s(?P<transactType>(.\w+)+)"
    ``` transform raw events to singular events, each representing a unique ID with their own list of tranactType value and _time value ```
    | stats 
        latest(_time) as _time,
        values(transactType) as transactType 
            by ID
    ``` make a time series tallying up all the unique IDs belonging to the unique transactType values in 5 minute buckets ```
    | timechart span=5m 
        count as count 
            by transactType