Splunk Search

Regex to query csv raw data

ravir_jbp
Explorer

I have a CSV raw data which has files names and data inside the files which is seperated by double quotes and comma.  I am trying to create following regex  (^\"(?<file_name>\w.*)\"\,\"(?<links_emb>\w.*)\") which is taking results as one event and results. Due to which count is mismaching.  One event has multiple CSV data mentioned below and few events has one file name and data inside the file name.  One file containts multiple files types.  Can you help me with regex which can can take one line as one event.

 

"filename_Time15151515.html","http://testdata1.html"
"filename_Time15151515.html","http://testdata2.gif"
"filename_Time15151515.html",""http://testdata3.doc"
"filename_Time15151515.html",""http://testdata4.xls"
"filename_Time15151515.html",""http://testdata5.aspx"

 

 

^\"(?<file_name>\w.*)\"\,\"(?<links_emb>\w.*)\"

Labels (2)
Tags (1)
0 Karma

ravir_jbp
Explorer

actually need to convert each line into seperate event so that each line can be counted correctly.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Do you mean something like this?

^\"(?<file_name>[^\"]*)\"\,\"(?<links_emb>[^\"]*)\"

Or is this one event that you want to split into multiple lines?

0 Karma

ravir_jbp
Explorer

actual want to splink into miultiple lines.

0 Karma

ravir_jbp
Explorer

@ITWhisperer 

currently csv raw data refelcting in splunk as mentioed below: If you notice event at 2:48:32.000 AM there are multiple csv lines, which is causing confusion.  I am looking for splunk out put as mentioned in 2:49:30.000 AM and 2:50:30.000 AM.

 

Hope this helps.

8/5/24

2:48:32.000 AM
"filename_Time15151515.html","http://testdata1.html"
"filename_Time15151515.html","http://testdata2.gif"
"filename_Time15151515.html",""http://testdata3.doc"
"filename_Time15151515.html",""http://testdata4.xls"
"filename_Time15151515.html",""http://testdata5.aspx"

2:49:30.000 AM
"filename_Time15151515.html",""http://testtest.aspx"

2:50:30.000 AM
"filename_Time46657555.html",""http://tessttestsest.aspx"

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming the filenames are in a field called filenames, you could try this

| eval filenames=split(filenames,"
")
| mvexpand filenames
| rex field=filenames "\"(?<file>[^\"]+)\",\"(?<url>[^\"]+)\""
0 Karma

ravir_jbp
Explorer

 

@ITWhisperer ,

As you are suggesting to use;

index=indexname host=server source="/SEM/Emblinksautomation/UploadEmblinks/Uploadlinks.csv"
| rex "^\"(?<filename>[^\"]*)\"\,\"(?<url>[^\"]*)\""
| eval filename=split(filename,"")
| mvexpand filename
| rex field=filename "\"(?<file>[^\"]+)\",\"(?<url>[^\"]+)\""

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It depends on what you events look like i.e. what the fields are called and what is in them. You should give more accurate information as I don't have access to your data and only know what you have shared so far.

0 Karma

ravir_jbp
Explorer

Okay let me show the exact requirment. Now if you look at the event below. Below data is coming from CSV data. Now 1st event  has muliple csv lines, which is creating confusion when counting the data. These multile events are counted has one event which is not the case. But if you look at second even which has one row with filename and link. I need to seperate 1st event (which has multple lines) into spereate events and need to use table command to list the data in the dashboard.  

When I am runing the regex it shows on 24000 records. But in CSV the files line counts are more than 200000 count. Which is not matching. Not sure why splunk is reading mulitple rows into 1 event.

Hope this helps.

 

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

First, a table "row" in CSV is not defined by linefeed in the document.  If some of your ingested CSV events do not contain CSV header, you need to focus on fixing the ingestion linebreaker problem.  No amount of regex can save broken ingestion and corrupt raw events.

ITWhisperer
SplunkTrust
SplunkTrust

OK it looks like all your data is in _raw?

| eval filenames=split(_raw,"
")
| mvexpand filenames
| rex field=filenames "\"(?<file>[^\"]+)\",\"(?<url>[^\"]+)\""
0 Karma

ravir_jbp
Explorer

This is still not working. Data count is not matching. Can you confirm the complete query again:

index=index host=server source="/UploadEmblinks/Uploadlinks.csv"
| rex "^\"(?<filenames>[^\"]*)\"\,\"(?<url>[^\"]*)\""
| eval filenames=split(_raw,"")
| mvexpand filenames
| rex field=filenames "\"(?<file>[^\"]+)\",\"(?<url>[^\"]+)\""
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Paste your search into a code block (like I have with mine) so it preserves formatting.

0 Karma

ravir_jbp
Explorer
index=index host=host  source="/Emblinksautomation/UploadEmblinks/Uploadlinks.csv" 
| rex "^\"(?<filenames>[^\"]*)\"\,\"(?<url>[^\"]*)\""
| eval filenames=split(_raw,"")
| mvexpand filenames
| rex field=filenames "\"(?<file>[^\"]+)\",\"(?<url>[^\"]+)\""
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You are missing the new line in the split command as shown in my suggestion - try using the command exactly as I suggested

0 Karma
Get Updates on the Splunk Community!

App Building 101 - Build Your First App!

WATCH RECORDING NOW   Tech Talk: App Dev Edition Splunk has tons of out-of-the-box functionality, and you’ve ...

Introducing support for Amazon Data Firehose in Splunk Edge Processor

We’re excited to announce a powerful update to Splunk Data Management with added support for Amazon Data ...

The Observability Round-Up: September 2024

What’s up Splunk Community! Welcome to the latest edition of the Observability Round-Up, a monthly series in ...