XQL converting Bytes to MB or GB

[Bojan-Totic]

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

[KanwarSingh01 · Accepted solution]

@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.

[bbarmanroy]

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" 

[Bojan-Totic]

Thank you both so much, that worked great 😄 

Love the visual representation as well!

[MosR]

This is very helpful. Would be nice if you could add a date to the graph to know when an endpoint took action. 

[JRzepka]

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)))