topic Re: Number of uploads for the top users in Splunk Search

How to create a table for number of uploads for the top users?

TrangCIC81 — Thu, 23 Mar 2023 14:48:39 GMT

Hello All,

I have been able to create a table that lists the top users that have been uploading files the most to cloud storage services for a certain time range as set in shared time picker with the following queries.

(time range:last month)

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | table end_time,user,src,src_remarks01,url,bytes_in | rename "end_time" as "Access date and time", "user" as "Username", "src" as "IP address", "src_remarks01" as "Asset information", "url" as "FQDN", "bytes_in" as "BytesIn(GB)" | sort - BytesIn(GB) | head 10

The result of the above search is as follows (for example).

"Access date and time" "Username" "IP address" "Asset information" "FQDN" "BytesIn(GB)"
2023-02-20 03:04:05 aa X.X.X.X mmm box.com 3.5
2023-02-21 06:07:08 bb Y.Y.Y.Y nnn firestorage.com 1.3
2023-02-22 09:10:11 cc Z.Z.Z.Z lll onedrive.com 0.3
.
.
.
.
.

Now, I am trying to get the number of (file) uploads in the last month for each user corresponding to each FQDN in the result above.
However, I still cannot make a correct search for it with the following queries using subsearch.

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing"
[ search index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing"
| eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| eval bytes_in=bytes_in/1024/1024/1024
| eval bytes_in=round(bytes_in, 2)
| table end_time,user,src,src_remarks01,url,bytes_in
| sort - bytes_in
| head 10
| fields user url
| rename user as username, url as FQDN ]
| where bytes_in>0
| stats count sum(bytes_in) as Number_File_Uploads by username FQDN
| table end_time,username,src,src_remarks01,FQDN,bytes_in,Number_File_Uploads
| rename "end_time" as "Access date and time", "src" as "IP address", "src_remarks01" as "Asset information", "bytes_in" as "BytesIn(GB)"

And as the result, I would like the column "Number of uploads" to be added to the table of the first result at the end like this.

"Access date and time" "Username" "IP address" "Asset information" "FQDN" "BytesIn(GB)" "Number of uploads (times)"
2023-02-20 03:04:05 aa X.X.X.X mmm box.com 3.5 10
2023-02-21 06:07:08 bb Y.Y.Y.Y nnn firestorage.com 1.3 20
2023-02-22 09:10:11 cc Z.Z.Z.Z lll onedrive.com 0.3 5
.
.
.
.
.

Does anyone have any idea on the seach queries that I am trying to do.

Many thanks.

Re: Number of uploads for the top users

ITWhisperer — Thu, 23 Mar 2023 09:11:48 GMT

Try something like this (move the rename to outside the subsearch, and use eventstats)

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" [ search index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | table end_time,user,src,src_remarks01,url,bytes_in | sort - bytes_in | head 10 | fields user url] | rename user as username, url as FQDN | where bytes_in>0 | eventstats count sum(bytes_in) as Number_File_Uploads by username FQDN | table end_time,username,src,src_remarks01,FQDN,bytes_in,Number_File_Uploads | rename "end_time" as "Access date and time", "src" as "IP address", "src_remarks01" as "Asset information", "bytes_in" as "BytesIn(GB)"

Re: Number of uploads for the top users

TrangCIC81 — Thu, 23 Mar 2023 10:21:33 GMT

Thank you so much. It now can show the column "Number_File_Uploads" at the end of the table.

In addition to that, however, I see the "Access date and time" has now disappeared and the "username" is no more the same as the first result.

How can I keep the "Access date and time" values being shown, basically all the columns's values that are shown at the first result should be kept the same, just the "Number_File_Uploads" being added with its count values.

Appreciate your help.

Re: Number of uploads for the top users

ITWhisperer — Thu, 23 Mar 2023 11:20:33 GMT

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" [ search index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | table user,bytes_in | sort - bytes_in | head 10 | fields user url] | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eval bytes_in=bytes_in/1024/1024/1024 | rename user as username, url as FQDN | where bytes_in>0 | eventstats count sum(bytes_in) as Number_File_Uploads by username FQDN | table end_time,username,src,src_remarks01,FQDN,bytes_in,Number_File_Uploads | rename "end_time" as "Access date and time", "src" as "IP address", "src_remarks01" as "Asset information", "bytes_in" as "BytesIn(GB)"

Re: Number of uploads for the top users

TrangCIC81 — Tue, 28 Mar 2023 10:11:38 GMT

@ITWhisperer wrote:
Try something like this (move the rename to outside the subsearch, and use eventstats)

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" [ search index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | table end_time,user,src,src_remarks01,url,bytes_in | sort - bytes_in | head 10 | fields user url] | rename user as username, url as FQDN | where bytes_in>0 | eventstats count sum(bytes_in) as Number_File_Uploads by username FQDN | table end_time,username,src,src_remarks01,FQDN,bytes_in,Number_File_Uploads | rename "end_time" as "Access date and time", "src" as "IP address", "src_remarks01" as "Asset information", "bytes_in" as "BytesIn(GB)"

Thanks!
Unfortunately the above did not keep the user column's values as well as the other columns's values staying the same as the first result.

I have tried with more testings and got the desired result with the following queries. Posting here so somebody else can refer in the future.

index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" [ search index=proxy sourcetype="XXX" filter_category="File_Storage/Sharing" | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | table end_time,user,url,bytes_in | sort - bytes_in | head 20 | fields user url ] | eval end_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | eventstats count(eval(bytes_in>0)) as Number_File_Uploads by user url | table end_time,user,src,src_remarks01,url,bytes_in,Number_File_Uploads | eval bytes_in=bytes_in/1024/1024/1024 | eval bytes_in=round(bytes_in, 2) | sort - bytes_in | head 20 | rename "end_time" as "Access date and time", "src" as "IP address", "src_remarks01" as "Asset information", "bytes_in" as "BytesIn(GB)"