Parse CSV file for data to generate desktop shortcut

[ABoon]

Hi, hoping someone could point me in the right direction to understand if this is possible.

I have 1300+ endpoints, which need shortcuts to IP CCTV cameras placed on the windows desktop, each endpoints shortcuts are unique as they contain the local IP of the CCTV camera, the rest of the shortcut is the same text. They also vary in number per endpoint, some will only have 1 shortcut, others will have multiple.

I was thinking I could create a CSV file containing a list of endpoint id’s and the IP addresses, send that temporarily to the endpoint and then use BigFix to iterate through the file and generate a shortcut for each instance it finds

e.g.

CSV Example

EndpointNumber, IP, Name
0002, 10.0.0.1, Cam1
0002, 10.0.0.2, Cam2
0003, 10.1.0.12, Cam1
0125, 192.168.23.19, Cam1
0125, 192.168.23.24, Cam2
0125, 192.168.23.124, Cam3
2487, 10.11.25.12, Cam1

Shortcut Example

http://#.#.#.#/cam/index.html

So if i run the action to endpoint number 0125, it would go through and generate 3 desktop shortcuts called Cam1, Cam2 and Cam3 with the correct IP address in each shortcut from the CSV file.

I would then delete the CSV from the endpoint at the end of the action script.

I have the EndpointNumber stored in a registry key

I cannot work out how to iterate through the CSV and generate the shortcuts so if someone could help that would be amazing. Thank you in advance.

[brolly33]

Welome back @ABoon

I can help with the reading the CSV part.

q: ("https://something.com/" & item 0 of it & "/someMoreULR/" & item 1 of it & "/TheRestOfTheURL.html") of (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer id as string)) of file "camera_lookup.csv"

The hard part is going to be synthesizing the actual shortcut with a command line. What Windows command are you using to make the shortcut file?

[ABoon]

Many thanks @brolly33

Wow, i’ll have to take some time to understand that

I was thinking I would probably have to use Powershell or mklink to generate the Shortcut if BigFix cannot do it directly.

I’m rubbish at Powershell but something like below???

powershell "$s=(New-Object -COM WScript.Shell).CreateShortcut('%userprofile%\Desktop\A VARIABLE.lnk');$s.TargetPath='SOME VARIABLE';$s.Save()"

Maybe the Variable name and Target Path can be passed by BigFix?

[brolly33]

Yes, you can build the powershell in action script with appendfile command, and your relevance substitution can go into curly braces for the variable parts.

appendfile New-Object ... TargetPath = {relevance that generates the https from above} ....

Work with that for now.
You will probably need another nudge when it comes to dealing with the “more than one shortcut” part of the requirement.

[ABoon]

So single camera totally makes sense, thank you so much

So how do i use the answers as variables for mutiple cameras? In QNA I can mock up so I get 2 responses which would both need to be shortcuts but i don’t see how i can reference them individually or their relevant Name from the CSV file.

[brolly33]

Great progress!

to handle the plurals, we will need to synthesize the entire powershell line directly in relevance instead of just inserting the variables.

use %22 to escape any double quotes in the line.
use {{ to escape any literal { in the line.

Then the relevance handles the plural lines and concatenation “%0d%0a” part puts the line breaks into the final .ps. You will also need to variablize the .lnk file, so it does not simply overwrite.

appendfile {concatenation "%0d%0a" of ("powershell %22$s=(New-Object -COM WScript.Shell).CreateShortcut('%userprofile%\Desktop\A VARIABLE.lnk');$s.TargetPath='" & item 1 & of it "';$s.Save()%22 of (<relevance to make the https links>)}

Then you run the .ps

[DerrickD]

It may be easier to work with JSON than CSV. I am doing something where I am reading from a JSON file. BigFix has native inspectors for that, so you can just do things like:

path (“$.” & (computer name as uppercase) & “.” & “camera_IP”) of json of file “myfile.json” of (client folder of site “CustomSite_BESTSITE”) as string | “N/A”

I upload the JSON to a customsite that distributes it down to all clients using the BigFix REST API.

[atlauren]

Given my data format explorations, I definitely endorse using JSON-formatted data files, distributed to clients via site distribution.

Inspector performance for different data types (CSV, XML, JSON)

Has anyone interrogated inspector performance for the various methods of extracting data from files? I have a usecase in which I will be storing data, distributing it to agents, and then parsing the data via relevance. I have the freedom of choosing the data format. This inspires questions of, of all of BigFix relevance’s data munging inspectors, which are notably faster or slower than others? Of the structured data inspectors, there are: ini section json plist dictionary sqlite database xm…

[ABoon]

@DerrickD @atlauren Thank you, I have no experience in JSON at all but will look into this as an option for moving forward given the performance benefits in this area.

@brolly33 Many thanks i will start to play with this and see if i can get the multiples working

[ABoon]

@brolly33 @Aram Hope you can help, i’m getting totally stuck with the plurals

I have my Powershell working fine when i test it locally on the machine obviously replacing the variables with values.

I have BigFix working for the single camera scenario with the variables with no issues.

When I add in the appendfile {concatenation “%0d%0a” of (powershell ****** )} then the action just fails at this stage. I’ve tried making all the curly brackets double ones but that didn’t seem to help either

This is the script below;

appendfile {concatenation “%0d%0a” of (powershell $wshShell = New-Object -ComObject “WScript.Shell”;$urlShortcut = $wshShell.CreateShortcut((Join-Path $wshShell.SpecialFolders.Item(“AllUsersDesktop”) “{(item 1 of it) of (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer name as string)) of file “c:\ab\camera.csv”}”));$urlShortcut.TargetPath = ‘{(“http://” & item 0 of it & “/cam/index.html”) of (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer name as string)) of file “c:\ab\camera.csv”}’;$urlShortcut.Save())}

[brolly33]

Good progress. One ask: please enclose your code inside of the code formatting markup in the toolbar it looks like </> otherwise your quotes turn to smart quotes (and other entertaining encoding junk happens.

Seems you are missing some double quotes in your appendfile…

Ah - and you tried to nest {relevance {more relevance}} instead of concatenating. Also, you will need to calculate item 0 and item 1 “at same time”, so downstream of your substituting into the larger string.

start like this:

q: concatenation "%0a%0d" of ("A" & item 1 of it & "B" & item 0 of it & "C") of  (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer name as string)) of file "c:\ab\camera.csv"

Then change the A B and C parts to be the rest of the powershell line you need.

[ABoon]

Thank you, sorry about not enclosing the code. I really appreciate the help. Hopefully I can get it to work now

[brolly33]

Something like this (although I think I messed up some of the details in the powershell…)

q: concatenation "%0a%0d" of ("powershell $wshShell = New-Object -ComObject %22WScript.Shell%22;$urlShortcut = $wshShell.CreateShortcut((Join-Path $wshShell.SpecialFolders.Item(%22AllUsersDesktop%22) %22" & item 1 of it & "%22 ));$urlShortcut.TargetPath = (%22http://" & item 0 of it & "/cam/index.html%22;$urlShortcut.Save())") of  (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer name as string)) of file "c:\ab\camera.csv"

Run it in your relevance debugger until you get two (or more) crisp and perfect lines output. Then slip it into the appendfile {}

[ABoon]

Amazing, will do, thank you.

[ABoon]

YES!!! That’s done it. Oh my goodness, thank you so much.

appendfile {concatenation "%0a%0d" of ("$wshShell = New-Object -ComObject %22WScript.Shell%22;$urlShortcut = $wshShell.CreateShortcut((Join-Path $wshShell.SpecialFolders.Item(%22AllUsersDesktop%22) %22" & item 1 of it & "%22 ));$urlShortcut.TargetPath = (%22http://" & item 0 of it & "/cam/index.html%22);$urlShortcut.Save()") of  (tuple string items 1 of it, tuple string items 2 of it) of lines whose (tuple string item 0 of it = (computer name as string)) of file "c:\ab\camera.csv"}

[brolly33]

@DerrickD

ork with JSON than CSV. I am doing something where I am reading from a JSON file. BigFix has native inspectors for that, so you can just

Thanks for sharing back your completed code. It’s very helpful for others that will come in the future!