<jupyter_start><jupyter_text>Convert time into timestamp<jupyter_code># df.groupBy('Time').count().show()
# from pyspark.sql.functions import from_unixtime

# df = df.withColumn('timestamp', from_unixtime((df.Time.cast('bigint')/1000)).cast('timestamp'))

df.take(10)
# df.groupBy('timestamp').count().show()<jupyter_output><empty_output><jupyter_text>Create Window Functions (Fix Usernames)  Total events per user in an (last) hour window interval  Ref: https://stackoverflow.com/questions/45806194/pyspark-rolling-average-using-timeseries-data<jupyter_code>from pyspark.sql.window import Window
from pyspark.sql import functions as F
new_df = df.select("Time", "UserName", "EventID")
w = (Window.partitionBy('UserName').orderBy(F.col("Time").cast('long')).rangeBetween(-3600, Window.currentRow))
new_df = new_df.withColumn('total_events', F.count("Time").over(w))
new_df.orderBy("Time").take(10)<jupyter_output><empty_output><jupyter_text>Paritition By UserName and Event<jupyter_code>w = (Window.partitionBy('UserName', 'EventID').orderBy(F.col("Time").cast('long')).rangeBetween(-3600, Window.currentRow))
new_df = new_df.withColumn('total_per_event', F.count("Time").over(w))<jupyter_output><empty_output><jupyter_text>Total number of Event 4624 per user in an hour window interval  (Successful Logon)<jupyter_code>new_df.filter((new_df.UserName == 'Comp502166$') & (new_df.EventID == 4624)).show()<jupyter_output>+----+-----------+-------+------------+---------------+
|Time|   UserName|EventID|total_events|total_per_event|
+----+-----------+-------+------------+---------------+
| 109|Comp502166$|   4624|          10|              1|
| 123|Comp502166$|   4624|          12|              2|
|1009|Comp502166$|   4624|          25|              3|
|1562|Comp502166$|   4624|          33|              5|
|1562|Comp502166$|   4624|          33|              5|
|1563|Comp502166$|   4624|          36|              6|
|1564|Comp502166$|   4624|          38|              7|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|             16|
|1858|Comp502166$|   4624|          61|         [...]
{'fraction_non_alphanumeric': 0.18976897689768976, 'fraction_numerical': 0.12252475247524752, 'mean_word_length': 2.6521084337349397, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '455521', 'license': 'FTL', 'n_tokens_mistral': 1003, 'n_tokens_neox': 845, 'n_words': 137, 'path': 'notebooks/2.0-vb-initial-lanl-benign-data-exploration.ipynb', 'repo_name': 'vekshan/anomaly_detection_active_directory_logs', 'size': '2424'}