topic Re: How to extract the numeric value and IP address from a string in my sample data? in Splunk Search

How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Fri, 06 Oct 2017 16:01:42 GMT

hello,

My log contains below entries.

2017-10-06T04:19:25.658+0000 I NETWORK [initandlisten] connection accepted from 12.34.56.789:12345 #192 (10 connections now open)

I am looking for 2 things.

I want to create a timechart for "Totalconnections". This information will come from the string "(10 connections now open)" and I want to timechart the number 10
I want to count the IPaddress to know how many connections there are per IP.

Re: How to extract the numeric value and IP address from a string in my sample data?

somesoni2 — Fri, 06 Oct 2017 16:41:16 GMT

You need to first capture those IP and connection number into field, like this

your base search | rex "connection accepted from (?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+) connections"

Now you can do total connection timechart like this

above search | timechart sum(ConnectionCount) as TotalConnections

For count of connections per IP address

above search | timechart sum(ConnectionCount) as TotalConnections by IPAddress

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Fri, 06 Oct 2017 17:15:01 GMT

Below query is giving me output 500 to 1100 connections but as per my logs connections are in between 10 to 30.
ring=xxxx source=xxxx "NETWORK" earliest=-4h | rex "connection accepted from (?\d+.\d+.\d+.\d+):[^(]+((?\d+) connections"| timechart sum(ConnectionCount) as TotalConnections

lets not worry about Connection per IP for now, I just need connection count i.e 10
from this string (10 connections now open) because these are real connections.

Here is the log sample:

2017-10-06T04:01:24.889+0000 I NETWORK [conn183] end connection xxx (9 connections now open)

Re: How to extract the numeric value and IP address from a string in my sample data?

somesoni2 — Fri, 06 Oct 2017 17:22:21 GMT

In stats, use max or latest instead of sum.

your base search | rex "connection accepted from (?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+) connections" | timechart max(ConnectionCount) as TotalConnections

your base search | rex "connection accepted from (?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+) connections" | timechart latest(ConnectionCount) as TotalConnections

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Fri, 06 Oct 2017 17:39:44 GMT

Thanks Somesoni!

Can't I get exact connected sessions graph instead of Max/Latest/avg?

Example:
From log entries at 04:05:53.268 I have 12 open connections (I just want to see in my graph 12 at that timestamp) and at 4:19:25.658 I have 10 connections open, so when I do plot a graph I want to see exact count so that I will get idea how many sessions were active at particular time.

2017-10-06T04:05:53.268+0000 I NETWORK [initandlisten] connection accepted from IP:PORT #187 (12 connections now open)

2017-10-06T04:19:25.658+0000 I NETWORK [initandlisten] connection accepted from IP:Port (10 connections now open)

2017-10-06T04:23:55.733+0000 I NETWORK [initandlisten] connection accepted from #193 (10 connections now open)

Sorry I am very new to splunk, we just started using this.

Re: How to extract the numeric value and IP address from a string in my sample data?

somesoni2 — Fri, 06 Oct 2017 17:44:53 GMT

You can actually do this

your base search | rex "connection accepted from (?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+) connections" | table _time ConnectionCount

This will display all the points with corresponding connection count. Please note that there is a limit on how many points can be plotted in the chart so it may not show all points based on how much data you select. See this for more details:
https://docs.splunk.com/Documentation/Splunk/7.0.0/Viz/ChartDisplayissues#Time_charting

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Fri, 06 Oct 2017 17:51:10 GMT

Excellent that worked Somesoni!

But I have multiple hosts on that ring, how do I get per host level?

Re: How to extract the numeric value and IP address from a string in my sample data?

somesoni2 — Fri, 06 Oct 2017 18:02:12 GMT

Try this (will create a new field with same name as value of field host, and that new field will contains corresponding connection count value)

your base search | rex "connection accepted from (?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+) connections" | table _time host ConnectionCount | eval {host}=ConnectionCount | fields - host ConnectionCount

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Mon, 09 Oct 2017 14:11:03 GMT

Somesoni, My graph is not showing as timestamp based, it's giving random time results.

Graph showing like this.
example:
First it showing 13:19:31 sessions count, second 13:48:01 sessions count and then 13:39:03 timestamp sessions count, it just shows random order.

Re: How to extract the numeric value and IP address from a string in my sample data?

kamlesh_vaghela — Tue, 10 Oct 2017 13:45:34 GMT

Hi Chandukreddi,

can you please try below search??

YOUR SEARCH | rex "(?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+)" | chart sum(ConnectionCount) as ConnectionCount over _time by  IPAddress

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Tue, 10 Oct 2017 15:41:11 GMT

Thanks Kamlesh! it worked!!

could you please help me on above session count timechart? Somesoni was trying to help me but still I am not getting expected output.

Re: How to extract the numeric value and IP address from a string in my sample data?

kamlesh_vaghela — Wed, 11 Oct 2017 05:17:26 GMT

Hi ChandukReddi,
Sure ..
Can you please let me know what you expect as session count from below sample event?

2017-10-06T04:05:53.268+0000 I NETWORK [initandlisten] connection accepted from IP:PORT #187 (12 connections now open)

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Wed, 11 Oct 2017 13:54:23 GMT

Hi Kamlesh,

I wan to see a number of open connections in timechart graph from above sample log.

2017-10-06T04:05:53.268+0000 I NETWORK [initandlisten] connection accepted from IP:PORT #187 (12 connections now open)

At time "2017-10-06T04:05:53" there were total "12 connections now open", I want to see this session count in graph.

and also in this ring we have multiple hosts and each host will have same kind of log, so want to get the count by host.

Mostly I will see open sessions count graph in last 1 hr per minute.

Please let me know if it is not clear.

Re: How to extract the numeric value and IP address from a string in my sample data?

kamlesh_vaghela — Thu, 12 Oct 2017 06:05:53 GMT

Hi Chandu,

Can you please execute below search for last 1 hour??

 | rex "(?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+)" | bucket _time span=1m | chart sum(ConnectionCount) as ConnectionCount over _time by  IPAddress

Here I have considered "IP" as a host.
This search will provide you host wise sum of session count by every minute.

Kindly correct me if I'm wrong in IP considerations.

Re: How to extract the numeric value and IP address from a string in my sample data?

chandukreddi — Thu, 12 Oct 2017 15:09:35 GMT

Hi Kamlesh,

No we should not count by IP addres, Here is IP address is client ip address.

I am just looking for total connection in cluster (we have 3 nodes in cluster) and in our logs it shows how many connections were open at that particular time period.

I just want to filter "12 connections now open" this string from bellow sample log and grep for number 12 and show them in the graph.

2017-10-06T04:05:53.268+0000 I NETWORK [initandlisten] connection accepted from IP:PORT #187 (12 connections now open)

Please let me know if I am not clear.

Thanks for your help!

Re: How to extract the numeric value and IP address from a string in my sample data?

kamlesh_vaghela — Fri, 13 Oct 2017 04:51:20 GMT

Hi Chandu,
I'm just trying to understand log files and how they forwarded to the indexer.

These logs are coming from clusters ( means from all the hosts of the cluster). Am I right ?? So we can use host field as cluster host.

Please check below search. This search will show you a timeline of host wise connection.

YOUR SEARCH | rex "(?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+)" | chart values(ConnectionCount) as ConnectionCount over _time by  host

If you found multiple values in ConnectionCount then we have to take latest value from them. So In this case use below search.

YOUR SEARCH | rex "(?<IPAddress>\d+\.\d+\.\d+\.\d+):[^\(]+\((?<ConnectionCount>\d+)" | chart latest(ConnectionCount) as ConnectionCount over _time by  host