I think I am holding it wrong, but I am cross join...
# general
t
I think I am holding it wrong, but I am cross joining the processes table with the cpu info table to include CPU info in my query results but in doing so I am only ever getting the process
kernel_task
returned
here is the query I have tried
cross 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 results
f
not sure why but the three printf() lines are causing this, if you comment them out it looks to return all processes
Copy code
SELECT 
  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;
t
@FG man THANK YOU I was banging my head against the wall
👍 1
f
i think this works now
Copy code
SELECT 
  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;
moved the calculations into the sub-select to virtually create those columns as part of the cput_time table, and the select at the top seems to like that. not sure what the actual but is though.
t
well I am working around a query result issue to get floats to match what the output of
osqueryi
is, see this github issue: https://github.com/osquery/osquery/issues/8301
yeah that works maybe order of ops issue? weird that some how my query was working in pieces but not all strung together and I wrote it like I would SQL in other data tools but maybe that is my problem. Other data tools are more forgiving or do diff order of ops
f
the printf() also seems extraneous, so maybe remove them to reduce a few more calls
Copy code
SELECT 
  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;
t
unfortunately due to that rounding bug in the GitHub issue I linked I need to run those
printf
statements because downstream the data shows up like this
f
ah gotcha
well at least it works
would need to compare line by line and maybe the bug/cause will become apparent
t
yeah I don't know why this is happening either ha I just am writing SQL like I do in our SQL data platforms that osquery pipes data to, but SQLite isn't always the same so I am pretty sure it is me not understanding the differences
f
new query plan is only 135 steps, the reordering of the math definitely affected the plan
the output is too big for me to post here
Screenshot 2024-06-20 at 3.11.02 PM.png
t
neat I will read that and pretend to understand it
😆 1