Rising Column

[rahul2gupta]

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

[thambisetty]

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.

[gcusello]

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

[rahul2gupta]

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

[gcusello]

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

[rahul2gupta]

Hi @gcusello ,

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

Regards,

Rahul

[gcusello]

Hi @rahul2gupta,

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

Ciao.

Giuseppe

[rahul2gupta]

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

[gcusello]

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

[rahul2gupta]

Hi @gcusello ,

The LOGON_TIME format is as below:

 Name                                                      Type

LOGON_TIME                                         DATE

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

Regards,

Rahul

[gcusello]

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

[thambisetty]

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.

[rahul2gupta]

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