Getting Data In

CSV Field extractions with one field has extra commas with in single quotes

som3guy
Explorer

Here is an example of one log:

20170309 10:41:16,hostname.vagrantup.com,username,localhost,155,9823,QUERY,database_name,'select min(t.nextcheck) from httptest t,hosts h where t.hostid=h.hostid and mod(t.httptestid,5)=4 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)',0

Here is an example of another log:

20170309 10:40:44,hostname,username,localhost,222,0,CONNECT,database,,0

Here is the format:

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

So I am stuck trying to get this correctly setup to have each field configured correctly. As you can see the logs differ based on what type of operation is taking place.
When the operation is a query they will likely have several commas inside of single quotes. Any help will be much appreciated.

Thanks

1 Solution

acruise_splunk
Splunk Employee
Splunk Employee

I was able to ingest your data successfully using the Add Data Wizard, using the following changes...

  1. In the "Set source type" stage, you should choose CSV from the "Source type" dropdown
  2. Under "Delimited settings", which appears after you choose CSV, you should choose single quote as your quote character.
  3. At the end of the process, you should pick a different source type name, so you don't confuse future, more general CSV users.

Accepting all the other defaults worked for me!

View solution in original post

acruise_splunk
Splunk Employee
Splunk Employee

I was able to ingest your data successfully using the Add Data Wizard, using the following changes...

  1. In the "Set source type" stage, you should choose CSV from the "Source type" dropdown
  2. Under "Delimited settings", which appears after you choose CSV, you should choose single quote as your quote character.
  3. At the end of the process, you should pick a different source type name, so you don't confuse future, more general CSV users.

Accepting all the other defaults worked for me!

som3guy
Explorer

Thanks this worked out great.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I've done this a while ago using a REGEX statement in my props.conf file. This regex string should get you started if you want to try that approach.

(?<ts>[^,]+),(?<serverhost>[^,]+),(?<username>[^,]+),(?<host>[^,]+),(?<connectionid>[^,]+),(?<queryid>[^,]+),(?<operation>[^,]+),(?<database>[^,]+),(?<object>'?.*'?),(?<retcode>\d+)
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...