topic Re: Regex to query csv raw data in Splunk Search

Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 13:57:43 GMT

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.*)\"

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 14:56:17 GMT

Do you mean something like this?

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

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

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 15:02:18 GMT

actual want to splink into miultiple lines.

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 15:07:39 GMT

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

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 15:18:27 GMT

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

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 15:40:19 GMT

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

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

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 15:57:59 GMT

@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>[^\"]+)\""

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 16:00:10 GMT

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.

Re: Regex to query csv raw data

ravir_jbp — Tue, 06 Aug 2024 03:52:41 GMT

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.

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 16:36:16 GMT

OK it looks like all your data is in _raw?

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

Re: Regex to query csv raw data

yuanliu — Mon, 05 Aug 2024 16:43:33 GMT

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.

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 18:39:32 GMT

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>[^\"]+)\""

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 16:46:08 GMT

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

Re: Regex to query csv raw data

ravir_jbp — Mon, 05 Aug 2024 16:54:22 GMT

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

Re: Regex to query csv raw data

ITWhisperer — Mon, 05 Aug 2024 17:12:59 GMT

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