delobelf@gmail.com
6/30/2017 - 12:06 PM

RG SQL examples (SQL Server)

RG SQL examples (SQL Server)


SELECT count(*) FROM orm_Plugin
SELECT * FROM orm_Plugin

SELECT count(*) FROM orm_PluginAgentType
SELECT * FROM orm_PluginAgentType

SELECT count(*) FROM orm_PluginUse
SELECT * FROM orm_PluginUse

SELECT count(*) FROM orm_AlertUse
SELECT * FROM orm_AlertUse

BEGIN TRAN
UPDATE orm_AlertUse
    SET Criticality=2, Threshold=1, Duration=30
WHERE AlertUseId=9
-- COMMIT

-----------------------------------------------------------------

SELECT TOP 10 * FROM orm_Agent where Name like '%DELOB%'
SELECT TOP 10 * FROM orm_Account where Name like '%fd%'


SELECT count(*) FROM orm_Account


-----------------------------------------------------------------
--  billing
--------------

-- orm_Account = compte
-- ds la requete : ,  a = ArticleTarif
-- a = Agent
-- c = compte
-- t = CompteTarif
-- at = ArticleTarif
SELECT TOP 10 * FROM Agent
SELECT TOP 10 * FROM Compte
SELECT TOP 100 * FROM orm_AccountPrice where PriceId NOT like 'W' and PriceId NOT like 'S' and PriceId NOT like 'D'
SELECT TOP 10 * FROM CompteTarif
SELECT TOP 10 * FROM ArticleTarif

-- type de compte : R = root, D = Dealer, C = Client
-- billingStartDate : date à partir de laquelle les agents du compte sont payants
-- cross apply : tous les agents de tous les noeuds de ce compte


SELECT TOP 10 * FROM ArticleTarif

SELECT * FROM WorkerTask


-----------------------------------------------------------------
--  OQ
--------------

SELECT COUNT (distinct OrderQueueId) FROM OrderQueue

SELECT TOP 10 * FROM OrderQueue where Name='UpdateReactEngineCachedRules' ORDER BY OrderQueueId DESC

SELECT TOP 10 * FROM OrderQueue where Name='UpdateReactEngineCachedRules' AND DoneAt IS NULL ORDER BY OrderQueueId DESC

SELECT * FROM OrderQueue WHERE OrderQueueId=105162

BEGIN TRAN
UPDATE OrderQueue SET DoneAt=NULL  WHERE OrderQueueId=105248
UPDATE OrderQueue SET Data=NULL  WHERE OrderQueueId=105213
--ROLLBACK
--COMMIT

BEGIN TRAN
DELETE OrderQueue WHERE Name='UpdateReactEngineCachedRules'
--ROLLBACK
--COMMIT


BEGIN TRAN
DELETE OrderQueue WHERE OrderQueueId=105246
--ROLLBACK
--COMMIT

-----------------------------------------------------------------
-- Worker Task
SELECT * FROM orm_WorkerTask ORDER BY TaskName ASC

BEGIN TRAN
UPDATE orm_WorkerTask SET Parameters = '--env=cli --no-debug --rate 61000 --burst 500 --names=ModifiedAt --names=MoveAgentTickets --names=MoveNodeTickets --names=AlertsConfiguration --names=PluginsConfiguration --names=DispatchNotificationMessage --names=BackupChangeStatus --names=UpdateReactEngineCachedRules --exclude' WHERE WorkerTaskId = 31
--ROLLBACK--COMMIT



-----------------------------------------------------------------
-- AUTHS & GROUPS
--------------

SELECT TOP 10 * FROM orm_GroupRight
SELECT TOP 10 * FROM orm_GroupRight WHERE RightId like '%TRAY%'

SELECT TOP 10 * FROM orm_Group

SELECT TOP 10 * FROM orm_Right
SELECT TOP 10 * FROM orm_Right WHERE RightId like '%TRAY%'

SELECT TOP 10 * FROM orm_OrderQueue where Name='DispatchNotificationMessage' ORDER BY OrderQueueId DESC


BEGIN TRAN
INSERT INTO orm_GroupRight VALUES (10, 'REACTENG')
--ROLLBACK
--COMMIT


BEGIN TRAN
INSERT INTO orm_Right VALUES ('REACTENG', 'CRUD  des règles du React Engine', 0)
INSERT INTO orm_GroupRight VALUES (10, 'REACTENG')
--ROLLBACK
--COMMIT

-----------------------------------------------------------------



-----------------------------------------------------------------
-- Beny13's house
--------------

SELECT * FROM orm_User WHERE UserId = 'test2@lol.com'
SELECT TOP 10 * FROM orm_Node WHERE Name = 'Fred'

SELECT * FROM WorkerTask

SELECT TOP 100 * FROM orm_Audit ORDER BY AuditId DESC

SELECT * FROM orm_GlobalParameter
SELECT TimeZone FROM orm_NodeReport
-----------------------------------------------------------------

-----------------------------------------------------------------
-- Adraspam's house
--------------

BEGIN TRAN
UPDATE WorkerTask SET Parameters='--env=cli --no-debug --rate 61000 --burst 500 --names=ModifiedAt --names=MoveAgentTickets --names=MoveNodeTickets --names=AlertsConfiguration --names=PluginsConfiguration --names=v --exclude' WHERE WorkerTaskId=31
--ROLLBACK
--COMMIT

----------------------------------------------------------------