topic Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI? in Splunk Search

How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

krish899 — Fri, 17 Mar 2017 17:37:45 GMT

Am in a process of creating a report, in which i have URI's from many different hosts hitting from multiple IP's .

Requirement : I would like to have report like this where IP's have a comma separation .

URI                                  Client IP                                              Total count 
-------------                        ----------------                                       --------------
URI/XYZ/service/ENDPOINT      10.256.85.164,10.528.65.313,10.58,65.198                      2500

But my search results this:

sourcetype=xyz index=urx host=jjk*  | extract endpoint-extractions | stats count values(clientip) as ClientIP by uri | sort  by uri

uri                              count                ClientIP
//Services/Service?MMJD          53                  10.166.148.11
                                                           10.166.148.15
                                                           10.166.149.13
/Services/Orders                    22                   10.178.5.152
                                                           10.178.5.153

I would like to get 30 days report for 2000 plus services from different domains. Can use tstats to have the results quickly.

Please help me with search to get the result for 30 days. Highly appreciate your help. Thanks in advance.

Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

woodcock — Fri, 17 Mar 2017 18:08:42 GMT

Just add this:

| nomv ClientIP | rex field=ClientIP mode=sed "s/s\+/,/g"

Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

DalJeanis — Fri, 17 Mar 2017 21:06:46 GMT

Woodcock's should work. This would also work.

| eval ClientIP=mvjoin(ClientIP,",")

Hmmm. Regarding woodcock's code, rex is acting like some other kind of whitespace is in between the IP addresses after nomv, as opposed to a plain vanilla space.

| makeresults | eval ClientIP="10.166.148.11 10.166.148.15 10.166.149.13"  | makemv ClientIP | eval ClientIP1=ClientIP, ClientIP2=ClientIP, ClientIP3=ClientIP
| nomv ClientIP1 | rex field=ClientIP1 mode=sed "s/ /,/g"
| eval ClientIP2=mvjoin(ClientIP2,",")
| nomv ClientIP3 | rex field=ClientIP3 mode=sed "s/\s/,/g"
| table ClientIP ClientIP1 ClientIP2 ClientIP3


ClientIP =
10.166.148.11 
10.166.148.15 
10.166.149.13 

ClientIP1 = 10.166.148.11 10.166.148.15 10.166.149.13
ClientIP2 = 10.166.148.11,10.166.148.15,10.166.149.13  
ClientIP3 = 10.166.148.11,10.166.148.15,10.166.149.13

Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

krish899 — Sat, 18 Mar 2017 00:25:06 GMT

Thanks @woodcock .

Appreciate your help.

I used this command for my report :

basesearch | extract endpoints-extractions | stats count values(clientip) as All_ClientIP by uri | eval clientip=mvjoin(All_ClientIP,",") | fields - All_ClientIP

Now I'll prefer using | eval ClientIP=mvjoin(ClientIP,",") in my next reports .

Thanks.

Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

woodcock — Sat, 18 Mar 2017 02:12:33 GMT

You accepted the wrong answer then! Mine does work (maybe worth upvoting it) but you should UnAccept mine and Accept the answer by @DalJeanis because his is better.

Re: How to generate a search of unique URI and all the client IP's hitting in a commas field and total count of the IP's hitting the URI?

woodcock — Sat, 18 Mar 2017 21:33:22 GMT

Yes, I updated my answer to account for more general variety.