Splunk Search
Highlighted

How to get total count of endpoints based on ClientID?

New Member

I am trying find solution to get Total count of URL Endpoints by field.
In this case by ClientID.

Below is example of Endpoint:

/{version}/{tenantId}/search/account/{AccountNumber}. 

As Endpoints are dynamic, is there any way to get results that include URLs, Total Count by Consumer.

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

Builder

@sameena822 Can you add a log sample (fuzz any sensitive info)? That way we can see...
- what your logs look like, in case there's a need for a regular expression to get the fields you need, beyond what you posted
- what you mean by dynamic, because this sounds ambiguous
- what a single "event" is, in order to get a count

###

If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

New Member

Hi @efavreau , Below are sample of events.

Sample 1:
2019-11-22 13:33:00,394 http-nio-8101-exec-11 INFO RequestFilter [trace=0909642b66224b25, span=d9b011f5c79bd083, userID=, clientID=ele-app-identity-usr, appName=IRIS,ele-app-identity, deviceId=a4393ebf3b17713ef24a2b77acaf5696] - starting /v1/*/search/account, gitId 0e8af6a, clientId ele-app-identity-usr, userId null, appName IRIS,ele-app-identity, deviceIda4393ebf3b17713ef24a2b77acaf5696

Sample 2:
2019-11-22 13:33:00,999 http-nio-8101-exec-16 INFO PathParmRedirectingFilter [trace=, span=, userID=TCHANDLER, clientID=iris-client, appName=ARKE,ele-app-cc, deviceId=9f4d6f097b554a75ab7bce5b09ac04c5] - Base64 Request Forwarded to /v1/{}/billToAccount/division/DC/customer/{}/billTo?billTo=%2540B09247

Sample 3:
2019-11-22 13:33:00,963 http-nio-8101-exec-9 INFO RequestFilter [trace=02a5d3c96b20fb9c, span=db22b4a3ff5b0520, userID=, clientID=gnp-usr, appName=, deviceId=] - starting /v1/{}/billToAccount/division/ATL/customer/{}/billTo, gitId 0e8af6a, clientId gnp-usr, userId null, appName null, deviceIdnull

And yes, I had to write regular expression to extract Endpoint as field1
I have used below query to get Total Count of Endpoints used by different Consumer:
index=** "/v1/{}/billToAccount/division//customer//billTo" sourcetype=xx | rex "^[^]\n]]\s+-\s+\w+\s+(?P[^,]+)" | stats count(URL) as TotalCount , first(trace) as TraceID by clientID | rename clientID as Consumer | dedup TraceID | table Consumer, TotalCount | sort -TotalCount | addcoltotals

I got following results:
ClientId1 100 Records
ClientID2 20 Records

Now, I am looking group all different endpoints by single ClientID (Consumer):

URL TotalCount
/v1//billToAccount/division/ATL/customer//billTo 100
/v1/*/search/account 2000

Note - Where I have wildcard(*), we have different values.

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

Esteemed Legend

I am completely lost. Is this a question about data already in Splunk or a question about hitting a URL endpoint to get data into Splunk? Either way, we need way more detail to get an answer.

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

New Member

Hi @woodcock , Yes, we already have data in Splunk.
Below are the sample endpoints:
1. /v1/{}/billToAccount/division/{}/customer/{}/billTo
2. /v1/{
}/search/account

I have used below query to get count of Endpoint by each ClientID:
index=** "/v1/{}/billToAccount/division/{}/customer/{}/billTo" sourcetype=xx | rex "^[^]\n]]\s+-\s+\w+\s+(?P[^,]+)" | stats count(Endpoint) as TotalCount , first(trace) as TraceID by clientID | rename clientID as Consumer | dedup TraceID | table Consumer, TotalCount | sort -TotalCount | addcoltotals

Below are my search results:
Consumer Count(Endpoint)
ClientID1 100
ClientID2 20

Now, I am looking for following results:
For ClientIDs:
ClientID1
Endpoint Count
/v1//billToAccount/division//customer//billTo 60
/v1/
/search/account 6

ClientID2
Endpoint Count
/v1//billToAccount/division//customer//billTo 40
/v1/
/search/account 14

Note - Wherever we have wildcard {*) , there is different values each time. Was wondering how do we get count based on endpoint.

Thanks!

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

Builder

@sameena822 I couldn't get your query to work for several reasons. However, you're stating you want to have the endpoint, the count of that endpoint, and to do it by clientID. Assuming you can get your regular expressions in order, you are looking for a stats command:
| stats count(Endpoint) AS Count BY clientID Endpoint
I think what you were missing, was that you can do more than one field after the BY.

###

If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

New Member

@efavreau - I have included | stats count(Endpoint) AS Count BY clientID Endpoint , but still it didn't work.
This is regex which I have used and it worked fine for me [ rex "^[^]\n]*]\s+-\s+\w+\s+(?P[^,]+)" ]. Thank you!

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

Esteemed Legend

Like this:

| makeresults 
| eval Endpoint = "/v1/foo/billToAccount/division/foo2/customer/foo3/billTo /v1/bar/billToAccount/division/bar2/customer/bar3/billTo /v1/bat/billToAccount/division/bat2/customer/bat3/billTo /v1/foo/search/account /vi/bar/search/account /vi/bat/search/account"
| makemv Endpoint
| mvexpand Endpoint
| streamstats count AS trace
| eval clientID = if(trace<=3, "FirstFour", "LastTwo")

| rename COMMENNT AS "Everything above generates sample event data; everything below is your solution"

| rex field=Endpoint "^\/(?<version>[^\/]+)\/(?<segment2>[^\/]+)\/(?<segment3>[^\/]+)\/(?<segment4>[^\/]+)(?:\/(?<segment5>[^\/]+)\/(?<segment6>[^\/]+)\/(?<segment7>[^\/]+)\/(?<segment8>[^\/]+))?"
| rex field=Endpoint mode=sed "s/^(\/[^\/]+\/)[^\/]+(\/[^\/]+\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(.*)$/\1*\2*\3*\4/ s/^(\/[^\/]+\/)[^\/]+(.*)$/\1*\2/"
| stats count BY clientID Endpoint
| sort 0 - count
| stats list(*) AS * sum(count) AS subTotal BY clientID
| rename clientID as Consumer
| addtotals col=t row=f
| fillnull value="GRAND TOTAL" Consumer
| eval count=if(Consumer=="GRAND TOTAL", null(), count)
| table Consumer subTotal count Endpoint
0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

New Member

@woodcock - your solution partially worked .. I was able to get total and grand total. But the thing is, we have close to 100 Endpoints. In that case, how would it work? Thanks!

0 Karma
Highlighted

Re: How to get total count of endpoints based on ClientID?

New Member

Hi @woodcock - I tried modified the query and added up more segments to mode=sed command and I was able to get results but I am not able strip values at the end of URL. Probably there might be minor thing which I might be missing.

Query:
index=xxx sourcetype=xxx earliest=-15m | search (RequestUri="shipToAccount" AND RequestUri="v1") |stats count(RequestUri) as TotalCount , first(trace) as TraceID by HttpMethod, RequestUri | dedup TraceID | rex field=RequestUri mode=sed "s/^(\/[^\/]+\/)[^\/]+(\/[^\/]+\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(.)$/\1\2\3\4\5\6/ s/^(\/[^\/]+\/)[^\/]+(\/[^\/]+\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(.)$/\1\2\3\4\5/ s/^(\/[^\/]+\/)[^\/]+(\/[^\/]+\/[^\/]+\/)[^\/]+(\/[^\/]+\/)[^\/]+(.)$/\1\2\3\4/ s/^(\/[^\/]+\/)[^\/]+(.)$/\1*\2/" |stats sum(TotalCount) as TotalCount by HttpMethod,RequestUri | table HttpMethod, RequestUri, TotalCount

I am getting result like this:
1./v2//shipToAccount/division//customer//billTo//shipTo
2./v2//shipToAccount/division//customer//billTo//shipTo//webToggles
3./v2/
/shipToAccount/division//customer//billTo//shipTo//webToggles?b64BillTo=AAA%3D&b64ShipTo=AAA%3D&orchestrate=true
4./v2//shipToAccount/division//customer//billTo//shipTo//webToggles?b64BillTo=MDAwODA4&b64ShipTo=VE9UQUwgTUdNVCBSRVM%3D&orchestrate=true*
5./v2//shipToAccount/division//customer//billTo//shipTo?shipTo=%40B37242
6./v2//shipToAccount/division//customer//billTo//copyFrom?sub=158479860&b64BillTo=QEI4MDYwOA%3D%3D&channel=web-ele-app-user&title=ShipToInformation

For each segment I would want to strip values at end of URL starting from '?' (in Bold). I tried creating regex on URL field but it is messing up other values.Feels like I am close but need your help 🙂

0 Karma