Report creation based on FilePathFull for Installation

[panghh]

Hi,

I am stuck in a tricky situation and hoping to reach out to the experts.

Note: I am not an expert in SQL

Objective:
1. to scan for systems without a file or a file with wrong version number, report it and run a schedule installation

Situation:
1. I am generating a file scanning to look for a specific file existence, say it is "Target.exe" at version "8.8.8"
2. Created a scan for detect the file successfully in a report that gets updated automatically
3. I am stuck with the area that i am trying to call out the systems that meet requirements in below:
a. if file "target.exe" doesn't exist, report
b. if file "target.exe" does exist but without the version number, report
c. if file "target.exe" does exist but with a smaller version number, report


Note: in the DB, there are more than an entry for FilePathFull per assetname

Anyone can share your work would be deeply appreciated.

Thanks.

[panghh]

Thanks mate, you made it so easy for a noob like me can understand.

[RCorbeil · Accepted solution]

3a & 3b are simple enough to deal with.

Assuming you're LEFT JOINing tblFileVersions along the lines of

  LEFT JOIN tblFileVersions ON tblFileVersions.AssetID=tblAssets.AssetID AND tblFileVersions.FilePathfull LIKE '%target.exe%'

to filter for only records containing "target.exe", the first couple of conditions you identify in point 3 should be straightforward enough.

WHERE
  (tblFileVersions.FilePathfull IS NULL)                 -- 3a. target.exe doesn't exist
  OR (tblFileVersions.FilePathfull LIKE '%target.exe%'   -- 3b. target.exe exists
      AND (tblFileVersions.FileVersion IS NULL           --     but version is NULL
           OR tblFileVersions.FileVersion = ''           --     or blank
          )
     )
  OR ()  -- 3c. the messy bit

The file versions are text strings, not numbers. You can do a simple less/equal/greater comparison, but you'll be doing an ASCIIbetical comparison: a left-to-right text comparison, meaning that version "10" < version "2" because "1" < "2".

There have been discussions here before on how to deal with version numbers. Until someone proposes a better solution, I'm stuck with the idea of slicing-and-dicing the "number", and in a generic approach, zero-padding the decimal-separated pieces. Refer to this thread.

If you're content doing an ASCIIbetical comparison, 3c is simple enough.

  OR (tblFileVersions.FilePathfull LIKE '%target.exe%'   -- 3c. target.exe exists
      AND (tblFileVersions.FileVersion < '8.8.8')        --     and version is less than what we want
     )

Otherwise, consider the slice-and-dice route.

  LEFT JOIN (Select
               tblFileVersions.AssetID,
               tblFileVersions.FilePathfull,
               tblFileVersions.FileVersion,

               CASE
                 WHEN  -- IF there is a decimal
                   CharIndex('.', tblFileVersions.FileVersion) > 0
                 THEN  -- THEN return everything to the left of it
                   Left(tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)-1)
                 ELSE  -- ELSE just return the version
                   tblFileVersions.FileVersion
               END AS versionA,

               CASE
                 WHEN  -- IF there is a second decimal
                   CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
                 THEN  -- THEN return what's between the first and second decimals
                   Substring(tblFileVersions.FileVersion,
                             CharIndex('.', tblFileVersions.FileVersion)+1,                                              -- next char after first decimal
                             CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)  -- second decimal position
                               - CharIndex('.', tblFileVersions.FileVersion)                                             -- minus first decimal position
                               - 1                                                                                       -- don't want the second decimal
                            )
                 WHEN  -- ELSE if there's a first decimal
                   CharIndex('.', tblFileVersions.FileVersion) > 0
                 THEN  -- THEN return everything to the right of the first (only) decimal
                   Right(tblFileVersions.FileVersion,
                         Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion)
                        )
                 ELSE NULL  -- ELSE return nothing
               END AS versionB,

               CASE
                 WHEN  -- IF there is a second decimal AND a third decimal
                   CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
                   AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
                 THEN  -- THEN return what's between the second and third decimals
                   Substring(tblFileVersions.FileVersion,
                             CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) +1,                                             -- next char after second decimal
                             CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1)  -- third decimal position
                               - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)                                             -- minus second decimal position
                               - 1                                                                                                                                      -- don't want the third decimal
                            )
                 WHEN  -- ELSE IF there is a second decimal AND no third decimal
                   CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
                   AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) = 0
                 THEN  -- THEN return everything to the right of the second decimal
                   Right(tblFileVersions.FileVersion,
                         Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)
                        )
                 ELSE NULL  -- ELSE return nothing
               END AS versionC,

               CASE
                 WHEN  -- IF there is a second decimal AND a third decimal
                   CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
                   AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
                 THEN  -- THEN return everything to the right of the third decimal
                   Right(tblFileVersions.FileVersion,
                         Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1)
                        )
                 ELSE NULL  -- ELSE return nothing
               END AS versionD

             From
               tblFileVersions
             Where
               tblFileVersions.FilePathfull LIKE '%target.exe%') AS FileVersion ON FileVersion.AssetID=tblAssets.AssetID
  . . .
WHERE
  (FileVersion.FilePathfull IS NULL)                 -- 3a. target.exe doesn't exist
  OR (FileVersion.FilePathfull LIKE '%target.exe%'   -- 3b. target.exe exists
      AND (FileVersion.FileVersion IS NULL           --     but version is NULL
           OR FileVersions.FileVersion = ''           --     or blank
          )
     )
  OR (FileVersion.FilePathfull LIKE '%target.exe%'   -- 3c. target.exe exists
      AND (FileVersion.FileVersion IS NOT NULL
           AND FileVersion.FileVersion <> ''
           AND Right('00000' + FileVersion.versionA, 5) + '.'  -- pad out the pieces before comparing
             + Right('00000' + FileVersion.versionB, 5) + '.'
             + Right('00000' + FileVersion.versionC, 5) < '00008.00008.00008'
          )
     )

Hopefully that gets you in the ballpark.