How to Auto-refresh dashboard that using function,module and GLPI MySql query

Universal Dashboard Universal Dashboard Help
1 
Universal Dashboard version 2.8.3

Hi everyone ,
I’am using SimplySQL Module.
It provide me many cmdlet for query and connecting to MySql database.
I have no problems when I execute the dashboard (query and result are correctly displayed )

I’am trying now to run this query periodicly for updating the result in the dashboard.
I have try (litteraly) a thousand of thing , without any success.

There is a example of what i’am trying to do :

Import-Module SimplySQL
$GlpiServerName = 'MyServerAddress'
$GlpiDatabase = 'MyGlpiDatabaseName'
$Credential = get-credential
$GlpiConnexion = Open-MySqlConnection -Server $GlpiServerName -Database $GlpiDatabase -Credential $Credential 
Show-SqlConnection

$MySqlQuery = 'SELECT DISTINCT `glpi_entities`.`name` AS `ITEM_Ticket_80`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname`) AS `NomU`, GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname`) AS `PrenomU`,`glpi_tickets`.`id`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`phone`) AS `ITEM_Ticket_phone1`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email`)) AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`completename`)
ORDER BY `glpi_groups_1358be41ea7158319a888236ffba5355`.`id`) AS `ITEM_Ticket_8`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60` ON (`glpi_tickets`.`id` = `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id` AND `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1 )
LEFT JOIN `glpi_users` AS `glpi_users_a5e2026cbcb683e774f93a59e24a62a4` ON (`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id` = `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_users` AS `glpi_users_f201be21cd638ee780d08cba4ceff0d4` ON (`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` = `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
LEFT JOIN `glpi_tickettasks` ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2` ON (`glpi_tickettasks`.`groups_id_tech` = `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id` )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_groups` AS `glpi_groups_1358be41ea7158319a888236ffba5355` ON (`glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`groups_id` = `glpi_groups_1358be41ea7158319a888236ffba5355`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( `glpi_tickets`.`status` IN ("1","2","3","4") AND ((`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` IS NULL) OR `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email` IS NULL) AND (`glpi_tickets`.`type` = "1") AND ( (`glpi_groups_1358be41ea7158319a888236ffba5355`.`id` = "45")) ) GROUP BY `glpi_tickets`.`id`'

$MySqlResults = @(Invoke-SqlQuery -Query $MySqlQuery)
$NumberOfResult = $MySqlResults.count

#-------------------Here it works--------------------
#The RandomNumber are correctly refresh every 5 second

$Schedule = New-UDEndpointSchedule -Every 5 -Second 
$Endpoint = New-UDEndpoint -Schedule $Schedule -Endpoint {
    $Cache:RandomNumber = get-random
}
#----------------------------------------------------

$dashboard = New-UDDashboard -Title "Test" -Content {
    New-UDCounter -Title "Test RandomNumber" -Id "Counter" -Endpoint {
        $Cache:RandomNumber
    } -AutoRefresh -RefreshInterval 1

    New-UDCounter -Title "Test SQL" -Id "Counter2" -Endpoint {
        $NumberOfResult
    } -AutoRefresh -RefreshInterval 1
}
Start-UDDashboard -Port 10001 -Endpoint $Endpoint -Dashboard $Dashboard

So when i’am trying this i have no result in the ‘Test SQL’ counter. I’am tring to move the invoke-sqlquery in the endpoint

$MySqlResults = @(Invoke-SqlQuery -Query $MySqlQuery)
$cache:NumberOfResult = $MySqlResults.count

here the modified script : :slight_smile:

Import-Module SimplySQL
$GlpiServerName = 'MyServerAddress'
$GlpiDatabase = 'MyGlpiDatabaseName'
$Credential = get-credential
$GlpiConnexion = Open-MySqlConnection -Server $GlpiServerName -Database $GlpiDatabase -Credential $Credential 
Show-SqlConnection
$MySqlQuery = 'SELECT DISTINCT `glpi_entities`.`name` AS `ITEM_Ticket_80`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname`) AS `NomU`, GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname`) AS `PrenomU`,`glpi_tickets`.`id`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`phone`) AS `ITEM_Ticket_phone1`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email`)) AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`completename`)
ORDER BY `glpi_groups_1358be41ea7158319a888236ffba5355`.`id`) AS `ITEM_Ticket_8`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60` ON (`glpi_tickets`.`id` = `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id` AND `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1 )
LEFT JOIN `glpi_users` AS `glpi_users_a5e2026cbcb683e774f93a59e24a62a4` ON (`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id` = `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_users` AS `glpi_users_f201be21cd638ee780d08cba4ceff0d4` ON (`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` = `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
LEFT JOIN `glpi_tickettasks` ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2` ON (`glpi_tickettasks`.`groups_id_tech` = `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id` )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_groups` AS `glpi_groups_1358be41ea7158319a888236ffba5355` ON (`glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`groups_id` = `glpi_groups_1358be41ea7158319a888236ffba5355`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( `glpi_tickets`.`status` IN ("1","2","3","4") AND ((`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` IS NULL) OR `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email` IS NULL) AND (`glpi_tickets`.`type` = "1") AND ( (`glpi_groups_1358be41ea7158319a888236ffba5355`.`id` = "45")) ) GROUP BY `glpi_tickets`.`id`'

#-------------------Here it doesn't works  :-( --------------------
#No result are displayed in the UD-Counter 'Test SQL'

$Schedule = New-UDEndpointSchedule -Every 5 -Second 
$Endpoint = New-UDEndpoint -Schedule $Schedule -Endpoint {
    $Cache:RandomNumber = get-random
    $MySqlResults = @(Invoke-SqlQuery -Query $MySqlQuery)
    $cache:NumberOfResult = $MySqlResults.count
}
#----------------------------------------------------

$dashboard = New-UDDashboard -Title "Test" -Content {
    New-UDCounter -Title "Test RandomNumber" -Id "Counter" -Endpoint {
        $Cache:RandomNumber
    } -AutoRefresh -RefreshInterval 1

    New-UDCounter -Title "Test SQL" -Id "Counter2" -Endpoint {
        $cache:NumberOfResult
    } -AutoRefresh -RefreshInterval 1
}

Start-UDDashboard -Port 10001 -Endpoint $Endpoint -Dashboard $Dashboard

Any advice, ideas will be very appreciate !

Jon

2

Have you tried to move the whole script block into the endpoint?



#-------------------Here it doesn't works  :-( --------------------
#No result are displayed in the UD-Counter 'Test SQL'

$Schedule = New-UDEndpointSchedule -Every 5 -Second 
$Endpoint = New-UDEndpoint -Schedule $Schedule -Endpoint {
Import-Module SimplySQL
$GlpiServerName = 'MyServerAddress'
$GlpiDatabase = 'MyGlpiDatabaseName'
$Credential = get-credential
$GlpiConnexion = Open-MySqlConnection -Server $GlpiServerName -Database $GlpiDatabase -Credential $Credential 
Show-SqlConnection
$MySqlQuery = 'SELECT DISTINCT `glpi_entities`.`name` AS `ITEM_Ticket_80`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname`) AS `NomU`, GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname`) AS `PrenomU`,`glpi_tickets`.`id`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`phone`) AS `ITEM_Ticket_phone1`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email`)) AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`completename`)
ORDER BY `glpi_groups_1358be41ea7158319a888236ffba5355`.`id`) AS `ITEM_Ticket_8`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60` ON (`glpi_tickets`.`id` = `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id` AND `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1 )
LEFT JOIN `glpi_users` AS `glpi_users_a5e2026cbcb683e774f93a59e24a62a4` ON (`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id` = `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_users` AS `glpi_users_f201be21cd638ee780d08cba4ceff0d4` ON (`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` = `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
LEFT JOIN `glpi_tickettasks` ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2` ON (`glpi_tickettasks`.`groups_id_tech` = `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id` )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_groups` AS `glpi_groups_1358be41ea7158319a888236ffba5355` ON (`glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`groups_id` = `glpi_groups_1358be41ea7158319a888236ffba5355`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( `glpi_tickets`.`status` IN ("1","2","3","4") AND ((`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` IS NULL) OR `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email` IS NULL) AND (`glpi_tickets`.`type` = "1") AND ( (`glpi_groups_1358be41ea7158319a888236ffba5355`.`id` = "45")) ) GROUP BY `glpi_tickets`.`id`'
    $Cache:RandomNumber = get-random
    $MySqlResults = @(Invoke-SqlQuery -Query $MySqlQuery)
    $cache:NumberOfResult = $MySqlResults.count
}
1 Like
3

Victory :partying_face: :partying_face:
It work like a charm !! :hugs:

A easy “one more things” question ?

Can i have multiple endpoint with different script inside and different timer ? And is it the same process for the last version of Powershell Universal ?

I put here the complete and functional code for who’s want :

#-------------------Here it works--------------------
$Schedule = New-UDEndpointSchedule -Every 5 -Second 
$Endpoint = New-UDEndpoint -Schedule $Schedule -Endpoint {
Import-Module SimplySQL
$GlpiServerName = 'MyServerAddress'
$GlpiDatabase = 'MyGlpiDatabaseName'
$Credential = get-credential
$GlpiConnexion = Open-MySqlConnection -Server $GlpiServerName -Database $GlpiDatabase -Credential $Credential 
Show-SqlConnection
$MySqlQuery = 'SELECT DISTINCT `glpi_entities`.`name` AS `ITEM_Ticket_80`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname`) AS `NomU`, GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname`) AS `PrenomU`,`glpi_tickets`.`id`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`,GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`phone`) AS `ITEM_Ticket_phone1`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email`)) AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`completename`)
ORDER BY `glpi_groups_1358be41ea7158319a888236ffba5355`.`id`) AS `ITEM_Ticket_8`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60` ON (`glpi_tickets`.`id` = `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id` AND `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1 )
LEFT JOIN `glpi_users` AS `glpi_users_a5e2026cbcb683e774f93a59e24a62a4` ON (`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id` = `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_users` AS `glpi_users_f201be21cd638ee780d08cba4ceff0d4` ON (`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` = `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
LEFT JOIN `glpi_tickettasks` ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2` ON (`glpi_tickettasks`.`groups_id_tech` = `glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id` )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b` ON (`glpi_tickets`.`id` = `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`tickets_id` AND `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`type` = 2 )
LEFT JOIN `glpi_groups` AS `glpi_groups_1358be41ea7158319a888236ffba5355` ON (`glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`groups_id` = `glpi_groups_1358be41ea7158319a888236ffba5355`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( `glpi_tickets`.`status` IN ("1","2","3","4") AND ((`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` IS NULL) OR `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email` IS NULL) AND (`glpi_tickets`.`type` = "1") AND ( (`glpi_groups_1358be41ea7158319a888236ffba5355`.`id` = "45")) ) GROUP BY `glpi_tickets`.`id`'
    $Cache:RandomNumber = get-random
    $MySqlResults = @(Invoke-SqlQuery -Query $MySqlQuery)
    $cache:NumberOfResult = $MySqlResults.count
}
#----------------------------------------------------
$dashboard = New-UDDashboard -Title "Test" -Content {
    New-UDCounter -Title "Test RandomNumber" -Id "Counter" -Endpoint {
        $Cache:RandomNumber
    } -AutoRefresh -RefreshInterval 1

    New-UDCounter -Title "Test SQL" -Id "Counter2" -Endpoint {
        $NumberOfResult
    } -AutoRefresh -RefreshInterval 1
}
Start-UDDashboard -Port 10001 -Endpoint $Endpoint -Dashboard $Dashboard
4

Yep. You can create multiple endpoints with different schedules. No restrictions there. These cmdlets also exist in PSU as well so you will be able to do the same thing there.

1 Like