tlark
06/20/2024, 6:19 PMkernel_task
returnedtlark
06/20/2024, 6:21 PMcross join
and now left outer join
there is no primary key to join on so I just want to add this info to the query resultsFG
06/20/2024, 6:45 PMSELECT
p.cmdline
, p.cwd
, p.name
, p.path
, p.pid
, p.on_disk
, p.disk_bytes_read
, p.disk_bytes_written
, p.resident_size
, p.wired_size
, p.start_time
, p.total_size as ram_used_bytes
, p.uid
, p.user_time
, ci.model
, ci.number_of_cores
, ci.logical_processors
, ci.max_clock_speed
, ci.number_of_efficiency_cores
, ci.number_of_performance_cores
, CAST(ROUND(((user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)) * 100, 2) as text) AS percent_cpu_process
, CAST(ROUND(((total_size * 1.0) / (1024 * 1024)), 2) as text) AS total_ram_used_mb
--, printf(ROUND((CAST(SUM(system) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2)) AS percent_cpu_sys
-- , printf(ROUND((CAST(SUM(user) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2)) AS percent_cpu_user
-- , printf(ROUND((CAST(SUM(idle) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2)) AS percent_cpu_idle
, cpu_time.steal as steal
, cpu_time.core as core
FROM processes as p,(
SELECT (
SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb
, SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
, steal
, core
, user
, idle
, system
FROM cpu_time
) AS cpu_time
left join cpu_info as ci
limit 10;
tlark
06/20/2024, 6:47 PMFG
06/20/2024, 6:52 PMSELECT
p.cmdline
, p.cwd
, p.name
, p.path
, p.pid
, p.on_disk
, p.disk_bytes_read
, p.disk_bytes_written
, p.resident_size
, p.wired_size
, p.start_time
, p.total_size as ram_used_bytes
, p.uid
, p.user_time
, ci.model
, ci.number_of_cores
, ci.logical_processors
, ci.max_clock_speed
, ci.number_of_efficiency_cores
, ci.number_of_performance_cores
, CAST(ROUND(((user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)) * 100, 2) as text) AS percent_cpu_process
, CAST(ROUND(((total_size * 1.0) / (1024 * 1024)), 2) as text) AS total_ram_used_mb
, cpu_time.percent_cpu_sys
, cpu_time.percent_cpu_user
, cpu_time.percent_cpu_idle
, cpu_time.steal as steal
, cpu_time.core as core
FROM processes as p,(
SELECT (
SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb
, SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
, printf(ROUND((CAST(SUM(system) AS FLOAT)/(SUM(cpu_time.idle)+SUM(cpu_time.system)+SUM(cpu_time.user)))*100,2)) AS percent_cpu_sys
, printf(ROUND((CAST(SUM(user) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2)) AS percent_cpu_user
, printf(ROUND((CAST(SUM(idle) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2)) AS percent_cpu_idle
, steal
, core
, user
, idle
, system
FROM cpu_time
) AS cpu_time
cross join cpu_info as ci
limit 10;
FG
06/20/2024, 6:52 PMtlark
06/20/2024, 6:55 PMosqueryi
is, see this github issue:
https://github.com/osquery/osquery/issues/8301tlark
06/20/2024, 7:00 PMFG
06/20/2024, 7:01 PMSELECT
p.cmdline
, p.cwd
, p.name
, p.path
, p.pid
, p.on_disk
, p.disk_bytes_read
, p.disk_bytes_written
, p.resident_size
, p.wired_size
, p.start_time
, p.total_size as ram_used_bytes
, p.uid
, p.user_time
, ci.model
, ci.number_of_cores
, ci.logical_processors
, ci.max_clock_speed
, ci.number_of_efficiency_cores
, ci.number_of_performance_cores
, CAST(ROUND(((user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)) * 100, 2) as text) AS percent_cpu_process
, CAST(ROUND(((total_size * 1.0) / (1024 * 1024)), 2) as text) AS total_ram_used_mb
, cpu_time.percent_cpu_sys
, cpu_time.percent_cpu_user
, cpu_time.percent_cpu_idle
, cpu_time.steal as steal
, cpu_time.core as core
FROM processes as p,(
SELECT (
SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb
, SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
, ROUND((CAST(SUM(system) AS FLOAT)/(SUM(cpu_time.idle)+SUM(cpu_time.system)+SUM(cpu_time.user)))*100,2) AS percent_cpu_sys
, ROUND((CAST(SUM(user) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2) AS percent_cpu_user
, ROUND((CAST(SUM(idle) AS FLOAT)/(SUM(idle)+SUM(system)+SUM(user)))*100,2) AS percent_cpu_idle
, steal
, core
, user
, idle
, system
FROM cpu_time
) AS cpu_time
cross join cpu_info as ci
limit 10;
tlark
06/20/2024, 7:05 PMprintf
statements because downstream the data shows up like thisFG
06/20/2024, 7:07 PMFG
06/20/2024, 7:07 PMFG
06/20/2024, 7:08 PMtlark
06/20/2024, 7:09 PMFG
06/20/2024, 7:10 PMFG
06/20/2024, 7:10 PMFG
06/20/2024, 7:11 PMtlark
06/20/2024, 7:13 PM