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?
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")
Iguinn - Didn't work
Have you looked at the fieldformat command?
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fieldformat
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.
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.
Iguinn - Didn't work
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"