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

[ravir_jbp]

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

[ravir_jbp]

@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.

[gcusello]

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

[ravir_jbp]

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"

[ITWhisperer]

| regex Employee_Id!="^\d{9}$"

[ravir_jbp]

@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.

[ITWhisperer]

Which characters exactly are you wanting?

[ravir_jbp]

Hi @ITWhisperer ,

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

[ITWhisperer]

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\.\%\,\@\*\&\^\$\#\@\!\)\(]+$"

[ravir_jbp]

@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

[ITWhisperer]

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