Basic Application Metrics from TMPSYS_LOG

Just wanted to share a very basic query to capture application metrics -- specifically Service/Screen 'tallys' listed out by day for a given application:

SELECT LogDay, ServiceName, COUNT(CountInd)

FROM (
SELECT DISTINCT L.StartDtm,
  CAST( FLOOR( CAST(L.StartDtm AS FLOAT) ) AS DateTime) As LogDay,
  CASE WHEN S.ScreenID IS NULL THEN L.ServiceName ELSE S.ScreenName END AS ServiceName,
  CAST(1 As Integer) As CountInd

FROM tmpsys_Log L LEFT OUTER JOIN tmpcfg_Screen S ON L.ServiceName = S.ScreenID

WHERE L.AppID = '100-' AND L.UserID <> '' AND L.EntryBy IS NULL ) As tmp

GROUP BY LogDay, ServiceName
ORDER BY LogDay DESC

Another useful query is a count of unique user ids, by day, for a given application:

SELECT LogDay, COUNT(UserID)

FROM (
SELECT DISTINCT
  CAST( FLOOR( CAST(StartDtm AS FLOAT) ) AS DateTime) As LogDay,
  UserID

FROM tmpsys_Log

WHERE AppID = '1000' AND UserID <> '') As tmp

GROUP BY LogDay
ORDER BY LogDay DESC

Obviously, change the AppID to the corrseponding application ID you wish to query.  Both queries are pretty basic and suit our needs but feel free to modify/expand on them to suit yours!

Have more questions? Submit a request

1 Comments

  • 0
    Avatar
    gaylon_vorwaller

    Can also get from statistics tables (tmpmon_%) if statistics are turned on...

Please sign in to leave a comment.
Powered by Zendesk