Query to get user scanning details

[Harshal_Shravgi]

In OnBase Thick client we can generate a report User Summary by Date to get below details -

Login User Name: MANAGER

  Totals for user - Batches scanned:         20   Batches indexed:      18 

  Totals for user - Documents created:        70   Pages: 131 

  Totals for user - Batches Image Quality Reviewed:   0

  Totals for user - Scan more documents:   0   Scan more pages: 0 

  Totals for user - Batches separated:        0   Batches skipped separation: 0 

  Totals for user - Documents indexed:        70   Pages indexed:       126 

  Totals for user - Documents re-indexed:       0   Pages re-indexed:      0 

  Totals for user - Batches QA Reviewed:   0

  Totals for user - Document Types used: 

    App. Files (70)

  Average batch index time for this user  (HH:MM:SS): 0:02:12 

  Average batch re-index time for this user (HH:MM:SS): 0:00:00 

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

My users want some of the data from above report in tabular format. The required data is User Name, Batches Scanned, Batches Indexed, No. of Documents Scanned, Pages Scanned, No. of Documents Indexed, Pages Indexed.

This data I want for a given date range and scan queue.

I know I could get this data from scanning log so I have written below query:

select * from scanninglog where queuenum = 142 and usernum= 1600 

and to_date(logdate, 'DD/MM/YY') >= to_date('07/11/18', 'DD/MM/YY') And

 to_date(logdate, 'DD/MM/YY') <= to_date('07/11/18', 'DD/MM/YY')

 and actionnum in (200,202);

I am not getting the count of documents with this query. I could restructure the query if I get all required data. I will also join it with user account table to get User Name.

I need help to get User Name, Batches Scanned, Batches Indexed, No. of Documents Scanned, Pages Scanned, No. of Documents Indexed, Pages Indexed in one query.

[James_Perry]

I use this query to get the scanning information for a data set necessary for my scanning managers to review it in Reporting Dashboards. I hope this helps you with your query needs.

SELECT SI.queuenum SIQN

      ,SQ.queuenum SQQN

      ,RTRIM(SQ.queuename) ScanQueue

      ,SQ.diskgroupnum SQDGN

      ,USQ.usergroupnum USQUGN

      ,UG.usergroupnum UGUGN

      ,RTRIM(UG.usergroupname) UserGroup

      ,UXG.usergroupnum UXGUGN

      ,UXG.usernum USGUN

      ,UA.usernum UAUN

      ,RTRIM(UA.username) UserID

      ,RTRIM(UA.realname) UserName

      ,UA.disablelogin Disabled

      ,UA.lastlogon LastLogon

      ,UA.lastpwchange LastPwdChange

      ,RTRIM(UA.securityid) SID

  FROM hsi.scanqueuexit SI

  LEFT JOIN hsi.scanqueue SQ ON SI.queuenum = SQ.queuenum

  LEFT JOIN hsi.usergscanqueue USQ ON USQ.queuenum = SI.queuenum

  LEFT JOIN hsi.usergroup UG ON USQ.usergroupnum = UG.usergroupnum

  LEFT JOIN hsi.userxusergroup UXG ON UG.usergroupnum = UXG.usergroupnum

  LEFT JOIN hsi.useraccount UA ON UXG.usernum = UA.usernum

  WHERE UA.username NOT IN ('MANAGER','ADMINISTRATOR')

  AND UG.usergroupname NOT LIKE 'MANAGER%'

  AND SQ.queuename NOT LIKE 'ZZ%'

  AND UG.usergroupname NOT LIKE 'Workstation%'

  ORDER BY SI.queuenum

[Harshal_Shravgi]

Hi Jim,

I tried above query but it is giving user's security related information. I want to get User Name, Batches Scanned, Batches Indexed, No. of Documents Scanned, Pages Scanned, No. of Documents Indexed, Pages Indexed in one query.

[Jonna_Pantelis]

I'm no SQL DBA but here's what I use and it works wonderfully

SELECT     

hsi.scanninglog.scanninglognum,

--hsi.scanninglog.usernum,

hsi.useraccount.username,

--hsi.scanninglog.registernum, 

hsi.registeredusers.registername, 

CONVERT(varchar,hsi.scanninglog.logdate, 0) AS [Scan Date],

hsi.scanninglog.messagetext, 

hsi.scanninglog.eventnum,

hsi.scanninglog.actionnum,

hsi.scanninglog.subactionnum, 

CASE

WHEN

(hsi.scanninglog.eventnum = '1' AND hsi.scanninglog.actionnum = '1' AND hsi.scanninglog.subactionnum = '0')

THEN 'To Awaiting Index'

WHEN

(hsi.scanninglog.eventnum = '1' AND hsi.scanninglog.actionnum = '6' AND hsi.scanninglog.subactionnum = '0')

THEN 'To Awaiting Commit'

WHEN

(hsi.scanninglog.eventnum = '1' AND hsi.scanninglog.actionnum = '7' AND hsi.scanninglog.subactionnum = '0')

THEN 'To Begin Commit'

WHEN

(hsi.scanninglog.eventnum = '1' AND hsi.scanninglog.actionnum = '8' AND hsi.scanninglog.subactionnum = '0')

THEN 'To Finish Commit'

WHEN

(hsi.scanninglog.eventnum = '2' AND hsi.scanninglog.actionnum = '200' AND hsi.scanninglog.subactionnum = '2')

THEN 'Create Batch'

WHEN

(hsi.scanninglog.eventnum = '2' AND hsi.scanninglog.actionnum = '202' AND hsi.scanninglog.subactionnum = '0')

THEN 'Perform Index'

WHEN

(hsi.scanninglog.eventnum = '2' AND hsi.scanninglog.actionnum = '209' AND hsi.scanninglog.subactionnum = '0')

THEN 'Commit Batch Manual'

WHEN

(hsi.scanninglog.eventnum = '2' AND hsi.scanninglog.actionnum = '210' AND hsi.scanninglog.subactionnum = '0')

THEN 'Commit Batch Auto'

WHEN

(hsi.scanninglog.eventnum = '2' AND hsi.scanninglog.actionnum = '260' AND hsi.scanninglog.subactionnum = '0')

THEN 'Scan Operation Finished'

END AS [Action],

--hsi.scanninglog.queuenum, 

hsi.scanqueue.queuename, 

hsi.scanninglog.batchnum, 

hsi.archivedqueue.batchname, 

--hsi.archivedqueue.status,

CASE

WHEN hsi.archivedqueue.status = '8' THEN 'Committed'

END AS [Current Status],

hsi.scanninglog.extrainfo1 AS [Nbr of Docs], 

hsi.scanninglog.extrainfo2 AS [Nbr of Pages], 

--hsi.scanninglog.itemnum, 

hsi.scanninglog.severityflag, 

hsi.scanninglog.tracelvl

FROM

hsi.scanninglog INNER JOIN

hsi.useraccount ON hsi.scanninglog.usernum = hsi.useraccount.usernum INNER JOIN

hsi.registeredusers ON hsi.scanninglog.registernum = hsi.registeredusers.registernum INNER JOIN

hsi.scanqueue ON hsi.scanninglog.queuenum = hsi.scanqueue.queuenum INNER JOIN

hsi.archivedqueue ON hsi.scanninglog.batchnum = hsi.archivedqueue.batchnum

WHERE  (hsi.scanninglog.batchnum = '<ENTER BATCH NUMBER HERE OR REMOVE THIS IF YOU WANT ALL')

ORDER BY logdate asc