Alert filtering

Archived Topics
1

OK, now that my alarms are displayed in FPMI with recipe and batch with the following code:

SELECT L.item_name, L.state_name, L.active_time, (SELECT R.recipe_name FROM recipes R WHERE R.line_id=LEFT(L.item_name, 3) AND R.start_time < L.active_time ORDER BY R.start_time DESC LIMIT 1) as recipe_name, (SELECT B.batch_number FROM batches B WHERE B.line_id=LEFT(L.item_name, 3) AND B.start_time < L.active_time ORDER BY B.start_time DESC LIMIT 1) as batch_number FROM alarmlog L

I now would like to be able to FILTER by recipe and batch if possible… I have tried a couple things to no avail. I have an idea or two but not sure how to proceed. Any guidance would be appreciated. LOL :open_mouth:

2

(note - I took the liberty of splitting this topic - forum etiquette is one topic per thread)

You should just be able to append your filtering into a HAVING clause using the recipe_name and batch_number columns.

like:

... FROM alarmlog L HAVING batch_number=398

3

Thank you Carl. It works as advertised :wink:

4

Is there a way to use a STORED PROCEDURE on the alarmlog table to add batch_number and recipe instead of using two VIEWS like I am doing in this code:

SELECT L.item_name, L.state_name, L.active_time, (SELECT R.recipe_name FROM recipes R WHERE R.line_id=LEFT(L.item_name, 3) AND R.start_time < L.active_time ORDER BY R.start_time DESC LIMIT 1) as recipe_name, (SELECT B.batch_number FROM batches B WHERE B.line_id=LEFT(L.item_name, 3) AND B.start_time < L.active_time ORDER BY B.start_time DESC LIMIT 1) as batch_number FROM alarmlog L
The CODE works fine by the way, it’s just that the QUERY TIME is roughly 20 seconds and I would like to speed it up. I figured if all the data was in ONE table it might run faster. Am I wrong about this?

5

bump

6

For now, ensure that you have indexes on:
Recipes.start_time
Batches.start_time
Alarmlog.active_time

edit - I suppose also: Alarmlog.item_name.

7

I need some expert advice here.

I suspect that you should have each alarm referencing a unique recipe and batch (with corresponding foreign keys).

Recipes.line_id->Alarmlog.recipe
Batches.line_id->Alarmlog.batch

It doesn’t seem right/possible to add foreign keys the way you’re doing it. If you can, you would edit the Alarmlog table and add 2 foreign keys from Item_name to Recipes.line_id and Batches.line_id, respectively.

Recipes.line_id->Alarmlog.Item_name
Batches.line_id->Alarmlog.item_name

8

Ok nathan,
First: I’m not using Foreign Keys on this. I have 2 views (Recipes, Batches).
Second: Question for you,
I have 4 mixing lines. Each line has a Recipe and a Batch table:

ie l11_recipe
l11_batch…

Then there is the alarmlog table.

So, in total I have 9 tables. How would I use a foreign Key to get the CORRECT batch and recipe from the CORRECT line???

9

Oh - nevermind. That absolutely won’t help here…

Did you index those columns?

I’m not sure that you can do much better without redesigning your table layout.

10

I’m thinking about a stored procedure or something like that. I have experimented with a query that DOES select ONE record from alarmlog and gets the alarms recipe and batch and then inserts into a NEW table. That does work. And I was thinking about adding a column to alarmlog named “MOVED” so all the records that Havent been moved to the new table will all have ZERO’S in that column. Once moved and joined with recipe and batch, that row will have a 1 in the MOVED column. Just need to figure out HOW to run my new query automatically…

I’m sure It could be done with a Stored PRoc, but I believe I’ve read something about FSQL being able to accomplish the same thing.

Open to ALL suggestions.

11

Well you can easily run a stored procedure just by having a standard group in FactorySQL running a single action item that EXEC’d your proc.

12

As it stands now, I have a Global Script Timer:

alarms = fpmi.db.runQuery("SELECT L.AlarmLog_ndx AS ID, Date_Format(L.active_time, '%Y-%m-%d %H:%i:%s') AS active_time, Date_Format(L.clear_time, '%Y-%m-%d %H:%i:%s') AS clear_time, L.item_name, L.state_name, L.state_id, L.active_value, L.clear_value, L.severity, L.point_path, L.printed, (SELECT R.recipe FROM recipe R WHERE R.line_id=LEFT(L.item_name, 3) AND R.t_stamp < L.active_time ORDER BY R.t_stamp DESC LIMIT 1) as recipe, (SELECT B.batch_number FROM batches B WHERE B.line_id=LEFT(L.item_name, 3) AND B.t_stamp < L.active_time ORDER BY B.t_stamp DESC LIMIT 1) as batch FROM alarmlog L WHERE alarm_moved = '0' LIMIT 1")
ip = fpmi.net.getIpAddress()
##if len(alarms)>0:
if ip == "10.24.14.112" and len(alarms)>0:
   alarm = alarms[0]
   a = alarm["ID"]
   b = alarm["active_time"] 
   c = alarm["clear_time"]
   d = alarm["item_name"]
   e = alarm["state_name"]
   f = alarm["state_id"]
   g = alarm["active_value"]
   h = alarm["clear_value"]
   i = alarm["severity"]
   j = alarm["point_path"]
   k = alarm["printed"]
   l = alarm["recipe"]
   m = alarm["batch"]
   def insertQuery(insert="INSERT INTO alarm_matrix (ID, active_time, clear_time, item_name, state_name, state_id, active_value, clear_value, severity, point_path, printed, recipe, batch) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(a,b,c,d,e,f,g,h,i,j,k,l,m)):
      import fpmi
      fpmi.db.runUpdateQuery(insert)
   fpmi.system.invokeLater(insertQuery)
   fpmi.db.runPrepStmt("UPDATE alarmlog SET alarm_moved=1 WHERE alarmlog_ndx = ?", [a])

How would I use something similar in FSQL??