plainblack.com
Username Password
search
Bookmark and Share

    

page visits from passiveLog

User christian_g
Date 1/20/2011 4:05 am
Views 1615
Rating -2    Rate [
|
]
Previous · Next
User Message
christian_g

It did took me some time to get all the page visits out of the passiveLog table. My suggested SQL-query is:

SELECT DISTINCT from_unixtime(passiveLog.timeStamp) AS Time,
userLoginLog.ipAddress,
users.username AS User,
assetData.title AS Page
FROM passiveLog, assetData, userLoginLog, users
WHERE passiveLog.assetId = assetData.assetId
AND assetData.revisionDate = (
    SELECT MAX(maxData.revisionDate)       
    FROM assetData as maxData       
    WHERE assetData.assetId=maxData.assetId
)
AND passiveLog.sessionId = userLoginLog.sessionId
AND users.userId = userLoginLog.userId
AND userLoginLog.timeStamp = (
    SELECT MAX(maxStamp.timeStamp)       
    FROM userLoginLog as maxStamp       
    WHERE userLoginLog.sessionId=maxStamp.sessionId
)
AND passiveLog.userId = users.UserId
AND users.username <> 'Visitor'
ORDER BY passiveLog.timeStamp DESC

--- (Edited on 1/20/2011 4:05 am [GMT-0600] by christian_g) ---



Back to Top
Rate [
|
]
 
 
christian_g

My previous query still was not correct. Here a better version:

SELECT DISTINCT from_unixtime(passiveLog.timeStamp) AS Time,
myloginlog.ipAddress,
users.username AS User,
assetData.title AS Page
FROM passiveLog, assetData, users,
  (SELECT userId, sessionId, ipAddress, MAX( timeStamp )
   FROM userLoginLog
   GROUP BY userId, sessionId) myloginlog
WHERE passiveLog.assetId = assetData.assetId
AND assetData.revisionDate = (
    SELECT MAX(maxData.revisionDate)       
    FROM assetData as maxData       
    WHERE assetData.assetId=maxData.assetId
)
AND passiveLog.sessionId = myloginlog.sessionId
AND users.userId = myloginlog.userId
AND passiveLog.userId = users.UserId
ORDER BY passiveLog.timeStamp DESC

--- (Edited on 1/20/2011 7:57 am [GMT-0600] by christian_g) ---



Back to Top
Rate [
|
]
 
 
    



© 2019 Plain Black Corporation | All Rights Reserved