Deployment Solution

I have a need to directly query the SQL database for DS 6.9SP5 and return just the currently active computers.  I know this is a simple condition within the console but I have been unable to reverse engineer the SQL query.  Would someone be able to provide the table(s) and field(s) I need for my query?

Thank you in advance!

The sessions table is what you're looking for and the boot_env column. '2' from what I can gather is the environment state for windows. Below should do the job for you.

 

SELECT  com.name

      ,[client_ip]

      ,[boot_env]

  FROM [eXpress].[dbo].[sessions] sess

  

  join computer com on com.computer_id = sess.computer_id where sess.boot_env = '2'

It's actually the "icon" column in the computer table.

By Active do you mean "on but nobody logged in" or simply "on"?

 

Here's the query for showing computers that are "on":

 

SELECT DISTINCT

computer.name,

computer.icon

FROM [computer]

 

Where computer.icon != '0' --means offline workstation

AND computer.icon != '41' --means offline server

I mean 'on' but now that you mention it knowing if a user is logged on would be useful as well. HighTower, would you be able to point me to some documentation as to what the values in the icon column mean aside from the comments you included? I should note that running HighTower's query returned more computers than the on from b3tts32, though the latter query returned the exact number of machines the console saw as on. However, I suspect that with multiple results, such as 2, 25 and 50 in the icon column HighTower's query will in the long run provide me with more information. Thank you both!

I haven't been able to find any of this documented.  I discovered this at some point in the past for similar reasons as what you're describing.

Fortunately there are not a lot of icon values and it's easy enought to have your SQL query open next to your DS console and make the association of what = what.

Thank you again.  I started to make the database to console compare.  It looks like the codes vary based on OS, Linux being different than Windows in terms of the code.

 

So far I have:

0 Windows, powered off

2 Windows, powered on

25 Windows, logged on

26 Windows, appears to be the code for shutting down

27 Windows, logged on and in hibernate/sleep or so it seems

39 Windows, server, powered on not logged in

40 Windows server, logged in

41 Windows server, powered off (surmising from your code)

50 Linux powered off

53 Linux logged on