Calculate eval (_raw,field) stats time Field - Field = TotalTime

[Xe03kfp]

This is what I have

( 2222222 dest_port="*") OR (1111111 src_port="*") 
| eval disconnect_time=if(match(_raw,"2222222"),time,null()) 
| eval connect_time=if(match(_raw,"1111111"),time,null()) 
| eval Ephemeral=if(isnotnull(disconnect_time),dest_port,myPortField) 
| eval Ephemeral=if(isnotnull(connect_time),src_port,Ephemeral) 
| stats min(connect_time) as connect max(disconnect_time) as disconnect by Ephemeral

Gives me -=THIS=- In Bold Black is what I'd like to have.

Secondly, the "Ephemeral" ports, I'd like to have nulled/not show. ??
-They do not have a Connect & Disconnect time -"not associated" with anything else. --They are a snort signature false positive.

Is this possible? I am in a huge jam at work where my job is on the line... 😞

[Xe03kfp]

index="snort"
( 2222222 dest_port="") OR (1111111 src_port="") OR ( 1111111 src_ip="") OR (2222222 dest_ip="")
| eval disconnect_time=if(match(_raw,"2222222"),_time,null())
| eval connect_time=if(match(_raw,"1111111"),_time,null())
| eval Ephemeral=if(isnotnull(disconnect_time),dest_port,Ephemeral)
| eval Ephemeral=if(isnotnull(connect_time),src_port,Ephemeral)
| stats min(connect_time) as Connect max(disconnect_time) as Disconnect min(src_ip) as "Source IP" by Ephemeral
| eval Total_Time(secs)=Disconnect-Connect
| convert timeformat="%a %b-%d %Y "at" %H:%M:%S" ctime(Connect) ctime(Disconnect)
| fieldformat "Calculated Time"=strftime('Total_Time(secs)', "%M:%S")
| search Connect=* Disconnect=* | rename Ephemeral as "Connection Port"

---

Looks like - THIS!

Thanks to the help of Sideview!!

[Xe03kfp]

sideview:

Got it working minus adding a field "src_ip of 1111111"

index="snort"
( 2222222 dest_port="*") OR (1111111 src_port="*")
| eval disconnect_time=if(match(_raw,"2222222"),_time,null()) 
| eval connect_time=if(match(_raw,"1111111"),_time,null())
| eval Ephemeral=if(isnotnull(disconnect_time),dest_port,Ephemeral) 
| eval Ephemeral=if(isnotnull(connect_time),src_port,Ephemeral)
| stats min(connect_time) as Connect max(disconnect_time) as Disconnect by Ephemeral 
| eval Total_Time(secs)=Disconnect-Connect 
| convert timeformat="%F" ctime(Connect) ctime(Disconnect) 
| fieldformat "Calculated Time"=strftime('Total_Time(secs)', "%M:%S")
| search Connect=* Disconnect=*

[Xe03kfp]

Ideas on adding a field column? src_ip of 1111111 and NOT 2222222 ( because it will be a different IP )

[martin_mueller]

The getting-rid-of-lines appears to be based on whether the connect field is set or not? Try ... | search connect=*

Calculating the differences is just a matter of adding another eval, potentially with some time format meddling.

[sideview · Accepted solution]

You changed a couple things from the other question/answer that will make it not work.

Splunk's time field is called "_time", so you need that underscore there. I think you trimmed those off, and

( 2222222 dest_port="*") OR (1111111 src_port="*") 
| eval disconnect_time=if(match(_raw,"2222222"),_time,null()) 
| eval connect_time=if(match(_raw,"1111111"),_time,null()) 
| eval port=if(isnotnull(disconnect_time),dest_port,port) 
| eval port=if(isnotnull(connect_time),src_port,port) 
| stats min(connect_time) as connect max(disconnect_time) as disconnect by port

and if you would like to filter off the rows that do not have both connect and disconect, then you just tack another search clause on the end.

making for a final search of:

( 2222222 dest_port="*") OR (1111111 src_port="*") 
| eval disconnect_time=if(match(_raw,"2222222"),_time,null()) 
| eval connect_time=if(match(_raw,"1111111"),_time,null()) 
| eval port=if(isnotnull(disconnect_time),dest_port,port) 
| eval port=if(isnotnull(connect_time),src_port,port) 
| stats min(connect_time) as connect max(disconnect_time) as disconnect by port
| search connect=* disconnect=* 

And I think the way you were using the Ephemeral field name there was reflecting some confusion, so I changed the constructed field name to just "port"

[sideview]

for converting unix time (aka epochtime) to a string format, look up the strftime() function in the eval command. And for converting a duration to HH:MM:SS, look at the convert command. http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/ListOfSearchCommands

[sideview]

With regard to the intentions error, I'm afraid that complex searches and drilldowns don't mix terribly well. My strong recommendation is to start using Sideview Utils, because it dispenses with intentions entirely, you get a much simpler set of everyday things to deal with and remember, and you get quite a bit more freedom and features when you need it. screencast at http://www.youtube.com/watch?v=9UTiJ65tlmY , and you can download it free for internal use at http://sideviewapps.com/apps/sideview-utils

[Xe03kfp]

AND --How do I convert Unix time to standard time format and convert the "Connection Time" to Hours:Minutes:Seconds

Via Google and Answers searches I have tried:

|fieldformat _time=strftime('duration', "%H:%M$S")
| convert TIME_FORMAT = %H:%M:%S
| Fieldformat timeVariable = totalCount(timeVariable,"duration")

To me it is a matter of possibly putting the time formatting after the | stats min(connect_time) as connect ........... string and then have the eval totalCount= after the time formatting string(that I do not know) ?

[Xe03kfp]

Search String:

index="snort"
( 2222222 dest_port="") OR (1111111 src_port="")
| eval disconnect_time=if(match(_raw,"2222222"),_time,null())
| eval connect_time=if(match(_raw,"1111111"),_time,null())
| eval Ephemeral=if(isnotnull(disconnect_time),dest_port,Ephemeral)
| eval Ephemeral=if(isnotnull(connect_time),src_port,Ephemeral)
| stats min(connect_time) as connect max(disconnect_time) as disconnect by Ephemeral | eval totalCount=disconnect-connect
| search connect=* disconnect=*
| rename totalCount as "Connection Time"

[Xe03kfp]

Fixed but ran into another issue with a "post-reporting 'eval; command --error. I know why but no matter where I put the eval, I stil. receive a parsing error. -=See Here=-

[sideview]

I'm not sure what's going on. Where are you running these searches? I would run them in the 'advanced charting view', and just make sure the field names aren't off by an uppercase letter. When in doubt just backtrack pipe by pipe. eval and stats are really pretty simple - there's no magic. | eval totalCount=disconnect-connect will create a new field called totalCount, assuming that there are events that have both disconnect and connect that are numerical. 😉

[Xe03kfp]

I've tried a few things:
The above and | stat sum(eval(disconnect - connect)) as Total ( I believe yours gave me a return summary but did not create a new field) As well as others but those gave me parsing problems and yours and mine gave a blank result but "97 results" It giving a blank result that it kinda works? or does not know what to do with it because there is something missing? If I use | addtotals, I get "ports" as the result, just a mirror of the same numbers.

[sideview]

yep. you're getting the hang of it. Pay close attention to the field names though, and be aware that field names are case sensitive

| eval totalCount = disconnect - connect | stats max(totalCount) as maxCount

Note that with that stats on the end, the whole thing will boil down to a single row with a single "maxCount" field.

[Xe03kfp]

Understood. As for the total Calculation:

Disconnect time - Connect Time = TimeDifference in minutes, instead of seconds.

I assume I tack on another stats or eval clause like:

|eval totalCount = disconnect_time - Connect_time
| max(totalCount) as TotalCount

?

[Xe03kfp]

Link to the background of this and link to the answer

-= HERE =-

[Xe03kfp]

I have until next Friday to produce something. sigh

[Xe03kfp]

-=HERE=- is a log file, if this makes testing easier. I wish I was not kidding about my job is on the line...

Right Click and Save as 🙂