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
I got it working via help over at stackoverflow.
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:
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.