SQL Report Help



  • Joe Williams

    I got it working via help over at stackoverflow.


    Applications.Name 'Application',
    Applications.Version 'Version',
    count(Computers.Name) 'Count',
    Applications.Uninstall 'Uninstall String',
    group_concat(CASE WHEN Computers.CurrentUser = ''
    THEN Computers.Name
    ELSE Computers.Name || ' (' || Computers.CurrentUser || ')'
    ', ') AS 'Computers (Users)'
    FROM Computers inner join Applications on Computers.ComputerID=Applications.ComputerID
    where Applications.Name not in ('7-Zip 16.04 (x64)', 'A360 Desktop', 'Adobe Acrobat DC (2015)', 'etc....')
    and Computers.Name like 'nfd%' and Computers.Name not like 'nfdnt%'
    group by Applications.Name
    Comment actions Permalink
  • Andrew S

    A Basic Report can achieve what you want.

    If you use NOT IN they must be exact matches, same as WHERE <> '7-zip 16.04 (x64)', OR WHERE <> 'A360 Desktop' etc. There may be hundreds or thousands of Drivers and Helper applications that are near impossible to include in mid to large environments.  Also the more "=" clauses added will slow the query. Not as exact but condensing to exclude by a combination of Starts with, Contains, and Equals > Names and Publishers as below may work better:

    Comment actions Permalink
  • Joe Williams

    I know a Basic report can accomplish it, but the UI when attempting to do anything beyond a few lines gets to laggy.

    Also the Basic Report doesn't allow for customization of the report like having all users and computers on the same line with it combined.

    We also are purposefully doing exact so we know if something gets updated either automatically or by the parent company.

    We only run the report once a week so slowness isn't a matter, but the report itself runs in 3ish seconds.

    Comment actions Permalink

Please sign in to leave a comment.