1
answer

Hi how can I modify my query to remember the last recordid? Right now im using the timestamp and its working but I want to use the recordid as the timestamp is causing another issue.

<Input in>
Module im_odbc
IdType integer
ConnectionString DSN=SMS SQL;database=CM_IMG;encrypt=true;trustServerCertificate=true;
SQL SELECT RecordID as id,'Microsoft_SCEP' as Name, MAL.DetectionTime as SCEPMalDetectTime, SY.Name0 as TargetHost,SY.Resource_Domain_OR_Workgr0 as NTdomain, U.UserName,MAL.Process,MAL.Path,MAL.ThreatID, MAL.ThreatName,MAL.SeverityID,TR.Category,CA.DefaultAction as CleanAction, PA.DefaultAction as PendingAction,MAL.ExecutionStatus,MAL.ActionSuccess,MAL.ErrorCode, CASE WHEN (LEN(MAL.Path) - LEN(REPLACE(MAL.Path, ';file:', ''))) = 0 THEN 1 ELSE (LEN(MAL.Path) - LEN(REPLACE(MAL.Path, ';file:', '')))/6 END AS MaliciousFileCt FROM CM_IMG.SCCM_Ext.vex_EP_Malware MAL LEFT JOIN CM_IMG.dbo.v_R_System SY on SY.ResourceID = MAL.ResourceID LEFT JOIN CM_IMG.dbo.v_Users U on U.UserID = MAL.UserID LEFT JOIN CM_IMG.dbo.EP_ThreatCategories TR on TR.CategoryID = MAL.CategoryID LEFT JOIN CM_IMG.dbo.EP_ThreatDefaultActions CA on CA.DefaultActionID = MAL.CleaningAction LEFT JOIN CM_IMG.dbo.EP_ThreatDefaultActions PA on PA.DefaultActionID = MAL.PendingActions where MAL.DetectionTime >= dateadd(minute, -5, GetDate())
SavePos TRUE
</Input>

AskedJanuary 10, 2019 - 6:26pm

Answer (1)

You might want to set IdType depending on the data type of the id column.

AnsweredJanuary 10, 2019 - 6:38pm

Comments (2)

  • alain112's picture

    That doesn't really answer my question :s Thank you tho

    January 10, 2019 - 6:53pm
  • alain112's picture

    Would this work?

    <Input in>
    Module im_odbc
    IdType integer
    ConnectionString DSN=SMS SQL;database=CM_IMG;encrypt=true;trustServerCertificate=true;
    SQL SELECT RecordID as id,'Microsoft_SCEP' as Name, MAL.DetectionTime as SCEPMalDetectTime, SY.Name0 as TargetHost,SY.Resource_Domain_OR_Workgr0 as NTdomain, U.UserName,MAL.Process,MAL.Path,MAL.ThreatID, MAL.ThreatName,MAL.SeverityID,TR.Category,CA.DefaultAction as CleanAction, PA.DefaultAction as PendingAction,MAL.ExecutionStatus,MAL.ActionSuccess,MAL.ErrorCode, CASE WHEN (LEN(MAL.Path) - LEN(REPLACE(MAL.Path, ';file:', ''))) = 0 THEN 1 ELSE (LEN(MAL.Path) - LEN(REPLACE(MAL.Path, ';file:', '')))/6 END AS MaliciousFileCt FROM CM_IMG.SCCM_Ext.vex_EP_Malware MAL LEFT JOIN CM_IMG.dbo.v_R_System SY on SY.ResourceID = MAL.ResourceID LEFT JOIN CM_IMG.dbo.v_Users U on U.UserID = MAL.UserID LEFT JOIN CM_IMG.dbo.EP_ThreatCategories TR on TR.CategoryID = MAL.CategoryID LEFT JOIN CM_IMG.dbo.EP_ThreatDefaultActions CA on CA.DefaultActionID = MAL.CleaningAction LEFT JOIN CM_IMG.dbo.EP_ThreatDefaultActions PA on PA.DefaultActionID = MAL.PendingActions where RecordID > ?
    Exec delete($id)
    ReadFromLast TRUE
    SavePos TRUE
    </Input>

    January 10, 2019 - 7:04pm