读取日志并分配注销时间 [英] Read Log and assign Logout time

问题描述

大家好

我又回来了另一个问题。我有一个系统日志（csv文件），它只记录用户名，登录时间和位置名称。我需要读取此文件并为每个用户分配注销时间。上一个用户的注销时间将是下一个用户在相同位置的登录时间。

希望我已经解释了我想要实现的目标。

请帮忙。我附上了一份样本Excel文件，其中包含预期结果供您考虑。


问候

附加文件

LogSample.xlsx （8.5 KB，151 views）

解决方案

rajeevs，


我确实看到了一些问题使用您的数据，但我认为如果您解决这些问题，下面的解决方案应该可以正常工作。


登录数据看起来不像真正的日期，因此当我们尝试比较这些值时，这可能会导致一些问题。如果它们是真实的日期/时间，比较应该可以正常工作。


所以，我将如何解决这个问题就是让用户登录。你设置一个带日期/时间的变量数据类型并存储用户登录时间的值。然后将该值保存到该用户的登录字段。


其次，因为您知道该人的位置，您查询该位置的其他用户的所有记录，但是您使用聚合查询，在登录字段中查找最大值（这也不等于刚刚登录的当前用户）。


希望这个表有一个索引（它绝对应该这样），因为你不能只更新和聚合查询。然后使用刚刚保存的登录时间更新找到的记录。下面的骨架：

展开 | 选择 | Wrap | 行号

嗨twinnyfo

感谢您的回复。

时间可以转换为日期/时间，因为csv文件名将是日志日期。所以我可以将登录时间更改为日期/时间字段。我怀疑哪个字段应该编入索引。我是否需要为每条记录设置一个序号，并将其用作索引或位置名称作为索引？

您已经提到要查找登录的最大值，但我需要的是下一个相同位置的登录时间，但具有不同的用户名，这将是前一个用户的注销时间。请指教

rajeevs：

+ CSV文件也会受到赞赏，以便我们可以看到原始数据。一旦我们有了...


如果您只是将这些信息作为[代码]块剪切并粘贴在帖子中，那将是最好的


我理解其中是否包含个人详细信息;但是，应该能够在记事本或excel等中打开文件。并将名称更改为JohA Doe，JohB Doe等...并且SSN或TaxID可以更改为000-00-0001，（... ）-0002等...


+此时你有什么代码吗？请你发帖吗？


如果你还没开始，请花一点时间写下你的工作流程。

出于某种原因，写下工作流程的物理行为有助于人类大脑解决完成任务所需的逻辑。

接下来，将非正式工作流程转换为编程图。 ...我更喜欢 NS -Charts（PDF文件）;然而，任何方法都比甩它更有帮助恕我直言。


+我同意TwinnyFo - 你真的应该有一个索引/主键字段（我讨厌复合主键 - 我的意见虽然:)）我用CSV文件执行此操作，我从实验室中的仪器导入数据库。

[PK] [LogInTime] [LogOutTime] [FK_User] [etc ....]


关于如何保存数据指针，这取决于原始数据流。我有一种情况，仪器样品数据导出并不总是以相同的顺序写出数据（相同的标题，只是水分可能是给定样品的第一个记录或第三个记录）这是信息的类型您的原始数据文件将提供。


对不起，这里没有更多细节...今天非常有限的时间:)


-z

Hi All
I am back with another problem. I have a system log (csv file) which logs the user name, login time and the position name only.I need to read through this file and assign the logout time for each user. The logout time for the previous user will be the next users login time in the same position.
Hope I have explained what I am trying to achieve.
Please help. I have attached a sample Excel file with the expected results for your kind consideration

Regards

Attached Files

LogSample.xlsx (8.5 KB, 151 views)

解决方案

rajeevs,

I do see some issues with your Data, but I think if you resolve those issues, the solution below should work fine.

The "Login" data does not look like a true date, so this might cause some problems when we try to compare these values. If they were true Date/Time, comparison should work fine.

So, how I would approach this problem would be to have the user log in. You set a variable with date/time data type and store the value of the time the user logged in. This value is then saved to that user''s Login field.

Second, since you know the position of the person, you query all your records for other users at that position, but you use an aggregate query, looking for the Max value in the Login field (which ALSO does not equal the current user who just logged in).

Hopefully this table has an index (which it most definitely should), because you can''t just update and aggregate query. Then you update the record you found with the login time you just saved. Skeleton below:

Expand|Select|Wrap|Line Numbers

Hi twinnyfo
Thank you for the reply.
The time can be converted to date/time because the csv file name will be a date which is the log date. So I can change the login time as date/time field. I doubt which field should be indexed. Do I need to have a sequential number for each record and use that as the index or the position name as index?
You have mentioned that look for the max value for login but what I need is the next login time for the same position but with a different user name and that will be the logout time of the previous user. Please advise

rajeevs:
+ The CSV file would be appreciated too so that we can see the raw data. Once we have that...

Would be best if you''d just cut and paste this information within a post as a [code] block

I understand if there are personal details contained therein; however, one should be able to open the file up in notepad or excel etc.. and change names to JohA Doe, JohB Doe, etc... and SSN or TaxID can be altered to 000-00-0001, (...)-0002, etc...

+ Do you have any code at this point? Would you post it please?

If you haven''t start yet, then please take a moment to write down your work flow.
For some reason, the physical act of writing down the work flow helps the human brain resolve the logic needed to accomplish a task.
Next, convert that informal work flow to a programing diagram.... I prefer NS-Charts (PDF File); however, any method is more helpful than winging it IMHO.

+ I Agree with TwinnyFo - you really should have an index/Primary Key field (I hate composite primary keys - my opinion though :) ) I do this with a CSV file I import from my instruments in the lab to the database.
[PK][LogInTime][LogOutTime][FK_User][etc....]

As to how to hold the data pointer, that depends on the raw data stream. I have a case where the instrument sample data export doesn''t always write the data out in the same order (same header, just the moisture might be the first record or the third record for a given sample) this is the type of information your raw data file will provide.

sorry, not much more detail here... very limited time today :)

-z

这篇关于读取日志并分配注销时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！