Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

SQL Report Help

I am attempting to write a custom SQL report and I am very close. I just can't get the last bit figured out.

Basically, the report is going to end up showing unapproved software Below is my almost working code. I am trying to put all computers and users in 1 field. I can get this working by itself, but just not sure how to group_concat it.properly.

 

select
Applications.Name 'Application',
Applications.Version 'Version',
count(Computers.Name) 'Count',
Applications.Uninstall 'Uninstall String',
case
WHEN Computers.CurrentUser = '' THEN Computers.Name || ', '
ELSE Computers.Name || ' (' || Computers.CurrentUser || '), '
END AS 'Computer/Users'
FROM Computers inner join Applications on Computers.ComputerID=Applications.ComputerID
where Applications.Name LIKE '%McAfee%'
group by Applications.Name


0

Comments

3 comments
Date Votes
  • I got it working via help over at stackoverflow.

     

    select
    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 || ')'
    END,
    ', ') 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
    0
  • 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:

    0
  • 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.

    0