View

Requete SQL sur la base d’OCS Inventory en Perl

Note

Utilisation de INET_ATON de MySQL pour trier sur les IP

  1. #!/usr/bin/perl -w
  2. use strict;
  3. use warnings;
  4. use DBI;
  5. #Connection à la base de donnes OCS
  6. my $dns = ‘dbi:mysql:ocsweb:localhost’;
  7. my $login = ‘root’;
  8. my $passwd = ‘secret’;
  9. my $dbh = DBI->connect($dns, $login, $passwd) or die “Connection impossible: $DBI::errstr\n;
  10. # Recuperation des IP et des MAC  des postes SANS “Anti-virus”
  11. my $query = “SELECT DISTINCT hard.NAME, net.IPADDRESS, net.MACADDR “.
  12.                 “FROM softwares AS soft, hardware AS hard, networks AS net “.
  13.                 “WHERE hard.ID = soft.HARDWARE_ID “.
  14.                   “AND hard.ID = net.HARDWARE_ID “.
  15.                   “AND net.STATUS = ‘up’ “.
  16.                   “AND soft.name NOT LIKE ‘%anti-virus%’ “.
  17.                 “ORDER BY INET_ATON(net.IPADDRESS) “;
  18. my $result = $dbh->prepare($query) or die “Preparation impossible: $DBI::errstr\n;
  19. $result->execute() or die “Execution impossible: $DBI::errstr\n;
  20. #$result->dump_results();
  21. printf (“Il y a %d correspondances:\n,$result->rows);
  22. while ( my ($name, $ip, $mac) = $result->fetchrow_array()){
  23.         print “$name, \t$ip, \t$mac\n;
  24. }
  25. $result->finish();
  26. $dbh->disconnect();

You may also like