How to get total count of endpoints based on ClientID?

[sameena822]

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.

[woodcock]

Like this:

index="YourIndexHere" AND sourcetype="xx" AND "/v1/{*}/billToAccount/division/{*}/customer/{*}/billTo"
| rex "^[^\]\n]*\]\s+\-\s+\w+\s+(?P[^,]+)" 
| stats count(Endpoint) AS TotalCount BY clientID Endpoint
| rename clientID as Consumer 
| sort 0 -TotalCount 
| addcoltotals

[to4kawa]

| makeresults 
| eval raw="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
 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
 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" 
| makemv delim="
 " raw 
| mvexpand raw 
| table raw 
| rename raw as _raw 
| rex "(?: \-).*( starting| to) (?<endpoint>[^,]+)" 
| rex field=endpoint "(?<URL>\/v1[^?]+)" 
| kv 
| stats count by clientID URL

there is a few sample log, I am not sure the result is corrent

[woodcock]

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

[sameena822]

@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!

[sameena822]

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 🙂

[efavreau]

@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.

[sameena822]

@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!

[woodcock]

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.

[sameena822]

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!

[efavreau]

@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

[sameena822]

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.