Splunk Search

How to span through log timestamps instead of _time

3666142
Path Finder

I'm receiving data from a client where they give me two Key Value Pairs: Time(this is a log timestamp) and NumOfConnections(int)
I want to get a count of events being ingested every 15 secs.

example data created by | stats count as "Transactions" by Time:
Time: "07/23/2019 12:56:12" NumOfConnections:165 Transactions:1
Time: "07/23/2019 12:56:12" NumOfConnections:166 Transactions:1
Time: "07/23/2019 12:56:12" NumOfConnections:167 Transactions:1
Time: "07/23/2019 12:56:13" NumOfConnections:168 Transactions:1
Time: "07/23/2019 12:56:13" NumOfConnections:169 Transactions:1
Time: "07/23/2019 12:56:14" NumOfConnections:170 Transactions:1
This is what I want:
Time: "07/23/2019 12:56:00" NumOfConnections:2475 Transactions:15
Time: "07/23/2019 12:56:15" NumOfConnections:2480 Transactions:15
Time: "07/23/2019 12:56:30" NumOfConnections:2495 Transactions:15
Time: "07/23/2019 12:56:45" NumOfConnections:2490 Transactions:15
Time: "07/23/2019 12:57:00" NumOfConnections:2488 Transactions:15
Time: "07/23/2019 12:57:15" NumOfConnections:2482 Transactions:15

Is there a way to span every 15s even if the timestamp is not _time?

I need something similar to this:

| bin _time span=15s | stats count as Transactions by _time 

But instead of Splunk's ingestion time (_time), I want to use the client's log timestamp (Time).

0 Karma
1 Solution

jaime_ramirez
Communicator

You should use the _time field if already parsed by Splunk, then you could use the bin and stats as you mentioned.

If you would like to use the original Time field anyway here is a simple search (paste and follow the comments):

| makeresults count=20
| rename COMMENT as "..... Sample Data Generated ....."
| eval _time = _time - random()%60
| eval Time=strftime(_time, "%m/%d/%Y %H:%M:%S")
| eval NumOfConnections=random()%200 + 1
| eval Transactions=random()%2 + 1
| table _time Time NumOfConnections Transactions
| rename COMMENT as "Parse the Time field (human-readable to epoch)"
| eval Time=strptime(Time, "%m/%d/%Y %H:%M:%S")
| rename COMMENT as "Grouping based on Time and convert to human readable form"
| bin Time span=15s
| convert ctime(Time)
| rename COMMENT as "Stats function"
| stats sum(Transactions) as Transactions sum(NumOfConnections) as NumOfConnections by Time
| sort Time

Cheers!!!

View solution in original post

jaime_ramirez
Communicator

You should use the _time field if already parsed by Splunk, then you could use the bin and stats as you mentioned.

If you would like to use the original Time field anyway here is a simple search (paste and follow the comments):

| makeresults count=20
| rename COMMENT as "..... Sample Data Generated ....."
| eval _time = _time - random()%60
| eval Time=strftime(_time, "%m/%d/%Y %H:%M:%S")
| eval NumOfConnections=random()%200 + 1
| eval Transactions=random()%2 + 1
| table _time Time NumOfConnections Transactions
| rename COMMENT as "Parse the Time field (human-readable to epoch)"
| eval Time=strptime(Time, "%m/%d/%Y %H:%M:%S")
| rename COMMENT as "Grouping based on Time and convert to human readable form"
| bin Time span=15s
| convert ctime(Time)
| rename COMMENT as "Stats function"
| stats sum(Transactions) as Transactions sum(NumOfConnections) as NumOfConnections by Time
| sort Time

Cheers!!!

View solution in original post

3666142
Path Finder

Much thanks.

0 Karma

3666142
Path Finder

Last thing. Let's say, for example, I put this on a line graph.

One line shows the sum of Transactions every 15s
The other line shows the NumOfConnections per transaction. (so not every 15s)

Is that possible? Thanks.

0 Karma

jaime_ramirez
Communicator

3666142
Path Finder

hmmm if I use the Overlay, won't it still give me the sum of the NumOfConnections per 15s?

I tried taking "sum(NumOfConnections) as NumOfConnections" out of the stats function, but then I cannot use NumConnections for the Overlay.

Thanks again.

0 Karma

jaime_ramirez
Communicator

Maybe this could help you:

| makeresults count=20 
| rename COMMENT as "..... Sample Data Generated ....." 
| eval _time = _time - random()%60 
| eval Time=strftime(_time, "%m/%d/%Y %H:%M:%S") 
| eval NumOfConnections=random()%200 + 1 
| eval Transactions=random()%2 + 1 
| table Time NumOfConnections Transactions 
| rename COMMENT as "Parse the Time field (human-readable to epoch)" 
| eval Time=strptime(Time, "%m/%d/%Y %H:%M:%S") 
| makecontinuous Time span=1s 
| fillnull value=0 
| bin Time span=15s as Time_15 
| eventstats sum(NumOfConnections) as sum_NumOfConnections by Time_15 
| convert ctime(Time) ctime(Time_15)
| sort Time

adonio
SplunkTrust
SplunkTrust

you can just make it the _time field
... your search ... | eval _time = Time ... | bin ... | stats ...

3666142
Path Finder

Thank you. That helped, but how do add up all the NumOfConnections and Transactions that occurred in the 15s window? Also, is there something wrong with the date format? I appreciate the help. Splunk is still a new thing for me.

Here is what I did.

| eval _time = Time | bin _time span=15s | stats count as Transactions by _time NumOfConnections
Time:"07/17/2019 11:00:02" NumOfConnections:92 Transactions:1
Time:"07/17/2019 11:00:17" NumOfConnections:100 Transactions:1
Time:"07/17/2019 11:00:47" NumOfConnections:113 Transactions:1
Time:"07/17/2019 11:47:50" NumOfConnections:139 Transactions:1
Time:"07/17/2019 11:47:51" NumOfConnections:140 Transactions:1

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!