Report query reading content from .txt file

Question

Hello everyone.

Our company has a list of software which employees can install without previous authorization (Allowed Software)

I was asked to create a report listing all the not allowed software installed in the company computers.

Let's say our computer names are in the format COMPUTERNAME0001 and the allowed software list has 3 entries (Skype for Business, Beyond Compare, Notepad++)

I have the following query :

Select DISTINCT
SYS.Netbios_Name0 as "Computer Name",
AR.Publisher0 as 'Vendor',
AR.DisplayName0 AS 'Software name',
AR.Version0 as 'Software Version'

FROM
v_GS_ADD_REMOVE_PROGRAMS AR

JOIN
v_R_System SYS ON AR.ResourceID=SYS.ResourceID
JOIN
v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE
sys.Name0 LIKE 'COMPUTERNAME%'

AND
DisplayName0 NOT LIKE 'Beyond Compare'
AND
DisplayName0 NOT LIKE '%Notepad++%'
AND
DisplayName0 NOT LIKE '%Skype for Business%'
AND
DisplayName0 NOT LIKE ''

ORDER By
SYS.Netbios_Name0, AR.DisplayName0, AR.Publisher0, AR.Version0

That works, I get a report with the info I need.

Now they asked me if instead of having to edit the query every time we add or remove a software from the list, I can configure it, so the query reads a .txt file containing
the list of allowed software.

And I have no idea how to do it.

Is it possible?

Best regards

Alberto

Answer 1

Hi @Alberto Valdes ,

You could have a try with BULK INSERT.

Firstly you could create one txt file like below on C drive.

Then you could refer the query in attached txt file since I still face some issues when I tried to paste the code here.
21017-bulk-insert.txt

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa