Splunk Search

search stats

asncari
Engager

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?

 

Labels (1)
Tags (2)
0 Karma
1 Solution

dtburrows3
Builder

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

 

View solution in original post

asncari
Engager

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

Thank you very much!!!!!

0 Karma

dtburrows3
Builder

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

 

Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...