- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
05-18-2022 01:29 PM
Hey!
I was just wondering if anyone knows of a way to get the total download/upload to show in MB or GB rather than bytes through an XQL queries' output?
XQL Query
dataset = xdr_data // Using the xdr dataset
| filter event_type = ENUM.NETWORK // Filtering by network activity
| fields action_upload, action_remote_ip as remote_ip, action_external_hostname as remote_hostname, actor_process_image_name as process_name // Selecting the relevant fields
| comp sum(action_upload) as total_upload by process_name, remote_ip, remote_hostname // Summing the total upload by process + ip + host
| sort desc total_upload // Sorting by total upload
| limit 10 // Limiting the results to only the top 10
05-19-2022 02:15 AM - edited 05-19-2022 02:15 AM
@Bojan-Totic Please try the below XQL query, you should be able to get your result in MB, similarly you can also convert into GB as per your convenience.
dataset = xdr_data // Using the xdr dataset
| filter event_type = ENUM.NETWORK // Filtering by network activity
| fields action_upload, action_remote_ip as remote_ip, action_external_hostname as remote_hostname, actor_process_image_name as process_name // Selecting the relevant fields
| comp sum(action_upload) as t_upload by process_name, remote_ip, remote_hostname // Summing the total upload by process + ip + host
| alter total_upload = to_integer(divide(t_upload,1048576))//1 MB == 1,048,576 Bytes (Based on the maths, if it is correct we can use the value.)
| fields remote_ip,remote_hostname,process_name,total_upload
| sort desc total_upload// Sorting by total upload
| limit 10 // Limiting the results to only the top 10
Thank You.
05-19-2022 02:15 AM - edited 05-19-2022 02:15 AM
@Bojan-Totic Please try the below XQL query, you should be able to get your result in MB, similarly you can also convert into GB as per your convenience.
dataset = xdr_data // Using the xdr dataset
| filter event_type = ENUM.NETWORK // Filtering by network activity
| fields action_upload, action_remote_ip as remote_ip, action_external_hostname as remote_hostname, actor_process_image_name as process_name // Selecting the relevant fields
| comp sum(action_upload) as t_upload by process_name, remote_ip, remote_hostname // Summing the total upload by process + ip + host
| alter total_upload = to_integer(divide(t_upload,1048576))//1 MB == 1,048,576 Bytes (Based on the maths, if it is correct we can use the value.)
| fields remote_ip,remote_hostname,process_name,total_upload
| sort desc total_upload// Sorting by total upload
| limit 10 // Limiting the results to only the top 10
Thank You.
05-19-2022 08:08 PM
Hi @Bojan-Totic This is what I use:
preset = network_story
| fields action_total_upload as upload, action_local_ip as source_ip, action_local_port as source_port, action_remote_ip as dst_ip, action_remote_port as dst_port,dst_action_external_hostname as hostname, actor_process_image_name as process_name
| comp sum(upload ) as total_upload by source_ip , dst_ip , hostname , process_name, source_port, dst_port
| alter total_upload_KB = divide(total_upload , 1024) // convert bytes to KB
| alter total_upload_MB = divide(total_upload_KB , 1024) // convert KB to MB
| alter total_upload_GB = divide(total_upload_MB , 1024) // convert MB to GB
|alter total_upload_GB_rounded = round(total_upload_GB) // round float to integer
|fields source_ip , source_port, dst_ip , dst_port, dst_port, hostname , process_name, total_upload_GB_rounded
|sort desc total_upload_GB_rounded
| view graph type = scatter header = "Large Uploads" xaxis = source_ip yaxis = total_upload_GB_rounded xaxistitle = "Source IP Address" yaxistitle = "GB uploaded"
05-25-2022 08:06 AM
Thank you both so much, that worked great 😄
Love the visual representation as well!
11-01-2023 10:56 AM
This is very helpful. Would be nice if you could add a date to the graph to know when an endpoint took action.
11-03-2023 06:39 AM
Here is another one using the pow function.
| alter total_mbytes = round(divide(t_upload, pow(2,20)))
| alter total_gbytes = round(divide(t_upload, pow(2,30)))
| alter total_tbytes = round(divide(t_upload, pow(2,40)))
Click Accept as Solution to acknowledge that the answer to your question has been provided.
The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!
These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!
The LIVEcommunity thanks you for your participation!