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 t...
See more...
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.