Archive

Calculate totals from columns but keep summary

Path Finder

Search string:

sourcetype=Juniper "closed" | stats count by SRCIP,date,REalm,Username,time

I need to calculate the time and have it display minutes

Snippet of Log:

2010-12-09 10:50:36 - ive - [10.7.0.133] east01(East)[east01] - Closed connection to 192.168.4.95 port 3301 after 3 seconds, with 53040 bytes read (in 38 chunks) and 2967 bytes written (in 25 chunks)

2010-12-09 12:19:02 - ive - [10.32.52.66] north05(Guard)[north05] - Closed connection to 192.168.194.95 port 3305 after 92 seconds, with 351663 bytes read (in 386 chunks) and 8059 bytes written (in 169 chunks)

2010-12-09 12:20:29 - ive - [10.15.165.19] west07(West)[west07] - Closed connection to 192.168.16.95 port 3307 after 72 seconds, with 342907 bytes read (in 347 chunks) and 6182 bytes written (in 118 chunks)

2010-12-09 12:21:50 - ive - [10.43.82.19] east02(East)[east02] - Closed connection to 192.168.4.95 port 3302 after 40 seconds, with 75723 bytes read (in 47 chunks) and 3197 bytes written (in 42 chunks)

What I'm trying to do is have a report that gives me:

Date Username SourceIP TotalTime Bytes(displayed as MB)--need a calculation

Fields: sourcetype=Juniper | SRCIP | Username | time |("after 324 seconds")--time=324 | bytes

I can't figure out when I have a number how to use a field to calculate it to my liking. I have tried eval, sum, and a few others. But when I use eval or sum how do I not cause it to interfere with my search summary and only "convert" the column numbers via my calculation?

Tags (1)
0 Karma

Legend

Okay, a new attempt. I probably should not have calculated the MB until the end, as the number of bytes is small. Also, I have some concerns about the actual contents of your fields.

Start with this to debug your fields

sourcetype=Juniper "closed"
| eval TotalBytes = bytesRead + bytesWritten
| eval Date=relative_time(_time,"@d")
| rename SRCIP as SourceIP
| fieldformat Date=strftime(Date,"%x")
| fieldformat timestamp=strftime(_time,"%x %X")
| eval MB = tostring(round(TotalBytes/1024/1024,2)) + " MB"
| table timestamp Date Username SourceIP bytesRead bytesWritten TotalBytes MB time

If that looks okay, then you can move on to

sourcetype=Juniper "closed"
| eval bytes = bytesRead + bytesWritten
| eval Date=relative_time(_time,"@d")
| rename SRCIP as SourceIP
| stats sum(bytes) as TotalBytes sum(time) as TotalTime by Date Username SourceIP
| fieldformat Date=strftime(Date,"%x")
| eval MB = tostring(round(TotalBytes/1024/1024,2)) + " MB"

And finally

sourcetype=Juniper "closed"
| eval bytes = bytesRead + bytesWritten
| eval Date=relative_time(_time,"@d")
| rename SRCIP as SourceIP
| stats sum(bytes) as TotalBytes sum(time) as TotalTime by Date Username SourceIP
| fieldformat Date=strftime(Date,"%x")
| eval TotalBytes = tostring(round(TotalBytes/1024/1024,2)) + " MB"
| fieldformat TotalTime = tostring(TotalTime,"duration")

Path Finder

Iguinn - Didn't work

-=See Here=-

0 Karma

Motivator
0 Karma

Legend

I am not sure what your question means, but try this anyway. It should get the output that I think you want.

sourcetype=Juniper "closed"
| eval MB = (bytesRead + bytesWritten)/(1024*1024)
| eval Date=relative_time(_time,"@d")
| stats sum(time) as TotalTime sum(MB) as Bytes by Date Username SRCIP
| rename SRCIP as SourceIP
| fieldformat TotalTime=(TotalTime,"duration")
| eval Bytes = round(Bytes,2) + " MB"
 | fieldformat Date=strftime(Date,"%x")

If you don't have the fields defined, include the field extractions as well:

sourcetype=Juniper "closed"
| rex "after (?<time>\d+) seconds, with (?<bytesRead>\d+) bytes read.*? and (?<bytesWritten>\d+) bytes written"
| eval MB = (bytesRead + bytesWritten)/(1024*1024)
| eval Date=relative_time(_time,"@d")
| stats sum(time) as TotalTime sum(MB) as Bytes by Date Username SRCIP
| rename SRCIP as SourceIP
| fieldformat TotalTime=(TotalTime,"duration")
| eval Bytes = round(Bytes,2) + " MB"
| fieldformat Date=strftime(Date,"%x")

I wasn't sure which IP address was the SourceIP or how to define Username. Otherwise I would have included the field extractions for them as well.

0 Karma

Path Finder

Screen capture shows all of my questions. I screen capped the "Total Time" from a version with Fieldformat TotalTime removed from search string and a screen capture paste of just the "Total Time" pasted where I removed FieldFormat to show seconds. I have no idea where it got the numbers. Side by side comparison reflects this.

0 Karma

Path Finder

Iguinn - Didn't work

-=See Here=-

0 Karma

Path Finder

Thankyou Iguinn! 🙂 I will test tomorrow morning.

To answer your question: The SourceIP's are 10.x.x.x

I have the Fields defined using "Build Event" and added as as a field.

"Date Username SourceIP TotalTime Bytes"

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!