URL aggregation in splunk query

[super_edition]

Hello Everyone,

Below is my splunk query:

index="my_index"  uri="*/experience/*"
| stats count as hits by uri
| sort -hits
| head 20

which returns me the output as below

/ae/english/experience/dining/onboard-menu/	1
/ae/english/experience/woyf/	2
/uk/english/experience/dining/onboard-menu/	1
/us/english/experience/dining/onboard-menu/	1
/ae/arabic/experience/dining/onboard-menu/	1
/english/experience/dining/onboard-menu/	1

I need to aggregate the url count into common url. For example:

/experience/dining/onboard-menu/	5
/experience/woyf/	2

Appreciate your help on this.

Thanks in advance

[super_edition · Accepted solution]

Thanks Everyone for your response. Highly Appreciate your input. I was able to construct the query something like this:

index="my_index"  uri="*/experience/*"
| eval common_uri = replace(uri, "^(/[^/]+){1,2}(/experience/.*)", "\2")
| stats count(common_uri) as hits by common_uri
| sort -hits
| head 20

[PickleRick]

But what constitutes those as "common"? As long as you can answer this question, adjusting your results will be relatively easy.

[super_edition]

what constitutes those as "common"?

The onboard-menu url hits same service. Its only accessed from different "markets" which are: 

/ae/english , /uk/english , /us/english , /ae/arabic and /english

like that we will have multiple markets starts /country_code/english or arabic/

[PickleRick]

You might simply cut the prefix from your URI. Something like this

| rex mode=sed field=uri "s/^\\/\S+((arabic|english)\\/)?//"

 @yuanliu 's pooint about /experience/ part is also valid. But searching for */experience/* is not a best idea (search terms with wildcards at the beginning are usually best avoided).

[yuanliu]

Do you know your application path always starts with /experience?  If so, @livehybrid 's method should work, just replace url with uri.

index="my_index"  uri="*/experience/*"
| rex field=uri "(?<uniqueURI>/experience/.*)"
| stats count as hits by uniqueURI
| sort -hits
| head 20

 If not, you can enumerate, or use some other methods to determine the beginning of application path.

[livehybrid]

Hi @super_edition 

A simple REX command to split out should hopefully work well here:

| rex field=url "(?<commonUrl>\/experience\/.*)\/?"
| stats count by commonUrl

Full example:

|makeresults count=2
| streamstats count
| eval url=case(count==1,"/us/english/experience/dining/onboard-menu/",count==2,"/ae/english/experience/dining/onboard-menu/")
| rex field=url "(?<commonUrl>\/experience\/.*)\/?"
| stats count by commonUrl

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[kiran_panchavat]

@super_edition 

| makeresults 
| eval data="/ae/english/experience/dining/onboard-menu/=1;/ae/english/experience/woyf/=2;/uk/english/experience/dining/onboard-menu/=1;/us/english/experience/dining/onboard-menu/=1;/ae/arabic/experience/dining/onboard-menu/=1;/english/experience/dining/onboard-menu/=1" 
| makemv delim=";" data 
| mvexpand data 
| rex field=data "(?<uri>[^=]+)=(?<count>\d+)" 
| eval count=tonumber(count)
| eval normalized_uri = replace(uri, "^/[^/]+/[^/]+", "") 
| stats sum(count) as hits by normalized_uri