Knowledge Management

how to use rex function different different pattern of data

shishirkumar
Engager

In my scenario data filename having different different of pattern :

Sample filename data :
File_Name | Client_name (Output column)
Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx | Absolute Total Care
OB Incentive Report-December 2018-WellCare of Georgia.xlsx | WellCare of Georgia
Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx | Optum
WellCareSC Qrtrly_Visits_2018Q4.xlsx | WellCareSC **
TotalCare_Dashboard_201812_V1.pdf |
TotalCare**

In Above data we have File_Name and Client name

Idea is : To extract Client Name from File name

to do this I have Lookupfile where client names are stored

Tags (1)
0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval File_Name="Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx|OB Incentive Report-December 2018-WellCare of Georgia.xlsx|Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx|WellCareSC Qrtrly_Visits_2018Q4.xlsx|TotalCare_Dashboard_201812_V1.pdf"
| makemv delim="|" File_Name
| mvexpand File_Name

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval hyphen_based = File_Name
| rex field=hyphen_based mode=sed "s/^.*-//"
| eval File_Name = if(File_Name != hyphen_based, hyphen_based, replace(File_Name, "[_ ].*$", ""))
| rex field=File_Name mode=sed "s/\.[^\.]+$//"
0 Karma

rvany
Communicator

This won't work as the sed part deletes all characters before and including the last dash. The line starting with "Optum" e.g. has no dash at all so the complete file_name is returned.

I like the idea of using a lookup file to check the file_name against it - but the lookup command does only an exact string match. Is there some way to

Expanding your way of creating test data I got the following:

| makeresults 
| eval file_name="Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx|OB Incentive Report-December 2018-WellCare of Georgia.xlsx|Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx|WellCareSC Qrtrly_Visits_2018Q4.xlsx|TotalCare_Dashboard_201812_V1.pdf" 
| makemv delim="|" file_name 
| mvexpand file_name
| eval client_name="Absolute Total Care|WellCare of Georgia|Optum|WellCareSC|TotalCare"
| makemv delim="|" client_name
| mvexpand client_name
| where file_name like "%".client_name."%"

Maybe this could lead in the right direction.

0 Karma

woodcock
Esteemed Legend

Quite right. I have made more adjustments; see my updated answer which works for every file.

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