SQL excluding something from a list

glenimp617 · 1 Dec 2022 at 12:37

I have a SQL query which returns all installed software across our network. Like this:

Adobe Lightroom
AMD Driver v2.2.1
Microsoft Office 2020
Nvidia CC

It's 3,330 lines and I want to start building up a list of things to exclude (like drivers, and stuff we don't want).

Rather than having a query like this

SELECT * FROM TABLE WHERE 'Title' NOT LIKE 'AMD Driver%' AND 'Title' NOT LIKE 'Nvidia%' AND.......AND......AND

Can I make the query a bit more elegant and reference perhaps a second table with everything we don't want?

Thanks!!

gpuerrilla · 1 Dec 2022 at 12:59

Lump all the apps in a table.

Refer to the Table with a WHERE NOT IN

glenimp617 · 1 Dec 2022 at 13:17

is it that simple :cry:

glenimp617 · 1 Dec 2022 at 15:42

Right, got it working great thanks

Next little problem to solve...... the table looks like the top part of this image, but I'd like it to look like the bottom part

skyripper · 1 Dec 2022 at 18:40

That's effectively a pivot table you're asking for.
There are ways to create them but not with a single SQL statement. So it depends what platform you're on as to what you can use.

glenimp617 · 1 Dec 2022 at 18:52

skyripper said:
That's effectively a pivot table you're asking for.
There are ways to create them but not with a single SQL statement. So it depends what platform you're on as to what you can use.

ok, thanks for the heads up. I might be able to do it in powershell.

gpuerrilla · 1 Dec 2022 at 18:52

You can use some unique element to it to return less results. Are you outputting this to excel ?

glenimp617 · 1 Dec 2022 at 18:58

gpuerrilla said:
You can use some unique element to it to return less results. Are you outputting this to excel ?

Yeah it's going into excel

gpuerrilla · 1 Dec 2022 at 19:13

OK dump it as it is. Then use Power Query to load it to the data model. Easy pivots from there.

touch · 2 Dec 2022 at 11:15

It can be done in SQL but it's an ugly query:

Code:

/* CREATE THE TABLE */
CREATE TABLE [dbo].[Application](
    [DisplayName0] [varchar](100) NULL,
    [Version0] [varchar](20) NULL,
    [Publisher0] [varchar](100) NULL,
    [Name0] [varchar](100) NULL
)

/* INSERT THE VALUES */
INSERT INTO Application(DisplayName0,Version0,Publisher0,Name0)
VALUES('ACTEnterprise 2.15.0.19','2.15.0.19','Access Control Technology','Computer1'),
('ACTEnterprise 2.15.0.19','2.15.0.19','Access Control Technology','Computer2'),
('ACTEnterprise 2.15.0.19','2.15.0.19','Access Control Technology','Computer3'),
('ACTEnterprise 2.15.0.19','2.15.0.19','Access Control Technology','Computer4'),
('CutePDF Writer 3.2','3.2','Acro Software Inc.','Computer1'),
('CutePDF Writer 3.2','3.2','Acro Software Inc.','Computer2'),
('CutePDF Writer 3.2','3.2','Acro Software Inc.','Computer3'),
('CutePDF Writer 3.2','3.2','Acro Software Inc.','Computer4')

/* SELECT THE DATA */
SELECT DisplayName0,Version0,Publisher0, STUFF(
(SELECT ',' + a2.Name0
FROM [Application] a2
WHERE a1.DisplayName0 = a2.DisplayName0
FOR XML PATH('')),1,1,'') AS Name0
FROM [Application] a1
GROUP BY DisplayName0,Version0,Publisher0

tom_e · 2 Dec 2022 at 11:20

SQL pivots are disgusting, definitely something to be done in your front end whenever possible.

glenimp617 · 2 Dec 2022 at 12:50

Thanks everyone!!

I cheated in the end. Once I got the data right in excel, I gave it to our data science dept who knocked this up in about 5mins

gpuerrilla · 2 Dec 2022 at 13:16

Power BI by look of it.

skyripper · 2 Dec 2022 at 16:23

That's not cheating, that's using the right tool for the job

gpuerrilla · 2 Dec 2022 at 16:25

If you had a whole team to ask in the first place lol. :rolleyes:

glenimp617 · 2 Dec 2022 at 16:53

gpuerrilla said:
If you had a whole team to ask in the first place lol.

We'll they wouldn't have done all the SQL queries from SCCM would they, they aren't the IT dept....and I enjoy learning new things. The first question helped me no end, and I can use that for other little projects I'm working on

gpuerrilla · 2 Dec 2022 at 17:03

TheOracle said:
We'll they wouldn't have done all the SQL queries from SCCM would they, they aren't the IT dept....and I enjoy learning new things. The first question helped me no end, and I can use that for other little projects I'm working on

Was a bit of sarc jesting bud, glad we could help. I like to learn this stuff too!

glenimp617 · 2 Dec 2022 at 17:14

gpuerrilla said:
Was a bit of sarc jesting bud, glad we could help. I like to learn this stuff too!

don't worry, I've added you to my list of oracles

I'll no doubt need some more help soon :cry:

gpuerrilla · 2 Dec 2022 at 17:25

TheOracle said:
don't worry, I've added you to my list of oracles I'll no doubt need some more help soon

Your welcome buddy!

I taught myself power query/pivot as we dont have a source to ask for help so I am in between back end query and nowadays design reports too. I am hoping we get proper BI implementation sometime so I can use it for visuals like you shown above.

OspreyO · 12 Feb 2023 at 23:47

You could create a table with a list of what are apps and what are drivers. Then join on that with your first query.

I would want to cut excel out of the loop on this.
Especially if it going to be repeated regularly or at least annually.

But if you got want you wanted I guess job done.