Kaseya Community

Workstation count and Server count per Parent Organization

This question has suggested answer(s)

Hey dudes,

I hope I'm not asking for too much here. In Kaseya I have two views called "Workstation" and "Server".

Is there a way for me to generate and SQL query report that tells the number of workstations and the number of servers that are contained within each parent organization? By parent organization I mean, if I have two machines groups texas.houston.root and texas.atlanta.root, I want all these machines to show up in the report under texas.root.

 

So basically something like:

Machine Group                                   Servers                            Workstations
-----------------------------------------------------------------------------------------------------
texas.root                                               11                                        200
california.root                                         20                                        400

Any help would be greatly appreciated with smiley emoticons and such. If I have to create two separate reports for Servers and Workstation that is fine!



[edited by: aabbasi at 12:11 PM (GMT -7) on 8-8-2011] typo
Verified Answer
  • This is the SQL Statement you need to get the data you are looking for:

    SELECT groupName as Organisation, COUNT (osInfo) as Servers FROM vMachine

    where osInfo like '%server%'

    GROUP BY GroupName

    SELECT groupName as Organisation, COUNT (osInfo) as Workstations FROM vMachine

    where osInfo not like '%server%'

    GROUP BY GroupName;

All Replies
  • I use Excel to connect directly to the SQL database then pull the data from "ksubscribers"."dbo"."vMachine"

    Once I have this I then create a pivot table which lets me break down this info by OS type.

  • I am working on this as well. I have the report so far but each group is a single line based on Machine_GroupID but I don't know enough SQL to be able to combine the groups with multiple subgroups.

  • , I can't seem to find the proper table for a Parent Organization view, though. The best I've found is groupName under "vMachine."



    [edited by: aabbasi at 10:11 AM (GMT -8) on 2-28-2012] typo
  • If you're using views that's about as good as it gets.  If you're into the SQL tables there's a table called kasadmin.org that may be what you're after.

  • I am looking for exactly the same as this, did anyone manage to get this working?

  • This is the SQL Statement you need to get the data you are looking for:

    SELECT groupName as Organisation, COUNT (osInfo) as Servers FROM vMachine

    where osInfo like '%server%'

    GROUP BY GroupName

    SELECT groupName as Organisation, COUNT (osInfo) as Workstations FROM vMachine

    where osInfo not like '%server%'

    GROUP BY GroupName;

  • SELECT groupName as Organisation, COUNT (osInfo) as Servers FROM vMachine

    where osInfo like '%server%'

    GROUP BY GroupName

    SELECT groupName as Organisation, COUNT (osInfo) as Workstations FROM vMachine

    where osInfo not like '%server%'

    GROUP BY GroupName;

  • Hi Glen,

    thks

    when I run it in management studio : I get 2 different windows. Would it be possible to

    Organisation  Servers Workstations     on one line, easier in order to create an output file.

    rgds