topic Re: Splunk query to search for alphanumeric characters or special characters other than numbers in Dashboards & Visualizations

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

ravir_jbp — Mon, 06 Feb 2023 17:51:50 GMT

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

gcusello — Mon, 06 Feb 2023 13:11:12 GMT

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ravir_jbp — Mon, 06 Feb 2023 14:26:18 GMT

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"

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ITWhisperer — Mon, 06 Feb 2023 14:35:36 GMT

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

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

ravir_jbp — Tue, 28 Feb 2023 09:49:43 GMT

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ravir_jbp — Tue, 28 Feb 2023 09:50:17 GMT

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ITWhisperer — Tue, 28 Feb 2023 10:29:52 GMT

Which characters exactly are you wanting?

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ravir_jbp — Tue, 28 Feb 2023 16:58:45 GMT

Hi @ITWhisperer ,

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ITWhisperer — Tue, 28 Feb 2023 18:31:20 GMT

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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ravir_jbp — Fri, 03 Mar 2023 15:07:45 GMT

@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

Re: Splunk query to search for alphanumeric characters or special characters other than numbers

ITWhisperer — Fri, 03 Mar 2023 15:18:14 GMT

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