I have a SQL query using at Splunk DB Connect to pull the SQL audit log into Splunk as below:
SELECT event_time, action_id, succeeded, session_id, server_principal_id, database_principal_id, object_id, class_type, session_server_principal_name, server_principal_name, database_principal_name, target_server_principal_name, server_instance_name, database_name, schema_name, object_name, statement, file_name, audit_schema_version, transaction_id FROM sys.fn_get_audit_file ('C:\\\\SQLAudit\\\\*',default,default) WHERE event_time > ? ORDER BY event_time ASC
However, the system return error:
"java.sql.SQLException: Conversion failed when converting date and/or time from character string."
The raw event_time field return value in format:
I havw tried to do conversion as below but no luck:
SELECT substr('2018-08-26 10:29:57.3456782', 1, 19) as evt_time,
TO_DATE(substr('2018-08-26 10:29:57.3456782', 1, 19), 'YYYY-MM-DD HH24:MI:SS') as evt_datetime,
to_char(TO_DATE(substr('2018-08-26 10:29:57.3456782', 1, 19), 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD HH24MISS') as evt_datetime
Anyone can give me a hand on this? Thanks.
... View more
Finally solved. The problem was due to userBaseDN value.
It should not be same as the value I set for groupBaseDN. Instead, it should be DN where AD user is first created, e.g OU=Users,OU=xx,OU=xx - xxxx,OU=xx - Users Computers Groups,OU=Organization Unit,DC=test,DC=com
... View more
Hi sk314, I have clicked "Reload authentication configuration" button from the Splunk web UI but the problem was still the same. The reload button actually do the same thing as running ./splunk reload auth command right?
... View more
I have recently configured splunk to use ldap authentication. The configuration is pretty straight forward, I can see the AD group and the AD group member from splunk and map it with splunk role but somehow it failed to authentication.
The only errors I can find in the splunkd log are as below. Question 1: If it is working fine, I need to put only username "_splunk" without the domain prefix and postfix right?
09-013-2016 17:09:52.454 +0800 ERROR UserManagerPro - LDAP Login failed, could not find a valid user="_splunk" on any configured servers
09-013-2016 17:13:18.432 +0800 ERROR UserManagerPro - LDAP Login failed, could not find a valid user="test_splunk" on any configured servers
09-013-2016 17:15:11.330 +0800 ERROR UserManagerPro - LDAP Login failed, could not find a valid user="email@example.com" on any configured servers
authSettings = testldap
authType = LDAP
SSLEnabled = 1
anonymous_referrals = 1
bindDN = CN=_splunk,OU=Admin Users,OU=TEST - Global Admins,OU=Organization Unit,DC=test,DC=com
bindDNpassword = $1$56ExJUjhTyFZzzzxZC
charset = utf8
emailAttribute = mail
groupBaseDN = CN=TEST-SPL-ADMIN,OU=Server Group,OU=TEST - Global Admins,OU=Organization Unit,DC=test,DC=com
groupMappingAttribute = dn
groupMemberAttribute = member
groupNameAttribute = cn
host = dc.test.com
nestedGroups = 1
network_timeout = 20
port = 636
realNameAttribute = cn
sizelimit = 3000
timelimit = 15
userBaseDN = CN=TEST-SPL-ADMIN,OU=Server Group,OU=TEST - Global Admins,OU=Organization Unit,DC=test,DC=com
userNameAttribute = samaccountname
admin = TEST-SPL-ADMIN
-I have tried to disable SSL and use port 389 for binding but no help.
have tried to use domain admin account "_testadmin" as bindDN but not help.
have used a newly created domain account "_splunk" with read only permission to the AD group "TEST-SPL-ADMIN", add this account to windows authorization access group in the specify domain controller but no help.
-In the quick testing, I have domain admin account "_testadmin" added as group member of the AD group "TEST-SPL-ADMIN" which I would like to use for authentication. This same with another account I used to test binding "_splunk", it is a member of the AD group "TEST-SPL-ADMIN" aslo. My bindDN I tried are "CN=_splunk,OU=Admin Users,OU=TEST - Global Admins,OU=Organization Unit,DC=test,DC=com" and "CN=_testadmin,OU=Admin Users,OU=TEST - Global Admins,OU=Organization Unit,DC=test,DC=com"
Any solution or hint to troubleshoot it will be much appreciated. Thanks in advance.
... View more
I'm using Splunk DB Connect in a Splunk Forwarder to collect data from a MySQL database. My Splunk version is 6.3.
The indexed data date refers to current date instead of the date from the input_timestamp_column_name (bbp_date column in the db in this case), although it did follow the time. I can see that Splunk has managed to create an additional field with the correct date and time as the bbp_date, however, the _time just does not follow. For example:
"2005-12-05 16:03:52" BBP_SWASTA="BBP_SWASTA", bbp_trx_id="3784", bbp_date="2005-12-05 16:03:52", companyno="191484", swastano="J301", bbp_no="J301/S200512/000039", season="200101", gross_paddy_weight="5920",
Current Setting in inputs.conf:
description = purchase data
index = bbp_purchase
input_timestamp_column_name = bbp_date
interval = 60
max_rows = 10000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT * FROM splunk_bbp_swasta
source = xxx.xxx.xxx.xxx:3306
sourcetype = purchase_swasta
tail_follow_only = 1
tail_rising_column_checkpoint_value = 2005860
tail_rising_column_name = bbp_trx_id
ui_query_catalog = bbpdev
ui_query_mode = advanced
ui_query_schema = NULL
ui_query_table = NULL
*splunk_bbp_swasta is a virtual table created by db admin.
*the data type of bbp_date column is datetime
*the raw data of bbp_date is in format "2005-12-05 16:03:52.0"
I have tried the solutions below but none of them work:
1) Solution mentioned in https://answers.splunk.com/answers/71485/splunk-db-connect-timestamp-not-working.html
2) Put in the input_timestamp_format (nothing will be index if put in this setting)
3) Convert bbp_date column data type to character myself or ask db admin to change the data type during this virtual table creation.
4) Convert to bbp_date column to epoch time during sql elect query.
5) Try put bbp_date as the first or end in the sql select query.
Appreciate that someone can help to solve this, it is important for me. Thanks a lot.
... View more