• Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login
  • Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login

MYSQL Query Add Hosts to Group

Scheduled Pinned Locked Moved Solved
FOG Problems
3
8
696
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • U
    UWPVIOLATOR
    last edited by Nov 8, 2019, 9:27 PM

    So was working with my SQL admin and got this narrowed down to the following. Yet I am still getting a error on my insert statement. What are we missing? 212 is the group ID. Those values I am querying are the asset tags of our machines but would also like to query the whole host names if possible but from what I heard I cant do a in and like statement. My select statement works perfectly.

    Insert Into groupMembers (gmHostID, gmGroupID) 
    SELECT hostID,212 FROM `hosts` 
    
    WHERE Right(hosts.hostName,6) in ('D02595','110563','110687','110570','110592','111045','111050','111064','111071','96643','D02320','111921','111914','111916','110076','-98264','103070','111846','111842','111840','111459','111470','111471','111463','111437','103636','103638','103639','-97871')
    
    T 1 Reply Last reply Nov 8, 2019, 9:33 PM Reply Quote 0
    • U
      UWPVIOLATOR
      last edited by Nov 9, 2019, 2:06 AM

      @Tom-Elliott Thank you for figure this out. Here is the correct query.

      Insert into groupMembers (gmHostID,gmGroupID) Select hostID,'212' from hosts where Right(hostName,6) in ('D02595','110563')
      
      1 Reply Last reply Reply Quote 1
      • T
        Tom Elliott @UWPVIOLATOR
        last edited by Nov 8, 2019, 9:33 PM

        @UWPVIOLATOR wrap the select statement in ( )

        Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

        Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

        Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

        1 Reply Last reply Reply Quote 0
        • U
          UWPVIOLATOR
          last edited by Nov 8, 2019, 11:14 PM

          Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ’ VALUES (‘hostID’,‘212’) SELECT hostID,212 FROM hosts WHERE Right(hosts.hos’ at line 1
          
          1 Reply Last reply Reply Quote 0
          • U
            UWPVIOLATOR
            last edited by Nov 8, 2019, 11:51 PM

            Ok so it has to be the gmGroupID. It doesnt like me just plain texting it. I am able to run this

            INSERT INTO groupMembers
            (gmHostID, gmGroupID)
            VALUES
            (8928, 212)
            
            1 Reply Last reply Reply Quote 0
            • D
              Daniel Miller
              last edited by Nov 9, 2019, 12:10 AM

              There are two distinct statement syntaxes you are dealing with here. INSERT INTO ... VALUES... is for inserting a single row into a table. INSERT INTO .... SELECT... is for copying data from a table into another table. See INSERT - MariaDB Knowledge Base. The error you posted has both VALUES and SELECT keywords indicated. Did the query that produced that error have both keywords?

              U 1 Reply Last reply Nov 9, 2019, 1:50 AM Reply Quote 0
              • U
                UWPVIOLATOR @Daniel Miller
                last edited by Nov 9, 2019, 1:50 AM

                @Daniel-Miller No, I typed in 212. I could get it from another table with another where statement. I think that is the issue.

                T 1 Reply Last reply Nov 9, 2019, 1:59 AM Reply Quote 0
                • T
                  Tom Elliott @UWPVIOLATOR
                  last edited by Tom Elliott Nov 8, 2019, 8:00 PM Nov 9, 2019, 1:59 AM

                  @UWPVIOLATOR

                  Insert Into groupMembers (gmHostID, gmGroupID) SELECT hostID,'212' FROM `hosts` WHERE Right(hostName,6) in ('D02595','110563','110687','110570','110592','111045','111050','111064','111071','96643','D02320','111921','111914','111916','110076','-98264','103070','111846','111842','111840','111459','111470','111471','111463','111437','103636','103638','103639','-97871')
                  

                  I wonder if the space between the select and the where for the insert is playing into it. Maybe the code above on one line will work better?

                  Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

                  Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                  Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                  1 Reply Last reply Reply Quote 0
                  • U
                    UWPVIOLATOR
                    last edited by Nov 9, 2019, 2:06 AM

                    @Tom-Elliott Thank you for figure this out. Here is the correct query.

                    Insert into groupMembers (gmHostID,gmGroupID) Select hostID,'212' from hosts where Right(hostName,6) in ('D02595','110563')
                    
                    1 Reply Last reply Reply Quote 1
                    • 1 / 1
                    1 / 1
                    • First post
                      6/8
                      Last post

                    202

                    Online

                    12.1k

                    Users

                    17.3k

                    Topics

                    155.3k

                    Posts
                    Copyright © 2012-2024 FOG Project