11 Replies Latest reply on Jul 28, 2017 3:06 PM by Edison Santos

    EXPORT CSV CONCATE

    Edison Santos

      Hi Guys!

      I'm creating an CSV export and I would like to know if is possible concatenate two differents "SRC" fields in just one "DEST", concatenating this dataset (same row).

      Regards!

      ES

        • 1. Re: EXPORT CSV CONCATE
          Shane Smith

          I would try this: In the mapping set, make the query something like   SEARCH Host show name + ' ' + os_type AS "Combined"  then in the mapping below, use the Combined as the src="Combined"

          1 of 1 people found this helpful
          • 2. Re: EXPORT CSV CONCATE
            Shane Smith

            Here is another example:

            SEARCH Host SHOW hostname, #"%s version %s"(os_type, os_version) as os

             

            Note:  #"%s version %s"(os_type, os_version) as os

            3 of 3 people found this helpful
            • 3. Re: EXPORT CSV CONCATE
              Edison Santos

              I tried .... It does not make errors in the query, but it does not show the data together, as was the purpose

               

              QUERY:

               

              search SoftwareInstance

                       where

                                 type has subword 'Database'

                       show

                                 version as 'Full Version',

                                 port as 'Port',

                                 listen_tcp_sockets +''+ port as 'concatenate'

               

               

              RESULTS:

                   

              HostFull VersionPortListen Tcp Socketsconcatenate
              czzlposimmg15.5.553306Not setNot set
              czzlwbleczk15.5.553306Not setNot set
              czzlweswwww15.5.553306Not setNot set
              czzlpgnnwwq18.3.235432Not setNot set
              cqqlodwwwww211.2.0.4.0Not setcqqlodwwwww2:1824Not set
              1 of 1 people found this helpful
              • 4. Re: EXPORT CSV CONCATE
                Edison Santos

                Ops.....

                 

                Almost there.....

                 

                QUERY:

                search SoftwareInstance

                        where

                                  type has subword 'Database'

                        show

                                  #RunningSoftware:HostedSoftware:Host:Host.name as 'Host',

                                  version as 'Full Version',

                                  port as 'Port',

                                  listen_tcp_sockets as 'Listen Tcp Sockets',

                                  listen_tcp_sockets +''+ port as 'concatenate',

                                  #"%s %s"(listen_tcp_sockets,port) as 'conactenate2'

                 

                 

                RESULT:

                 

                But the word "None" is printing together with de valid col..

                 

                None 3306

                      

                HostFull VersionPortListen Tcp Socketsconactenateconactenate2
                czzwwwwrrr15.5.553306Not setNot setNone 3306
                czzrrrwwwd15.5.553306Not setNot setNone 3306
                cqqwwwrrrr15.5.553306Not setNot setNone 3306
                cxxvvvrrrr18.3.235432Not setNot setNone 5432
                czzmnmnrmn1EmptyNot setNot setNot setNone None
                1 of 1 people found this helpful
                • 5. Re: EXPORT CSV CONCATE
                  Andrew Waters

                  You cannot do it like this. port, when present, is a number and listen_tcp_sockets, when present, is a list of strings. Either added to a non-present value fails and you cannot add them together.

                  SEARCH SoftwareInstance

                  WHERE type HAS SUBWORD 'Database'

                  SHOW

                    version as 'Full Version',

                    port as 'Port',

                    (listen_tcp_sockets or port) as 'concatenate'

                  But this will given different kinds of values in concatenate. It really depends upon what behaviour you want if a value is missing or when both are present.

                  2 of 2 people found this helpful
                  • 6. Re: EXPORT CSV CONCATE
                    Edison Santos

                    Andrew Waters and Shane Smit, thanks for yours replies   

                     

                    Is there a way to turn both into CHAR (STRING)?

                    I need to use only one output column.

                    For the query results, if the CI is a listner, the port number is in the listen_tcp_port column, if it is a DB instance, the port number is in the "port" column.

                    My wish is that the single output column should contains the two concatenated information (I will always have one that exists).

                    1 of 1 people found this helpful
                    • 7. Re: EXPORT CSV CONCATE
                      Edison Santos

                      Or, maybe a way to treat this output collumn erasing this undesired word??

                      1 of 1 people found this helpful
                      • 8. Re: EXPORT CSV CONCATE
                        Edison Santos

                        Hi guys. Thank you for your help!!

                        With your help and my persistence, I will solve this!

                         

                        SOLUTION:

                         

                        search SoftwareInstance

                                    where

                                              type has subword 'Database'

                                    show

                                              name as 'Name',

                                              version as 'Full Version',

                                              instance as 'Instance'

                                              processwith

                                              show

                                              name as 'Name',

                                              instance as 'Instance',

                                              version as 'Full Version',

                                              booleanLabel(join(listen_tcp_sockets, '|'),join(listen_tcp_sockets, '|'),'','') + booleanLabel(port,port,'','') as 'PORTS',

                                              #RunningSoftware:HostedSoftware:Host:Host.name as 'Host'

                        1 of 1 people found this helpful
                        • 9. Re: EXPORT CSV CONCATE
                          Andrew Waters

                          How do you expect this to work? You still have a fundamental problem where port is a number not a string so you cannot add it to a string. Not only that but the number of arguments to booleanLabel is wrong. If you really have to do something like this then I suppose you could do

                          SEARCH SoftwareInstance

                          WHERE type HAS SUBWORD 'Database'

                          SHOW

                            name,

                            instance,

                            version as 'Full Version',

                            join((listen_tcp_sockets or []) + booleanLabel(port, list(str(port)), [], []), " | ") AS "PORTS",

                            #RunningSoftware:HostedSoftware:Host:Host.name as 'Host'

                          2 of 2 people found this helpful
                          • 10. Re: EXPORT CSV CONCATE
                            Edison Santos

                            hI Andrew Waters, thanks for your attention and reply!!

                             

                            I understood that your QUERY is more clean than mine, and so, more easy to work and manage....

                            But why did you say that the numbers of arguments from the booleanLabel is wrong?

                            The manual says:  "booleanLabel(value, true_label, false_label, other_label)". Can't I use '' instead [].

                            I'm sorry about these basic questions, but I'm a Control-M specialist and now I recently started to work with Discovery..

                             

                            Best regards

                            ES

                            1 of 1 people found this helpful
                            • 11. Re: EXPORT CSV CONCATE
                              Edison Santos

                              Adding one more question, could anybody tell me how to aggregate the HOST where one database instance searched on DatabaseDetail runs, on the report resulted by my query??

                              1 of 1 people found this helpful