Splunk Search

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

ripvw32
New Member

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!

Labels (5)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Super Champion

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

0 Karma

livehybrid
Super Champion

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

livehybrid_0-1749756115496.png

 

 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

0 Karma

ripvw32
New Member

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)

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...