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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...