XQL converting Bytes to MB or GB

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements
Please sign in to see details of an important advisory in our Customer Advisories area.

XQL converting Bytes to MB or GB

L1 Bithead

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

 

1 accepted solution

Accepted Solutions

L3 Networker

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

Kind Regards
KS

View solution in original post

5 REPLIES 5

L3 Networker

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

Kind Regards
KS

L5 Sessionator

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"

 

bbarmanroy_0-1653015323519.png

 

 

 

L1 Bithead

Thank you both so much, that worked great 😄 

Love the visual representation as well!

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

L2 Linker

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

  • 1 accepted solution
  • 2998 Views
  • 5 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

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!