cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
panghh
Engaged Sweeper
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.

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

2 REPLIES 2
panghh
Engaged Sweeper
Thanks mate, you made it so easy for a noob like me can understand.

RCorbeil
Honored Sweeper II
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.