Splunk Search

How to get total count of endpoints based on ClientID?

sameena822
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

woodcock
Esteemed Legend

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
0 Karma

to4kawa
Ultra Champion
| 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

0 Karma

woodcock
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

sameena822
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

sameena822
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

efavreau
Motivator

@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

sameena822
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

woodcock
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

sameena822
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

efavreau
Motivator

@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

sameena822
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...