Report for groups

[Susan Gum]

Is there a way to export or find out what group all my computers are in? I have done an inventory and hosts report and it doesn’t have that column in the report. I know I have some stragglers in there that need to go into groups but I cant tell which ones they are.

[Kiweegie]

I too would be keen to know if this is possible. I’ve seen the upload new report link but haven;t seen anywhere on line outlining how to create your own custom report.

[moss]

I know very little about SQL but if you install phpmyadmin on the fog server (do a quick search on here, there’s a few guides on how to do it) you can perform SQL queries on the database. The following will join the groupMembers table and the hosts table, showing which group ID (not name) hosts are associated with. It ‘should’ be fairly easy to build upon that and join the groups table too.
[CODE]
SELECT *
FROM groupMembers
JOIN hosts
ON groupMembers.gmHostID = hosts.hostID
[/CODE]

[moss]

Just had a go at adding the group names to the query. Ive came up with this:
[CODE]
SELECT *
FROM groupMembers
JOIN hosts
ON groupMembers.gmHostID = hosts.hostID
JOIN groups
ON groupMembers.gmGroupID = groups.groupID
[/CODE]
I’m no coder/DB admin so someone else might have a better solution

[Kiweegie]

Hey Moss,

that’s pretty helpful, though oddly with both SQL entries i get the same output? In any case enough to go on for now - I’ve hand edited the gmGroupID to match actual bank numbers so it’s easier to view the output. I’m even less of a coder/DBA than you but if I could get example report code from someone I could have a crack at editing it for my needs. I’m guessing to upload a report to Fog it would require integration with the GUI as well - think a feature request might be the best way.

[moss]

I definitely get different outputs from the two queries, the 2nd query gives the extra group name fields etc. I’m not sure what format etc the upload report requires but I would do it manually by copying an existing predefined report and editing the source code.
Hopefully someone else can confirm this, but I believe the existing reports are located:
/var/www/fog/management/reports
A custom report building module would be a nice feature but I’m not sure if I have time to try and build one (or if I even have the ability to do so).

[Kiweegie]

Hey Moss,

ok learning more about SQL than I ever thought I would (or would need to). Your pointer to location of the reports in fog has definitely helped.

I’ve managed to get this working albeit with the group ID showing rather than the name (working on that and will post back once I’ve sussed it).

Under the echo ( “<div>” ); section I added this line:

            $report->addCSVCell(_("Group"));

then the SQL reads now as:

                            $sql = "SELECT
                                            *
                                    FROM
                                            hosts
                                            left outer join images on ( hostImage = imageID )
                                            left outer join supportedOS on ( hostOS = osID )
                                            inner join groupMembers on ( hosts.hostID = groupMembers.gmHostID)
                                            ";

Important bit is the last line referencing inner join

And lastly add this to the last array section

$report->addCSVCell($ar[“gmGroupID”]);

Save the file on your local machine upload to fogserver and voila - an extra column after the host showing the group id - which I have managed to manually edit to match my desk bank numbering scheme as a quick and dirty hack

[moss]

I don’t have access to FOG/SQL at the minute as I only use it at work, but this part of the query:
[CODE]ON groupMembers.gmGroupID = groups.groupID[/CODE]
Was what I used also pull the group names in from the groups table. So I’m sure you would have to include a modified version of this as part of your PHP/SQL query.

[Kiweegie]

Moss you are a legend.

left outer join groupMembers on ( hostID = gmHostID)
left outer join groups on ( gmGroupID = groupID)

added to the SQL works a treat. Thanks for your help. Thats made life so much easier (or the opposite more likely as I now have another language to learn). Cheers!

[moss]

Glad you managed to sort it, SQL is also on my ever-growing list of things to learn.