topic Re: FTP file staistics in Splunk Search

FTP file staistics

srajanbabu — Mon, 28 Sep 2020 15:00:41 GMT

I am new to spluk, I have the below sample log and would like to arrive statistics on userwise how many files/Bytes retrived/FTPed. Please guide me to accomplish this task.

0004 rajan.#### :: 04/03/13 00:00:34 :: User rajan logged in
0004 rajan.000f :: 04/03/13 00:00:27 :: User logged off, Processing will begin
0004 rajan.000f :: 04/03/13 00:00:27 :: Transmission type set to Binary
0004 rajan.000f :: 04/03/13 00:00:27 :: Customer retrieved file xxxxxxxxx_G3INF0000000108647.CSV.PGP 62619 bytes transferred
0004 rajan.000f :: 04/03/13 00:00:27 :: Customer has successfully retrieved file xxxxxxxxx_G3INF0000000108647.CSV.PGP (123 records/62619 bytes)
0004 rajan.000f :: 04/03/13 00:00:27 :: FTPWATCH: (saf_file) SAF_INFO returned WINT1TBY,153F,123,62619
0004 rajan.000f :: 04/03/13 00:00:29 :: 125 Storing data set xxxxxxxxx.T000029.rajan.S000f
0004 rajan.000f :: 04/03/13 00:00:29 :: 250 Transfer completed successfully.
0004 rajan.000f :: 04/03/13 00:00:31 :: 125 Storing data set xxxxxxxxx.T000030.WINT1TBY.S000f
0004 rajan.000f :: 04/03/13 00:00:31 :: 250 Transfer completed successfully.
0004 rajan.000f :: 04/03/13 00:00:31 :: File xxxxxxxxx_G3INF0000000108647.CSV.PGP was an SAF from WINT1TBY.153F
0004 rajan.000f :: 04/03/13 00:00:31 :: No delete option was set for file xxxxxxxxx_G3INF0000000108647.CSV.PGP
0004 rajan.000f :: 04/03/13 00:00:31 :: Transmission type set to Binary
0004 rajan.000f :: 04/03/13 00:00:31 :: Customer retrieved file xxxxxxxxx_20130402.CSV.PGP 5656 bytes transferred
0004 rajan.000f :: 04/03/13 00:00:31 :: Customer has successfully retrieved file xxxxxxxxx_20130402.CSV.PGP (12 records/5656 bytes)
0004 rajan.000f :: 04/03/13 00:00:31 :: FTPWATCH: (saf_file) SAF_INFO returned WINT1TBY,741F,12,5656
0004 rajan.000f :: 04/03/13 00:00:33 :: 125 Storing data set xxxxxxxxx.T000032.rajan.S000f
0004 rajan.000f :: 04/03/13 00:00:33 :: 250 Transfer completed successfully.

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 07:20:22 GMT

The example query below will give you the number of files, records and bytes transferred per user. Assumptions;

no fields are previously extracted and will thus be made inline with rex,
all relevant information can be found in the 'Customer has successfully retrieved...' messages
user name is found in beginning of the line (between '0004 ' and '.000f')
timestamps are already being parsed correctly

index=blah sourcetype=bleh "Customer has successfully retrieved file" | rex "^\S+\s(?<userid>\S+)\s" | rex "$(?<record_count>\d+)\srecords/(?<byte_count>\d+)\sbytes$$" | stats count as FileCount sum(record_count) as RecordCount sum(byte_count) as ByteCount by userid

EDIT: userid regex now includes all characters between the first and second space.

Hope this helps,

Kristian

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 09:05:26 GMT

Thanks for your quick response, that was useful. However this query gives result for particular user only. I am looking to compute the statistics for all the users.

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 09:30:26 GMT

No. Or well. IF the user information is located where I assumed, the search will give the statistics per user.

Note that my regex stipulates that userid's should contain only 'word' characters (\w). So if they contain #.-! etc, they won't be extracted.

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 10:02:42 GMT

The prvivded search works for user id end with .000f only, typical userid will suffixed with some numbers such as YAHOO3SN.871F, ANALF4DY.874F,FISGMEFX.880F etc.,

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 10:08:13 GMT

which part exactly is the username?

rajan
rajan.000f

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 10:17:39 GMT

updated the regex to capture more characters. see above.

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 10:42:28 GMT

is the user-id, the search is much better with new regex, thanks a lot for taking time to answer.

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 11:03:24 GMT

I am able make out the regex what I want. I an done thanks a lot.

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 12:05:16 GMT

Well, maybe you figured out how to do it yourself, but if you DON'T want the 'second' part, i.e. '.123X', you can write the regex as;

rex "^\S+\s(?<userid>[^.]+)\.\S+\s"

which reads as;

from the start;
- one or more non-space characters (the 0004)
- one space character (space or tab)
- one or more non-dot characters (this is what we capture as userid)
- a single dot (hey, a dot)
- one or more non-space character (000F, 871F etc)
- one space character (space or tab)

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 12:53:39 GMT

yea, that works except the filename is missing in the seach result. Below is the regex, the search result to include filename, which appears next to "Customer has successfully retrieved file", can you help me how do i include that in the search result.

index=main sourcetype=summa "Customer has successfully retrieved file" | rex "^\S+\s(?<userid>\S+)\." | rex "$(?<record_count>\d+)\srecords/(?<byte_count>\d+)\sbytes$$" | stats count as FileCount sum(record_count) as RecordCount sum(byte_count) as ByteCount by userid

Re: FTP file staistics

kristian_kolb — Fri, 18 Oct 2013 13:06:41 GMT

Change your second rex to;

| rex "\s(?<file_name>\S+)\s$(?<record_count>\d+)\srecords/(?<byte_count>\d+)\sbytes$$"

and rewrite your stats to;

| stats count as FileCount list(file_name) as FileName sum(record_count) as RecordCount sum(byte_count) as ByteCount by userid

Combined, this will add a list of the file names transferred.

Re: FTP file staistics

srajanbabu — Fri, 18 Oct 2013 13:39:55 GMT

Awesome, thanks a lot

Rajan