Transaction sub process stats

[tyronetv]

I have an FTP log (using VSHELL from http://www.vandyke.com/) wherein each user session gets a unique ID but the user can (and often does) send multiple files.

I am trying to write a report that shows time, ID, login, IP, file, size and for the most part it works, unless the "trigger" file is the same byte count and then the report is a bit wonkey....

Essentially, the transfer line looks like:

Nov 17 00:02:27 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p created directory /DIR/. 

Nov 17 00:02:39 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000010692381.PDF for write 940646 bytes transferred. 

Nov 17 00:02:40 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000010692381.tkt for write 910 bytes transferred. 

Nov 17 00:02:52 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000020888169.PDF for write 1032185 bytes transferred. 

Nov 17 00:02:52 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000020888169.tkt for write 910 bytes transferred. 

Nov 17 00:03:04 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000020891502.PDF for write 1045536 bytes transferred. 

Nov 17 00:03:04 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/000020891502.tkt for write 910 bytes transferred. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/110005569971.PDF for write 933715 bytes transferred. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: kai101p opened /DIR/110005569971.tkt for write 910 bytes transferred. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] dbg ,2011830: [LOCAL DEBUG] RECV: CHANNEL_OPEN[session] 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] conn,2011830: Session channel open request accepted. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: Sftp subsystem initialized; remote version is 3. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: Sending VERSION packet to remote (3) 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: Sftp subsystem terminated. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] conn,2011830: Session channel has been closed (pid: none). 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: Sftp subsystem terminated. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] conn,2011830: Session channel has been closed (pid: none). 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] sftp,2011830: Sftp subsystem terminated. 

Nov 17 00:03:13 HOST vshelld[27640]: [ID 681337 local3.notice] conn,2011830: Session channel has been closed (pid: none). 

I wrote a rex to pull out the file and bytes (910 above) but when there are, multiple transfers and each file includes a 'trigger' file of (in the case of above) 910 bytes the report is inaccurate.

I do a transaction based upon the vshelld[27946], which includes all the steps from connection to disconnect and multiple transfer lines (as above) so I can pull the credentialed user's name as well as remote host, etc., and when I attempt to report on time, user, file, size it generates one line per the vshelld[27640] and then lists all the files names but only lists 910 one time regardless of how many trigger files.

I would think this is some sort of sub-transaction but I'm not sure how to do it and get a proper grouping...i.e.

_time, file & trigger (they have the same timestamp in the log), login, clientip, etc. ,etc.

Help?

I hope this is enough information to get the conversation started and hopefully get somewhere closer to the answer I need.

[Ayn]

I don't follow entirely if you want to report on the whole session or on individual files. Trying to combine those two can be a bit tricky. You haven't posted a search or field names you're using or stuff like that so it's hard to write something that you can take and just paste and go, but let's say you have the session ID extracted in the field "ID", the username in the login as "login", files as "file" and size as "size". You also mentioned IP but I don't see that anywhere in the log sample. One more thing I don't understand is what's special about these "trigger" files - it's just another file, no? 🙂

If you're fine with reporting individually on each item uploaded by a user, you could just do

... | table ID login file size

If you want to group all this in a session for some reason, you could do something like

... | stats list(login) as login, list(file) as file, list(size) as size by ID

From your requirements it doesn't seem like you need to use transaction at all.

[Ayn]

You could make the clientip available by doing

... | eventstats first(clientip) as clientip by PNUM | ...

...or, if you take the stats route that I wrote as your second option, you could do

... | stats first(clientip) as clientip, list(login) as login, list(file) as file, list(size) as size by ID

[tyronetv]

sourcetype=vshell PNUM=27640 |transaction PNUM | reverse |table _time PNUM action loginid clientip SFIL |search action=write OR action=read

[vshell]

EXTRACT-vshell_sar = (?i) (?P[^ ]+) opened (?P[^ ]+) for (?P\w+)\s(?P\d+) bytes transferred

EXTRACT-vshell_pnum = ^\w+\s+\d+\s\d+:\d+:\d+\s+\w+-\w+\s\w+\[(?P\d+)\]

EXTRACT-vshell_SNOD = Connection accepted from (?P\d+\.\d+\.\d+\.\d+):\d+

[tyronetv]

To get the remote host (clientip) I need to gather from the first line (based upon vshelld[27640]).

Nov 17 00:02:25 HOST vshelld[27640]: [ID 681337 local3.notice] conn,2011830: Connection accepted from 167.117.16.147:3098.

That's why I think I need to use a transaction.