topic Re: XQL converting Bytes to MB or GB in Cortex XDR Discussions

XQL converting Bytes to MB or GB

Bojan-Totic — Wed, 18 May 2022 20:29:39 GMT

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

Re: XQL converting Bytes to MB or GB

KanwarSingh01 — Thu, 19 May 2022 09:15:21 GMT

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

Re: XQL converting Bytes to MB or GB

bbarmanroy — Fri, 20 May 2022 03:08:22 GMT

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"

Re: XQL converting Bytes to MB or GB

Bojan-Totic — Wed, 25 May 2022 15:06:16 GMT

Thank you both so much, that worked great 😄

Love the visual representation as well!

Re: XQL converting Bytes to MB or GB

MosR — Wed, 01 Nov 2023 17:56:31 GMT

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

Re: XQL converting Bytes to MB or GB

JRzepka — Fri, 03 Nov 2023 13:39:50 GMT

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