Calculate totals from columns but keep summary

[Xe03kfp]

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?

[lguinn2]

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")

[Xe03kfp]

Iguinn - Didn't work

-=See Here=-

[emiller42]

Have you looked at the fieldformat command?

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fieldformat

[lguinn2]

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.

[Xe03kfp]

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.

[Xe03kfp]

Iguinn - Didn't work

-=See Here=-

[Xe03kfp]

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"