Is there a way to be more efficient in writing this query - Regex/Wildcard/Substitution question

[ripvw32]

I have the below query I've written - I am used to SQL, SPL is still new to me. I feel like there has to be some way to make this shorter/more efficient - i.e: 

Data:

API_RESOURCE="/v63.0/gobbledygook/unrequitededits_somename_request"

API_RESOURCE="/v62.0/gobbledygook/unrequitededits_somename_response"

API_RESOURCE="/v61.0/gobbledygook/unrequitededits_somename_update"

API_RESOURCE="/v63.0/gobbledygook/unrequitededits_somename_delete"

API_RESOURCE="/v61.0/gobbledygook/unrequitededits_somename_delete"

API_RESOURCE="/v62.0/gobbledygook/unrequitededits_somename_update"

API_RESOURCE="/v61.0/gobbledygook/URI_PATH_batch_updates"

Original query:
index="some_index"

API_RESOURCE!=""

| eval API_RESOURCE=case(

LIKE(API_RESOURCE,"%63%"),"/v63",

LIKE(API_RESOURCE,"%62%"),"/v62",

LIKE(API_RESOURCE,"%61%"),"/v61",1==1, API_RESOURCE)

|stats count by API_RESOURCE

Desired query:

index="some_index"

API_RESOURCE!=""

| eval API_RESOURCE=case(LIKE(API_RESOURCE,"%6\d%"),"/v6\d",1==1, API_RESOURCE)

|stats count by API_RESOURCE

Where the outcome would be the three versions being counted as grouped within their own version (so, v/63 = 2, v/62 = 2, v/61= 2

Every time I run the 'desired query' it completely ignores the wildcard/variable in both the search and replace part of the case statement.

Any help would be appreciated, as there are at least 64 current versions, and every time a new one is developed it gets the next highest version number

Thanks in advance!

[yuanliu]

It shows that you think like SQL.  The API version in your examples is the easiest to extract in Splunk. (In fact, in any modern language other than SQL.) And using case function is about the most complicated method.  @livehybrid and @richgalloway suggested regex.  There is an even simpler and perhaps cheaper method:

| eval version = mvindex(split(API_RESOURCE, "/"), 1)

[livehybrid]

And if you want the full version number after . then try:

| rex field=API_RESOURCE "^/(?<version>v[\d\.]+)\/"

🌟 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

[livehybrid]

Hi @ripvw32 

Use rex with a regular expression to extract or normalize the version segment efficiently, instead of using multiple LIKE or case statements.

For exxample:

| makeresults
| eval API_RESOURCE="/v63.0/gobbledygook/unrequitededits_somename_request"
| append [| makeresults | eval API_RESOURCE="/v62.0/gobbledygook/unrequitededits_somename_response"]
| append [| makeresults | eval API_RESOURCE="/v61.0/gobbledygook/unrequitededits_somename_update"]
| append [| makeresults | eval API_RESOURCE="/v63.0/gobbledygook/unrequitededits_somename_delete"]
| append [| makeresults | eval API_RESOURCE="/v61.0/gobbledygook/unrequitededits_somename_delete"]
| append [| makeresults | eval API_RESOURCE="/v62.0/gobbledygook/unrequitededits_somename_update"]
| append [| makeresults | eval API_RESOURCE="/v61.0/gobbledygook/URI_PATH_batch_updates"]
| rex field=API_RESOURCE "^/(?<version>v\d+)\."
| stats count by version

 rex extracts the version (e.g., v63, v62, v61) from the start of API_RESOURCE. - stats count by version groups and counts by the extracted version. - This approach is scalable and requires no manual updates for new versions.

🌟 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

[ripvw32]

Thank you so much for the response!!!

That didn't seem to do the trick -  Still seeing entries like this in my table

/v63.0/gobbldygook/unrequietededit/describe

Also, I am seeing that API_RESOURCE also contains singular words, like "Update", "Delete", "Login" etc, with no v/2digitnumber (didn't see them before as the data is several dozen pages long, at 100 rows per page)

[richgalloway]

There may be more than one way to do that using regular expressions.  Here's one of them.

| rex field=API_RESOURCE "\/v(?<API_RESOURCE>\d+)"

Use this command line in place of the existing eval.