Report query reading content from .txt file

asked 2020-08-27T06:41:56.923+00:00
Alberto Valdes 21 Reputation points

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

No comments
{count} votes

Accepted answer
  1. answered 2020-08-28T02:28:53.62+00:00
    MelissaMa-MSFT 24,116 Reputation points

    Hi @Alberto Valdes ,

    You could have a try with BULK INSERT.

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

    21083-stuff.png

    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


0 additional answers

Sort by: Most helpful