Asset management/replacement

[Josip]

Hey



does anyone know of a way to reference 2 devices(computers) and compare softwares and stating the rule

that would enable automatic deployment of programs in question?/




We are now in a process of replacing 200 old computers with the new ones ,but each user wants to have softwares specific to their tasks pre installed on new computers.

As you can imagine it's a tedious task of comparing old and new comp and installing those software.

I am looking for a way to automate that to some level.


Any suggestions?


This here helps but doesn't get me all the way.

Code:
Select Top 1000000 tblSoftwareUni.softwareName,
tNotInstalled.AssetID,
tNotInstalled.AssetName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Left Join (Select tblSoftwareUni.SoftID,
tblAssets.AssetID,
tblAssets.AssetName
From tblSoftwareUni,
tblAssets
Inner Join tblComputersystem
On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Assettype = -1 And tblComputersystem.Domainrole < 2 And
Not Exists(Select tblSoftware.softID,
tblSoftware.AssetID From tblSoftware
Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
tblSoftware.softID
Where tblAssets.AssetName = 'AssetName'
Order By tblSoftwareUni.softwareName,
tNotInstalled.AssetName

[RCorbeil]

We are not allowed to interface outside lansweeper

Is there anything stopping you exporting from LANSweeper and massaging the results elsewhere? MS Access isn't the most elegant thing to work with, but it can be made to do the job, for example.

Create a table to mimic the #PCPairs temporary table.

Table: PCPairs

• PairNo: AutoNumber
  
• oldAssetName: Text(255)
  
• newAssetName: Text(255)

Populate it with the names of your old/new PC pairs.

Run a report from LANSweeper for your old and new PCs. Include tblAssets.AssetName, tblSoftwareUni.SoftwarePublisher, tblSoftwareUni.SoftwareName, tblSoftware.SoftwareVersion. Filter out the fluff you're not interested in seeing compared. Export it to CSV or Excel or whatever you want to work with. Import that into your Access database.

Table: PCSoftware

• AssetName: Text(255)
  
• SoftwarePublisher: Text(255)
  
• SoftwareName: Text(255)
  
• SoftwareVersion: Text(255)

MS Access doesn't support FULL JOIN, so you'll have to fake that.

Create a query:

SELECT *
FROM
  ( SELECT
      oldPC.PairNo,
      oldPC.oldAssetName,
      oldPC.SoftwarePublisher AS oldSoftwarePublisher,
      oldPC.SoftwareName AS oldSoftwareName,
      oldPC.SoftwareVersion AS oldSoftwareVersion,
      newPC.newAssetName,
      newPC.SoftwarePublisher AS newSoftwarePublisher,
      newPC.SoftwareName AS newSoftwareName,
      newPC.SoftwareVersion aS newSoftwareVersion
    FROM
      ( SELECT
          PCPairs.PairNo,
          PCPairs.oldAssetName,
          PCSoftware.SoftwarePublisher,
          PCSoftware.SoftwareName,
          PCSoftware.SoftwareVersion
        FROM
          PCPairs
          LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.oldAssetName) AS oldPC
      LEFT JOIN 
        ( SELECT
            PCPairs.PairNo,
            PCPairs.newAssetName,
            PCSoftware.SoftwarePublisher,
            PCSoftware.SoftwareName,
            PCSoftware.SoftwareVersion
          FROM
            PCPairs
            LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.newAssetName
        ) AS newPC ON newPC.PairNo = oldPC.PairNo
                      AND newPC.SoftwarePublisher = oldPC.SoftwarePublisher
                      AND newPC.SoftwareName = oldPC.SoftwareName

    UNION ALL

    ( SELECT
        newPC.PairNo,
        oldPC.oldAssetName,
        oldPC.SoftwarePublisher AS oldSoftwarePublisher,
        oldPC.SoftwareName AS oldSoftwareName,
        oldPC.SoftwareVersion AS oldSoftwareVersion,
        newPC.newAssetName,
        newPC.SoftwarePublisher AS newSoftwarePublisher,
        newPC.SoftwareName AS newSoftwareName,
        newPC.SoftwareVersion aS newSoftwareVersion
      FROM
      ( SELECT
          PCPairs.PairNo,
          PCPairs.oldAssetName,
          PCSoftware.SoftwarePublisher,
          PCSoftware.SoftwareName,
          PCSoftware.SoftwareVersion
        FROM
          PCPairs
          LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.oldAssetName
      ) AS oldPC
      RIGHT JOIN 
        ( SELECT
            PCPairs.PairNo,
            PCPairs.newAssetName,
            PCSoftware.SoftwarePublisher,
            PCSoftware.SoftwareName,
            PCSoftware.SoftwareVersion
          FROM
            PCPairs
            LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.newAssetName
        ) AS newPC ON newPC.PairNo = oldPC.PairNo
                      AND newPC.SoftwarePublisher = oldPC.SoftwarePublisher
                      AND newPC.SoftwareName = oldPC.SoftwareName
        WHERE
          oldPC.oldAssetName IS NULL
    )
) AS FakeFullJoin
ORDER BY
  PairNo,
  IIF(oldSoftwarePublisher IS NULL, newSoftwarePublisher, oldSoftwarePublisher),
  IIF(oldSoftwareName IS NULL, newSoftwareName, oldSoftwareName);

[Josip]

RC62N wrote:

We are not allowed to interface outside lansweeper

Is there anything stopping you exporting from LANSweeper and massaging the results elsewhere? MS Access isn't the most elegant thing to work with, but it can be made to do the job, for example.

Create a table to mimic the #PCPairs temporary table.

Table: PCPairs

• PairNo: AutoNumber
  
• oldAssetName: Text(255)
  
• newAssetName: Text(255)

Populate it with the names of your old/new PC pairs.

Run a report from LANSweeper for your old and new PCs. Include tblAssets.AssetName, tblSoftwareUni.SoftwarePublisher, tblSoftwareUni.SoftwareName, tblSoftware.SoftwareVersion. Filter out the fluff you're not interested in seeing compared. Export it to CSV or Excel or whatever you want to work with. Import that into your Access database.

Table: PCSoftware

• AssetName: Text(255)
  
• SoftwarePublisher: Text(255)
  
• SoftwareName: Text(255)
  
• SoftwareVersion: Text(255)

MS Access doesn't support FULL JOIN, so you'll have to fake that.

Create a query:

SELECT *
FROM
  ( SELECT
      oldPC.PairNo,
      oldPC.oldAssetName,
      oldPC.SoftwarePublisher AS oldSoftwarePublisher,
      oldPC.SoftwareName AS oldSoftwareName,
      oldPC.SoftwareVersion AS oldSoftwareVersion,
      newPC.newAssetName,
      newPC.SoftwarePublisher AS newSoftwarePublisher,
      newPC.SoftwareName AS newSoftwareName,
      newPC.SoftwareVersion aS newSoftwareVersion
    FROM
      ( SELECT
          PCPairs.PairNo,
          PCPairs.oldAssetName,
          PCSoftware.SoftwarePublisher,
          PCSoftware.SoftwareName,
          PCSoftware.SoftwareVersion
        FROM
          PCPairs
          LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.oldAssetName) AS oldPC
      LEFT JOIN 
        ( SELECT
            PCPairs.PairNo,
            PCPairs.newAssetName,
            PCSoftware.SoftwarePublisher,
            PCSoftware.SoftwareName,
            PCSoftware.SoftwareVersion
          FROM
            PCPairs
            LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.newAssetName
        ) AS newPC ON newPC.PairNo = oldPC.PairNo
                      AND newPC.SoftwarePublisher = oldPC.SoftwarePublisher
                      AND newPC.SoftwareName = oldPC.SoftwareName

    UNION ALL

    ( SELECT
        newPC.PairNo,
        oldPC.oldAssetName,
        oldPC.SoftwarePublisher AS oldSoftwarePublisher,
        oldPC.SoftwareName AS oldSoftwareName,
        oldPC.SoftwareVersion AS oldSoftwareVersion,
        newPC.newAssetName,
        newPC.SoftwarePublisher AS newSoftwarePublisher,
        newPC.SoftwareName AS newSoftwareName,
        newPC.SoftwareVersion aS newSoftwareVersion
      FROM
      ( SELECT
          PCPairs.PairNo,
          PCPairs.oldAssetName,
          PCSoftware.SoftwarePublisher,
          PCSoftware.SoftwareName,
          PCSoftware.SoftwareVersion
        FROM
          PCPairs
          LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.oldAssetName
      ) AS oldPC
      RIGHT JOIN 
        ( SELECT
            PCPairs.PairNo,
            PCPairs.newAssetName,
            PCSoftware.SoftwarePublisher,
            PCSoftware.SoftwareName,
            PCSoftware.SoftwareVersion
          FROM
            PCPairs
            LEFT JOIN PCSoftware ON PCSoftware.AssetName = PCPairs.newAssetName
        ) AS newPC ON newPC.PairNo = oldPC.PairNo
                      AND newPC.SoftwarePublisher = oldPC.SoftwarePublisher
                      AND newPC.SoftwareName = oldPC.SoftwareName
        WHERE
          oldPC.oldAssetName IS NULL
    )
) AS FakeFullJoin
ORDER BY
  PairNo,
  IIF(oldSoftwarePublisher IS NULL, newSoftwarePublisher, oldSoftwarePublisher),
  IIF(oldSoftwareName IS NULL, newSoftwareName, oldSoftwareName);

Thank you mate for trying to help and going to such lenghts, but i am trying to find a way to completely automate that with Lansweeper.


I am presuming that something like that is not yet possible.
Just started to use LS few months ago and am still trying it out.

Its good to know that there is an active community that is ready to help people in need.

[Josip]

This is awsome , would solve most of my problems , but 😄 We are not allowed to interface outside lansweeper

[RCorbeil]

I can't speak to automation, but if you're wanting to do a side-by-side comparison of software on the old and new machines and you're willing to work outside the LANSweeper web-based interface (e.g. fire up SQL Server Management Studio)...

CREATE Table #PCPairs
  ( PairNo     Int NOT NULL IDENTITY,
    oldAssetID Int NOT NULL,
    newAssetID Int NOT NULL
  )

/*
   Run yourself a list of assets to pull the AssetID values of the old and new machines.
*/
INSERT INTO #PCPairs (oldAssetID, newAssetID)
VALUES
  (1419, 15829),
  (1411, 10871)  -- etc.


SELECT
  IsNull(oldSoftware.PairNo, newSoftware.PairNo) AS PairNo,
  oldSoftware.AssetID,
  oldSoftware.AssetName,
  oldSoftware.SoftwarePublisher,
  oldSoftware.SoftwareName,
  oldSoftware.SoftwareVersion,
  newSoftware.AssetID,
  newSoftware.AssetName,
  newSoftware.SoftwarePublisher,
  newSoftware.SoftwareName,
  newSoftware.SoftwareVersion,
  CASE
    WHEN oldSoftware.AssetID IS NULL OR newSoftware.AssetID IS NULL THEN '-'
    WHEN oldSoftware.SoftwareVersion = newSoftware.SoftwareVersion THEN 'same'
    WHEN oldSoftware.SoftwareVersion > newSoftware.SoftwareVersion THEN 'OLD > new'
    WHEN oldSoftware.SoftwareVersion < newSoftware.SoftwareVersion THEN 'NEW > old'
    ELSE '??'  -- shouldn't happen, but cover all bases
  END AS VersionDifference
FROM
  ( SELECT
      #PCPairs.PairNo,
      tblAssets.AssetID,
      tblAssets.AssetName,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.SoftwareName,
      tblSoftware.SoftwareVersion
    FROM
      #PCPairs
      INNER JOIN tblAssets ON tblAssets.AssetID = #PCPairs.oldAssetID
      INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
      INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID) AS oldSoftware
  FULL JOIN ( SELECT
                #PCPairs.PairNo,
                tblAssets.AssetID,
                tblAssets.AssetName,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftwareUni.SoftwareName,
                tblSoftware.SoftwareVersion
              FROM
                #PCPairs
                INNER JOIN tblAssets ON tblAssets.AssetID = #PCPairs.newAssetID
                INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID) AS newSoftware
    ON newSoftware.PairNo = oldSoftware.PairNo
       AND newSoftware.SoftwarePublisher = oldSoftware.SoftwarePublisher
       AND newSoftware.SoftwareName = oldSoftware.SoftwareName

ORDER BY
  IsNull(oldSoftware.PairNo, newSoftware.PairNo),
  IsNull(oldSoftware.SoftwarePublisher, newSoftware.SoftwarePublisher),
  IsNull(oldSoftware.SoftwareName, newSoftware.SoftwareName)

DROP Table #PCPairs

Add filters to the software retrievals to ignore fluff you don't care about.

[Josip]

Thx Brandon, but I alredy have that.



Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.Installdate,
tblAssets.Lastseen,
tblAssets.Username,
tblAssets.IPAddress
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblSoftwareUni.softwareName Like 'Adobe Acrobat Reader DC%' And
tblSoftware.softwareVersion < '20.006.20034' And
tblSoftware.softwareVersion Not Like '20.006.20034' And
tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
tblSoftwareUni.softwareName





What I need is a script that compares two computers and gives me a list of softwares that don't exist on the other computer.

Would be even better if that could be automatised and that I can use that scrypt in package deployment.

If you understand what I mean 😄

[brandon_jones]

The first thing that you need to do is create a report that finds the software that you want to update. Here is the report that I use for Adobe Acrobat Reader DC.
To create the report, hover over reports and click new report. It may take a little while for the page to load. Go ahead and type what you want to name the report in the name field.

Delete what is in the bottom section and replace it with the text below.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftware.Lastchanged,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblSoftwareUni.softwareName Like 'Adobe Acrobat Reader DC' And
tblSoftware.softwareVersion < '21.001.20138'
Order By tblAssets.Domain,
tblAssets.AssetName,
Software

You will to customize the information in the where clause with your info. Take out Adobe Acrobat Reader DC and replace it with the name of your software. Also take out the software version and replace it with the software version of the version that your software needs to be at.. Save the report and run it. Look over the results and make sure they look correct.

Next you need to create a deployment to install your software. The way you do this will depend on the software. You will need to know how to install the software silently with no user interaction.

Before you create the deployment you will need to copy your software installer to your Lansweeper server. The directory you will need is program files (x86)\Lansweeper\Packageshare. You will want to create a new folder. I usually name it the title of the software that I am installing. Copy your installer into that folder.

Hover over deployment and click on installer packages. Click on new package in the upper left corner. Give the package a name and an optional description. Choose the maximum amount of time you want the deployment to run on each workstation. If the limit is reached the deployment will stop for that workstation that went over the time allotted. If you want you can choose a final action. The last step is to choose what user you want the deployment to run as. I would not choose select on deployment. I have never tried this in an automated deployment, so results may vary if you choose that option. Click ok. Click add step.

Usually the first action is to check the version of the software that you want to install. Even though we did that in the report, it is still a good idea to do it in the deployment as well. In the action dropdown choose condition. Name the step. Maybe use something like "Check version of name of software to be installed". Click add condition. The condition type should be file. In the path field enter the path to the file, but not the file name. Put the filename in the file name field. In the condition field I use the "has version lower than". When using the version number you will need to open the program and go to help about and see version number, or a similar way. You will need to use the exact version number that you see. For the Adobe deployment I tried to use 21.1.20138 and it did not work. I had to use 2021.001.20138. Click save.

In the action on success you can choose next. On action on failure you can choose what you want. If the condition fails, it just means that the software is already at the version you want it to be. Click ok.


Click add step.

If it is a MSI or MSP (like in the the deployment I am referencing) file that is used to install the software choose installer. If you have to use a specific command with switches use command.


In the step name field enter the name of the step such as "Update software name".

Next you need to enter the location of the install file.

You can enter the path Using the example below.
{PackageShare}\Installers

And the parameters field You will need to Enter the parameters Enter the parameters That will make the software install silently.
For instance the result command for the Adobe deployments looks like this:
msiexec.exe /update "{PackageShare}\Installers\AcroRdrDCUpd2100120138.msp" /norestart /qn.

Set the action on success and action on failure fields as you wish.

Next hover on deployment and go to Scheduled deployments. Click “new deployment configuration”.
Look under schedule and you will see after scanning. Click that radio button. In the package drop down choose the package you want to deploy.
In the “deploy on” section choose report. In the drop down choose the report that you created at the beginning of the post.
Select if you want the deployment to retry on offline computers.
Click ok.

Whenever Lansweeper scans a computer and that computer is in that report, the deployment will run on those computers.

If you need any assistance please reply to this post.

[Josip]

Sure , that sounds intereasting .

Can you show me code too?

Would like to solve this problem on my own but thats going to take time and i dont have any:D

[brandon_jones]

This may help. I do something like this for installing Adobe Reader updates. I have a report that finds all the computers that have Adobe Reader installed and the version is lower than the current version.

I then create a deployment that installs the update for Adobe Reader.

Then I create a scheduled deployment that runs after Lansweeper scans assets and runs the Adobe deployment against the computers that appear in the report of computers that have the out dated version of Adobe Reader.

So what you can do is run the deployment against the computers that appear in the report that you posted with a scheduled deployment.

I can post more details tomorrow if you would like them.