How to query the same result as in the Scheduled updates activity in the actionlogDB?

[Mark123]

Nice day,every one

    I'm using Spotfire 14.0 and would like to create a view in SQL Server based on the data in the actionlogDB.

    The view is consistent with the content in the Scheduled updates activity on the web page.

    It has been clearly stated that USER_NAME should be limited to 'scheduledupdates@SPOTFIRESYSTEM', and LOG_ACTION should be limited to 'load_comtent' as Start time.

    Could you provide an SQL statement?

    

 

[David Boot-Olazabal]

Hi Mark,

Do you mean a normal query like this:

select * from [dbo].[ACTIONLOG]
where USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and LOG_ACTION = 'load_content'

Or do you want to do something else with the data?
Looking at your question, I tend to lean to the 'something else' part, since you mentioned the value of 'load_content' from the Log_Action column to be the starting point. Can you explain a bit more what you are looking for?

Kind regards,

David

[Mark123]

1 hour ago, David Boot-Olazabal said:

Hi Mark,

Do you mean a normal query like this:

select * from [dbo].[ACTIONLOG]
where USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and LOG_ACTION = 'load_content'

Or do you want to do something else with the data?
Looking at your question, I tend to lean to the 'something else' part, since you mentioned the value of 'load_content' from the Log_Action column to be the starting point. Can you explain a bit more what you are looking for?

Kind regards,

David

I'd like to create a view in the database which content is the same as Scheduled updates activity(the second figure).

[David Boot-Olazabal]

Hi Mark,

Right. So this is a combination of data from the Actionlog and from the Spotfire server database.
More in particular, you will need the Routing_Rules table for the Rule name & Analysis file path columns as well as the Resource_Pools table for the Resource pool column. The web instance column can be derived from the Nodes & Nodes_Services tables .

Then, to fill in the other columns, you will either need to use a 'self-join' statement or a pivot statement on the Actionlog table. This one can serve as an example for the self-join option:

select al1.log_category, al1.success, al1.id2, al1.original_time as start_time, al2.original_time as end_time, datediff("ss", al1.original_time,al2.original_time) as "load time"
from actionlog al1, actionlog al2
where 
al1.original_time >= CONVERT (date, GETDATE()) and
al2.original_time >= CONVERT (date, GETDATE()) and
al1.USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and al2.USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'

and al1.ARG3 = 'SchedulerUserSession'
and al2.ARG3 = 'SchedulerUserSession'
and al1.LOG_ACTION = 'load_start'
and al2.LOG_ACTION = 'load'

and al1.ID1 = al2.ID1
and al1.Session_Id = al2.session_id
ORDER BY ID2

It will show off in the table as:

For the pivot example, you can use this statement:

SELECT distinct * FROM
( SELECT distinct LOG_CATEGORY, LOG_ACTION, SUCCESS, ID2, ORIGINAL_TIME
     FROM ACTIONLOG
	 WHERE  USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and ARG3 = 'SchedulerUserSession'
and LOG_ACTION <> 'close'
 ) AS P
 PIVOT
 (
   max(ORIGINAL_TIME) FOR LOG_ACTION IN ([load_start], [load])
 ) AS pv1

Which would look like this:

 

Once you have your Actionlog pivot part working, you can start joining the other tables to it.

Kind regards,

David

[Mark123]

16 hours ago, David Boot-Olazabal said:

Hi Mark,

Right. So this is a combination of data from the Actionlog and from the Spotfire server database.
More in particular, you will need the Routing_Rules table for the Rule name & Analysis file path columns as well as the Resource_Pools table for the Resource pool column. The web instance column can be derived from the Nodes & Nodes_Services tables .

Then, to fill in the other columns, you will either need to use a 'self-join' statement or a pivot statement on the Actionlog table. This one can serve as an example for the self-join option:

select al1.log_category, al1.success, al1.id2, al1.original_time as start_time, al2.original_time as end_time, datediff("ss", al1.original_time,al2.original_time) as "load time"
from actionlog al1, actionlog al2
where 
al1.original_time >= CONVERT (date, GETDATE()) and
al2.original_time >= CONVERT (date, GETDATE()) and
al1.USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and al2.USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'

and al1.ARG3 = 'SchedulerUserSession'
and al2.ARG3 = 'SchedulerUserSession'
and al1.LOG_ACTION = 'load_start'
and al2.LOG_ACTION = 'load'

and al1.ID1 = al2.ID1
and al1.Session_Id = al2.session_id
ORDER BY ID2

It will show off in the table as:

For the pivot example, you can use this statement:

SELECT distinct * FROM
( SELECT distinct LOG_CATEGORY, LOG_ACTION, SUCCESS, ID2, ORIGINAL_TIME
     FROM ACTIONLOG
	 WHERE  USER_NAME = 'scheduledupdates@SPOTFIRESYSTEM'
and ARG3 = 'SchedulerUserSession'
and LOG_ACTION <> 'close'
 ) AS P
 PIVOT
 (
   max(ORIGINAL_TIME) FOR LOG_ACTION IN ([load_start], [load])
 ) AS pv1

Which would look like this:

 

Once you have your Actionlog pivot part working, you can start joining the other tables to it.

Kind regards,

David

Thank you for your reply.But there's something defferent.

1.There's NO  'SchedulerUserSession' in ARG3.

2.There's NO 'load_start' or 'load' in LOG_ACTION.Perhaps using 'load_content' instead of 'load_start'.But I don't know what to use instead of 'load'.

Which version of Spotfire you are using?I'm using 14.0.1.

[David Boot-Olazabal]

Hi Mark,

I have Spotfire 14.4, but I can check this out on Spotfire 14.0.1. Just need to spin up my 14.0.1 instance and add a couple of scheduled updates to see what kind of information is being captured.
And hopefully, I can give you more information on what I see in terms of log_action values and user_name values.

I'll come back on this as soon as possible.

Kind regards,

David

[Mark123]

Hello David,

Waiting for your good news,thank you.

[David Boot-Olazabal]

Hi Mark,

I have finalized my testing on 14.0.1 LTS.
I have created a couple of new rules, which have been executed successfully:

This also shows in the Activity overview:

And then, when I checked the database, with the query I provided earlier, I see I can use it without making any changes to it

If you are not seeing the proper information, you may need to check the Spotfire Configuration. It could very well be that you are not capturing all the action log categories.
See my setup for reference:

 

Kind regards,

David

[Mark123]

Hi David

Something confusing.I checked the relevant settings and they are the same as yours.

[David Boot-Olazabal]

Hi Mark,

That is weird. When all categories are set to be captured by the actionlog and you don't see them, this may call for more investigation.
I would advise you to create a support ticket for this to get this resolved (you can do so via https://support.tibco.com).

Once you have solved the issue with the actionlog log_action values, you should be able to move forward with all the information in this topic.

Kind regards,

David

[Mark123]

In the future, I'll consider setting up another Spotfire for testing in the testing environment.
Thank you for your help.