Home > Database, Deep Dives > LANDesk Query Builder

LANDesk Query Builder

February 17th, 2011 Leave a comment Go to comments

Originally posted by: Dave Zubrowski

LDMSQB is a small utility (written in AutoitScript) that takes a text file of either computer/machine or user/logon names and creates a LANDesk query. Currently there is no efficient way to take a list of machine/user names and create a LANDesk query. The script is based on Jan Chojnacki’s original script (hard to find). I found in version 8.8 that the original script would not import the last line of the query. The script was pretty much rewritten to expand on the great work Jan completed in the original script.

Some enhancements – better error handling, fully documented, whitespace and blank line removal of the input file, GUI stays up to handle multiple query generation, imports last line of query, overwrite warning for existing queries,  etc… I have left the column names to a minimal. My feelings are that it is very easy to add additional columns in LANDesk.
There are bound to be issues with this utility, and I offer no support (see disclaimer – readme.txt).  However, if you find a bug or have a suggestion, please let me know.  This utility has only been tested in LANDesk version 8.8

Enjoy…

Version 1.2 (6-15-09)
Version 1.2 (6-15-09) – Like parameter added – Mike Compton
Properly close all files out
Added Login Name to query col.

Download ldmszqb.zip

Categories: Database, Deep Dives Tags:
  1. jpierson
    November 17th, 2011 at 15:45 | #1

    We wrote something similar in HTA, code below.

    ********************

    CHOP LANDesk Query Builder

    Sub Window_onLoad
    window.resizeTo 550,540
    End Sub

    Sub CreateReport_onClick
    Dim bCreate, i, j, arrOptions, strValues, ext
    If Document.frmImport.QueryName.value = “” then
    MsgBox “Please enter Query Name”,0,”Missing Data”
    exit sub
    end if
    If Document.frmImport.QueryType.selectedIndex = 0 then
    MsgBox “Please enter Query Type”,0,”Missing Data”
    exit sub
    end if
    If Document.frmImport.ImportList.value = “” then
    MsgBox “Please select a list to import”,0,”Missing Data”
    exit sub
    end If

    ”loop through the checkboxes
    For i=1 to 23
    inField= “opt” & i

    ‘check required fields for blanks
    if document.frmImport.elements(inField).Checked then
    if Trim(strValues)=”” then
    strValues=Document.forms(0).elements(inField).value
    else
    strValues=strValues & “,” & Document.forms(0).elements(inField).value
    end if
    end if
    Next

    arrOptions=Split(strValues,”,”)

    bCreate=CreateXML(document.frmImport.QueryName.value,document.frmImport.QueryType.value,document.frmImport.ImportList.value,arrOptions)
    if bCreate then
    MsgBox(“XML complete”)
    else
    MsgBox(“Could not create XML”)
    end if
    End Sub

    Sub ExitBtn_onClick
    self.close()
    End Sub

    Function ReadDataFromExcel(sFileNameAndPath)
    Dim conn,rs,sSheetName
    Dim sSQL,vRows, strtext, strValue
    Set conn = CreateObject(“ADODB.Connection”)

    Call conn.Open(“Driver={Microsoft Excel Driver (*.xls)};” & _
    “DriverId=790;” & _
    “Dbq=” & sFileNameAndPath & “;”)
    Set rs = conn.OpenSchema(20)
    if not rs.eof then
    sSheetName = rs(“Table_Name”)
    end if
    rs.Close
    Set conn = nothing
    Set conn = CreateObject(“ADODB.Connection”)
    With conn
    .Provider = “Microsoft.Jet.OLEDB.4.0”
    .ConnectionString = “Data Source=” & sFileNameAndPath & “;” & _
    “Extended Properties=Excel 8.0;”
    .Open
    End With

    Set rs = CreateObject(“ADODB.Recordset”)
    sSQL = “SELECT * FROM [” & sSheetName & “]”

    rs.open sSQL, conn

    i=0
    do while not rs.eof
    for each field in rs.fields
    strValue=field.value

    if Trim(strValue) “” then
    if Trim(strtext)=”” then
    strtext=strtext & strValue
    else
    strtext=strtext & ” ” & strValue
    end if
    end if
    next
    i=i+1
    rs.moveNext()
    loop

    rs.close
    Set rs = nothing
    conn.close
    Set conn = nothing
    vRows=Split(strtext,vbTab)
    ReadDataFromExcel=vRows
    End Function

    Function ReadDataFromFile(sFileNameAndPath)
    Dim objFSO,i,sContents,vRows
    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    sContents = getFileContents(sFileNameAndPath, objFSO)

    vRows = split(sContents, vbCrLf)

    ReadDataFromFile=vRows
    End Function

    Function getFileContents(sFileNameAndPath, objFSO)
    Const ForReading = 1, ForWriting = 2
    Dim objFile
    Set objFile = objFSO.OpenTextFile(sFileNameAndPath, ForReading)
    getFileContents = objFile.ReadAll
    Set objFile = nothing
    End Function

    Function CreateXML(queryname, querytype, importPath, arr)
    Dim strheader, strxml, filepath, i, j, strwhere,vRows,ext, temp, arrTemp, strTemp
    ext=LCase(Right(importPath,3))

    Select Case ext
    Case “txt”, “csv”
    vRows=ReadDataFromFile(importPath)
    Case “xls”
    vRows=ReadDataFromExcel(importPath)
    Case else
    MsgBox(“Invalid file!”)
    CreateXML=false
    Exit Function
    End Select

    filepath=Replace(document.location.pathname,”QueryBuilder.hta”,””)
    filepath=Replace(filepath, “%20″, ” “)
    filepath=filepath & queryname & “.xml”

    strheader= “”
    strxml=”” & vbCrLf
    strxml = strxml & “” & vbCrLf
    strxml = strxml & “” & vbCrLf

    strwhere = “” & vbCrLf

    strxml = strxml & strwhere

    ”retrieve all the options
    for j=0 to ubound(arr)-1
    arrTemp=Split(arr(j),”.”)
    strxml = strxml & “” & vbCrLf
    next

    strxml = strxml & “” & vbCrLf

    for j=0 to ubound(arr)-1
    strxml = strxml & “” & vbCrLf
    next

    strxml = strxml & “” & vbCrLf
    strxml = strxml & “”

    strxml= strheader & vbCrLf & strxml

    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    ‘open file for writing, create if not there, and open as text
    Set objFile = objFSO.CreateTextFile(filepath, True, False)
    objFile.Write(strxml)
    CreateXML=true
    End Function

    Query Name

    Query Type

    Device Name
    <!–Primary User Like–>
    MAC Address

    Select fields to be imported

    Device Name

    Login Name

    Model

    Domain Name

    Physical Address

    IP Address

    Workgroup

    Image

    Building Name

    Data Entry Date

    Data Jack Number

    Department

    Floor

    Floor Plan Sector

    Monitor Type

    Other Building

    Other Department

    Other Monitor Type

    Phone Extension

    Proximity Room Number

    Shared/Secured

    Sub Department

    OS Type

    Import List 

  1. October 10th, 2014 at 21:08 | #1
  2. October 31st, 2014 at 14:56 | #2
  3. November 13th, 2014 at 13:31 | #3
You must be logged in to post a comment.