6 Replies Latest reply on Mar 12, 2009 7:22 PM by zman

    Text Length Query

    Apprentice

      I'm trying to build a query that would yield all workstations where the TCP host name is longer than 15 chars. Any thoughts? Also, is there any good documentation for these ad hoc queries?

        • 1. Re: Text Length Query
          Apprentice

          Hi --

           

          I'm not sure whether this can be done in the front end.  If you're interested (and able) to obtain the data from the database directly, this should do it.  The devicename is in the computer table of the LANDesk database:

           

          select len(devicename)as 'Number of Characters',DeviceName as 'Device', DeviceID ,Computer_idn from computer -- where len(devicename)>15

          • 2. Re: Text Length Query
            zman Master

            You can use basic regular expressions as documented here.http://www.droppedpackets.org/reports/use-regular-expressions-in-queries?searchterm=expression

            The expression listed below seems to work in my RE Utility, but not in the query tool. Probably too complex. An alternative is somebody can't rewrite the expression to work with LANDesk is to export to Excel and use the function listed below:

             

            ^.{16,100}$
            
            =IF(LEN(A7)>15)
            
            • 3. Re: Text Length Query
              Apprentice

              I have been doing the Excel thing for too long, too much redundancy. I might just go directly in the database if the UI doesn't allow it.

              • 4. Re: Text Length Query
                zman Master

                Try Computer.Device Name > len()-15

                 

                Seems to work for me with lower chars since I don't have anything over 15. Scratch that I don't think it works..

                • 5. Re: Text Length Query
                  Apprentice

                  Nope, I have actually tried this before. It doesn't filter my records at all, still showing all of them. Just for the record I'm using the Computer, Network, TCPIP, Host Name inventory field

                  • 6. Re: Text Length Query
                    zman Master

                    Well looks like you are probably stuck with SQL - either with straight SQL or sucking in the data directly into Excel from SQL. Maybe the new version of LANDesk will be using the new version of Active Report 3.X

                     

                    In ActiveReports, C# expressions can be used in the DataField property to specify textbox output in a report, such as date/time, mathematical calculations or conditional values. Any field expression should begin with the equals (=) sign.

                    Using Field Expressions

                    To use a mathematical expression

                    Change the DataField property for the text box to the mathematical calculation desired.

                    Examples: =UnitPrice+5

                                    =Quantity-5

                                    =Quantity*UnitPrice

                                    =UnitPrice/QuantityPerUnit

                    To use a substring

                    Change the DataField property for the text box to the substring needed. If setting up grouping, change the GroupHeader's DataField property to the same substring.

                    Example: =ProductName.Substring(0, 1)

                    To use date/time

                    Change the DataField property for the text box to the following.

                    Example: =System.DateTime.Now.ToString()

                    To create a conditional value

                    Change the DataField property for the text box to the conditional statement desired.

                    Example: =(UnitsInStock > 0)?"In Stock":"Backorder"

                    To concatenate fields

                    Change the DataField property for the text box to the following.

                    Examples: ="There are " + UnitsInStock + " units of " + ProductName + " in stock."

                                    =TitleOfCourtesy + " " + FirstName + " " + LastName

                    NOTE: ActiveReports for .NET 3.0 automatically handles null values, replacing them with an empty string.

                    To round a calculation

                    Change the DataField Property for the text box to the following.

                    Example: =(double)System.Math.Round(UnitPrice*UnitsOnOrder,2)

                    To use modular division

                    Change the DataField Property for the text box to the following to get the remainder (2 in this case).

                    Example: =22%(5)

                    To replace a null value

                    Change the DataField Property for the text box to the following to replace nulls with your own value.

                    =(Region == System.DBNull.Value) ? "No region specified" : Region

                    1 of 1 people found this helpful