topic Re: Rising Column in Knowledge Management

Rising Column

rahul2gupta — Fri, 28 Aug 2020 10:20:38 GMT

Hi All,

I unable to select Rising column parameter from the following SQL Query.Can someone please help me with this.

select * from
(select to_char(count(1)) as "sessions" from v\\$session),
(select to_char(count(1)) as "processes" from v\\$process),
(select value as "max_sessions" from v\\$parameter where NAME='sessions'),
(select value as "max_processes" from v\\$parameter where NAME='processes'),
(select to_number(substr(output, 33),9999999.99) avg_ash
from table(dbms_workload_repository.ash_report_text( (select dbid from v\\$database), 1, sysdate - interval '5' minute, sysdate, 0))
where output like '%Average Active Sessions%')

Regards,

Rahul

Re: Rising Column

gcusello — Fri, 28 Aug 2020 10:44:54 GMT

Hi @rahul2gupta,

you're using select * this means that you take all the fields from those tables,

As I said in anothe answer, in the fields that you have as output of your query you have to identify one of them that's progressive (always growing), if you haven't you have to create it merging two fields (e.g. date and another field).

Which fields do you have?

Is there a progressive field?

If not, which fields you can use?

I don't know your data so I cannot help more.

As I said, this isn't a Splunk question, it's an SQL question!

Ciao.

Giuseppe

Re: Rising Column

thambisetty — Fri, 28 Aug 2020 11:15:11 GMT

To use rising column option, your sql query results should have a column which will be incremented by 1 or timestamp.

can you share header?

identify the key column which will be changing when new record is inserted into table.

Re: Rising Column

rahul2gupta — Fri, 28 Aug 2020 11:41:07 GMT

Can I use LOGON_TIME parameter as a Rising Column. It just tell us when the user has logged-in.

Re: Rising Column

rahul2gupta — Fri, 28 Aug 2020 11:42:27 GMT

Can I use LOGON_TIME parameter as a Rising Column(suggested by DBA Team)

It just tell us when the user has logged-in.

Regards,

Rahul

Re: Rising Column

thambisetty — Fri, 28 Aug 2020 12:27:44 GMT

Of course you can. First run your query as batch. Once results are returned, you can change to rising column and add query suggested in the db connect app.

set checkpoint, in your case choose logon_time and date also login_time.

Re: Rising Column

gcusello — Fri, 28 Aug 2020 12:33:27 GMT

Hi @rahul2gupta,

LOGON_TIME could be usable, but in this way you have the risk to take twice an event or loose it if you have two events with the same timestamp at the separation time.

Probably it isn't a great problem.

Ciao.

Giuseppe

Re: Rising Column

rahul2gupta — Thu, 03 Sep 2020 02:20:26 GMT

Hi @gcusello ,

When I am trying to save it,I'm encountering the following error.

Regards,

Rahul

Re: Rising Column

gcusello — Thu, 03 Sep 2020 07:07:30 GMT

Hi @rahul2gupta,

it's difficoult in this way, could you share the screen shots of the steps you did?

Ciao.

Giuseppe

Re: Rising Column

rahul2gupta — Thu, 03 Sep 2020 07:24:23 GMT

Hi @gcusello ,

Query:

index=main sourcetype=wms_oracle_sessions | bucket span=5m _time | stats count AS sessions by _time,warehouse,machine,program | sum(sessions) AS wsessions by _time,warehouse | timechart avg(wsessions) by warehouse

In which sourcetype=wms_oracle_sessions is missing.

I tried to create new sourcetype. Please find the screenshot below:

Name:wms_oracle_sessions

Input type -- tail

I specified sql query

Rising column --LOGON_TIME

I filled all the columns but when I am trying to save it we are encountering the error.

Please help.

Regards,

Rahul

Re: Rising Column

gcusello — Thu, 03 Sep 2020 07:34:06 GMT

Hi @rahul2gupta,

I think that you flagged "Specify SQL query" and inserted your query or inserted in the field the table you want.

Then you inserted LOGON_TIME in the Rising_Column field and the other Splunk fields.

Only one question: which is the format of LOGON_TIME ? it's in epochtime so you're sure that's always growing or in a different one?

if in a different one, try to transform it in the Oracle query.

Ciao.

Giuseppe

Re: Rising Column

rahul2gupta — Thu, 03 Sep 2020 10:38:42 GMT

Hi @gcusello ,

The LOGON_TIME format is as below:

Name Type

LOGON_TIME DATE

eg. 9/3/2020 7:18:50 PM

Regards,

Rahul

Re: Rising Column

gcusello — Thu, 03 Sep 2020 10:45:03 GMT

Hi @rahul2gupta,

you should add a field containing the LOGON_TIME in epochtime format so you're sure to have an always growing field to use in the Rising_Column.

Ciao.

Giuseppe