|
Concatenate (Sessions)
Load
RecNo() as SessionsEntry,
'$(fname)' as SessionsFileName, // For incremental loading of Sessions
'$(fname)' & RecNo() as _SessionId, // For incremental loading of sessionheatmap
makedate(year([Session Start]),month([Session Start]),day([Session Start])) as SessionDate,
num(makedate(year([Session Start]),month([Session Start]),day([Session Start]))) as SessionDateNum,
1 as Session_Start_Count,
hour([Session Start]) as SessionHour,
[Cal Type] as Cal_Type,
if(len([Authenticated user])>0,lower([Authenticated user]),'Anonymous') as Authenticated_User,
Selections as SessionSelections,
Calls as SessionCalls,
[Bytes Sent] as Bytes_Sent,
[Bytes Received] as Bytes_Received,
[CPU spent (s)] as CPU_spent__s_,
[Session Duration] as Session_Duration,
$(SessionStart_field) as Session_Start,
$(Timestamp_field) as SessionTimestamp,
[Exe Version] as SessionExeVersion,
[Exe Type] as SessionExeType,
lower(replace(Document,'/','\')) as Sessions_FileName,
LOWER(SubField(replace(Document,'/','\'),'\',-1)) as DocName,
SubField(replace(Document,'/','\'),'\',-1) as DocName_Sessions,
$(DocumentTimestamp_field) as Document_Timestamp,
[Exit Reason] as Exit_Reason,
([Exit Reason]='Server shutting down')*-1 as [Session Lost to Server Restart],
[Client machine identification] as Client_machine_identification,
[Serial number] as Serial_number,
[Client Type] as Client_Type,
[Secure Protocol] as Secure_Protocol,
[Tunnel Protocol] as Tunnel_Protocol,
[Server Port] as Server_Port,
[Client Address] as Client_Address,
[Client Port] as Client_Port,
'$(tmp_node)'&'|' & round(num($(Timestamp_field)),0.001) as Node_TS
FROM '$(fname)' (txt, utf8, embedded labels, delimiter is '\t', msq)
WHERE NOT EXISTS(_SessionId,'$(fname)' & RecNo());
| 
