Dashboards & Visualizations

How to create a search for alphanumeric characters or special characters other than numbers?

ravir_jbp
Explorer

Currently I am using below query to extract the list of employee_ID column has less then 9 digit employee ID.  However, I have another requirement in same table to extract the employee ID with alphanumeric- (like N0001234, etc) and any special characters. 

So overall we need data which is less than 9 digits, more than 9 digits, any alphanumeric characters, special characters.

index=QQQQQ sourcetype="XXXXX*" source=TTTTTT Extension="*" MSID="*" Employee_Active="*" Employee_Id=* last_name="*" first_name="*"| rename Extension as DN| dedup Employee_Id | eval emplength=len(Employee_Id)| stats count by DN, MSID, Employee_Active, emplength,Employee_Id, last_name, first_name| where emplength>9 ]
| table DN, MSID, Employee_Active, emplength,Employee_Id, last_name, first_name

Labels (1)
Tags (2)
0 Karma

ravir_jbp
Explorer

@ITWhisperer 

Thank you for your help. This worked. However, now I was trying to pull only data with Alphabets and any special characters. Can you please assists me on how to extract that information.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @ravir_jbp,

let me understand: you haven't any rule to filter your logs, so you have only to remove all the filters (where):

index=QQQQQ sourcetype="XXXXX*" source=TTTTTT Extension="*" MSID="*" Employee_Active="*" Employee_Id=* last_name="*" first_name="*"
| rename Extension as DN
| dedup Employee_Id 
| stats count by DN MSID Employee_Active Employee_Id last_name first_name
| table DN MSID Employee_Active Employee_Id last_name first_name

Ciao.

Giuseppe

0 Karma

ravir_jbp
Explorer

Hi @gcusello ,

THank you for response.

I am not sure if I get your question but let me rephrase my question:

Below is the raw data. My task is to find the list of employees data which has non-compliant employee ID (refer  Employee_Id="test@test.com here) which shows the e-mail address instead of number. We are trying to create a report for list of employee which has wrong entries other than numeric value. And employee should be 9 digit. We need extract data any thing more than 9 digit or less than 9 digit and having any alpanumeric character or any special characters in employee ID.

 

2023-02-05 23:00:11.663, time="1675659600", row_num="60252", Instance_ID="1", Environment="XXXX", Extension="cpanek", Extension_State="Enabled", MSID="yyyyyy", Assignment="test.test", Employee_Active="Active", Account_Type="Primary Account", Employee_Id="test@test.com", last_name="test", first_name="Christine", Agent_Flag="Yes", User_state="Enabled", dn_dbid="243433", person_dbid="44323", Extension_Folder_Structure="Rxxxx > Switches > cluster > DNs > rrrr > vefect > Oncology > Digital", Person_Folder_Structure="Root > Persons > test > MedBenefitMgmt > Oncology", Unit="dream", Segment="MedBen", Function="Oncology", Manager_Name="test, jerry", Access_Group_List="Agent", Has_VQ="No ", Agent_Last_Login_TS="2021-07-02 00:00:00.0", Agent_Create_TS="2021-04-13 15:17:03.0", Non_Agent_Flag="Y"

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| regex Employee_Id!="^\d{9}$"
0 Karma

ravir_jbp
Explorer

@ITWhisperer 

 

Thank you for your help. This worked. However, now I was trying to pull only data with Alphabets and any special characters. Can you please assists me on how to extract that information.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Which characters exactly are you wanting?

0 Karma

ravir_jbp
Explorer

Hi @ITWhisperer ,

 

Looking only for employee Id which has any specicial characters or ends /starts/middle of employed  like %,@*&^%$##@!)( also alphabets.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this - you hadn't include . so I added that - you would normally have to escape the special characters, but I wasn't sure which of the ones you mentioned needed escaping so I escaped them all

| regex Employee_Id=="^[A-Za-z0-9\.\%\,\@\*\&\^\$\#\@\!\)\(]+$"
0 Karma

ravir_jbp
Explorer

@ITWhisperer 

This works for me. Also how can i add more more condition like employee id more than 9 digits. 

so Ideally  employee ID is that it should have:

- 9 digits employee ID (digits only)

 

- No alphabets

-no special characters

- employee not less than or more than 9 digits

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| regex Employee_Id=="^[A-Za-z0-9\.\%\,\@\*\&\^\$\#\@\!\)\(]+$"
| regex Employee_Id!="^\d{9}$"
0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...