Splunk Search

How to I exclude a field value that contains numbers?

Jhand2016
Explorer

I have a situation where we break out user classes by adding numeric characters at the end of their username. As an example a student may have a username of "jjabrams20" whereas a faculty member or staff would not have the numbers at the end and may be "glucas". I am running a search on authenticated users and want to exclude students from the search but am fairly new to modifying the search parameters. Was thinking originally to use:
"sourcetype=loginslog action=login | where username!=" argument might work but have not found a suitable regex or splunk language to match the alphanumeric exclusion.

Thanks,
Johnny

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

sourcetype=loginslog action=login | where NOT match(username,"\d+$")

OR
Updated command name

 sourcetype=loginslog action=login | regex username!=".+\d+$"

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

sourcetype=loginslog action=login | where NOT match(username,"\d+$")

OR
Updated command name

 sourcetype=loginslog action=login | regex username!=".+\d+$"

Jhand2016
Explorer

I'm trying both, so far no good. It will accept option 1 but returns no results even though individually I can see the events. Option 2 wants to wrap the rex in a format of (?.) as an example but doesn't split out the usernames with/without numerics.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you check if there are any extra space or something is there. Both (2nd one after update, works fine with sample data, see these run anywhere sample searches)

| gentimes start=-1 | eval username=split("jjabrams20 glucas"," ") | mvexpand username | table username 
| where NOT match(username,"\d+$")

| gentimes start=-1 | eval username=split("jjabrams20 glucas"," ") | mvexpand username | table username | regex username!="\d+$"
0 Karma

Jhand2016
Explorer

Sorry, it actually started working, not sure why but after about 15 minutes the same exact text returned results.

I used the following successfully:

| where NOT match(username,"\d+$")

Thanks again.

0 Karma

sundareshr
Legend

Or try this

... | regex username="[A-Za-z]+$"
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 ...