Getting Data In

DB Connect issue

vpantangi
Explorer

I have been given this query to get data into dbconnect, it works perfectly fine for batch, but i want to run and get results for rising column by using EVENT_TIME from January 1st 2020. what should be added to the below query, i tried adding AND EVENT_TIME > ?
ORDER BY EVENT_TIME DESC and it gives java.sql.SQLDataException: ORA-01861: literal does not match format string. If i change EVENT_TIME to EVENT_NAME it works but i want rising column value for event time . Please help 

SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
)

Labels (1)
Tags (1)
0 Karma

jakubzak
Explorer

I've been facing exactly the same issue reading data from 1 of 2 DB views (Ora 19). One was working OK, second one was having this issue. The only difference was a position of EVENT_TIMESTAMP column in view definition. One that was working OK had rising column (date) as first, second one had it as last column in view definition. I've redefined second view (moved EVENT_TIMESTAMP as first column) and error disappeared.

Full listing of columns tested at first without any success. 

Tags (1)
0 Karma

somesoni2
Revered Legend

Give this a try

 

SELECT * FROM (SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
sa.event_time AS rising_column
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
)) ev WHERE rising_column > ?
0 Karma

vpantangi
Explorer

Same error, i noticed something new when pasting the queries you sent, previously i was able to select values in rising column section, now it shows no results.

0 Karma

somesoni2
Revered Legend

Give this a try

 

SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
sa.event_time AS rising_column
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
) WHERE rising_column > ?

 

You had formatted EVENT_TIME value to string and that's the reason oracle was having hard time casting it to DATETIME /TIMESTAMP for rising column comparison. Create a new column to be used  for that.

0 Karma

vpantangi
Explorer

it gives this error "java.sql.SQLException: Missing IN or OUT parameter at index:: 1"

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...