Recent Software Changes Report show only installed software

[laurentiun]

Hello,
I would like to change this report. I need only installed software (+ sign)

THank you

[RCorbeil · Accepted solution]

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblstate.Statename As [Asset state],
  tblADusers.Username,
  tblAssets.Domain,
  tsysOS.Image As icon,
  Case tblSoftwareHist.Action When '1' Then '+' When '2' Then '-' End As [+/-],
  tblSoftwareUni.softwareName As Software,
  tblSoftwareHist.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftwareHist.Installdate,
  tblSoftwareHist.Lastchanged,
  tblADusers.Department
FROM
  tblAssets
  INNER JOIN tblSoftwareHist ON tblAssets.AssetID = tblSoftwareHist.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareHist.softid = tblSoftwareUni.SoftID
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
  INNER JOIN tblADusers ON tblAssets.Username = tblADusers.Username
  INNER JOIN tblstate ON tblstate.State = tblAssetCustom.State
WHERE
  tblSoftwareHist.Lastchanged > GetDate() - 7
  AND tblADusers.Department = 'something'
  AND tblSoftwareUni.Approved IN (0, 2)
  AND tblSoftwareHist.Action = '1'
  AND tblSoftwareUni.softwareName NOT LIKE '%Flash Player%'
ORDER BY
  tblAssets.AssetName,
  tblSoftwareHist.Lastchanged Desc,
  Software

You should be able to repeat the last condition on the WHERE to match other software you're not interested in seeing.

[laurentiun]

Thank you! last questions: may I exclude some software like: exclude "%Flash Player%"? Ho can I do this? Also, I'm trying to show neutral software and denied software, but it's showing all departments.. not only selected one " tblADusers.Department = 'department'

[RCorbeil]

Add another condition to the WHERE clause:

tblSoftwareUni.Approved = 2

The three states appear to be
0: neutral/not specified
1: approved
2: not approved

[laurentiun]

Thank you very much. Please forgive my mistake.. I need "Approved" column. I would like to receive by email, only "denied" new installed software.

[Daniel_B]

Your report only lists computers in state "Active". The following modified report lists all computers and shows the asset state as well.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblState.Statename As [Asset state],
  tblADusers.Username,
  tblAssets.Domain,
  tsysOS.Image As icon,
  Case tblSoftwareHist.Action When '1' Then '+' When '2' Then '-' End As [+/-],
  tblSoftwareUni.softwareName As Software,
  tblSoftwareHist.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftwareHist.Installdate,
  tblSoftwareHist.Lastchanged,
  tblADusers.Department
From tblAssets
  Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
  Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblSoftwareHist.Lastchanged > GetDate() - 7 And tblADusers.Department =
  'something' And tblSoftwareHist.Action = '1'
Order By tblAssets.AssetName,
  tblSoftwareHist.Lastchanged Desc,
  Software

[laurentiun]

Thank you, I would like to add "state" column to these reports.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblADusers.Username,
tblAssets.Domain,
tsysOS.Image As icon,
Case tblSoftwareHist.Action When '1' Then '+' When '2' Then '-' End As [+/-],
tblSoftwareUni.softwareName As Software,
tblSoftwareHist.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftwareHist.Installdate,
tblSoftwareHist.Lastchanged,
tblADusers.Department
From tblAssets
Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblSoftwareHist.Lastchanged > GetDate() - 7 And tblADusers.Department =
'something' And tblAssetCustom.State = 1 And tblSoftwareHist.Action = '1'
Order By tblAssets.AssetName,
tblSoftwareHist.Lastchanged Desc,
Software

[RCorbeil]

If you look at the report you should see that the +/- is being determined by checking the value of tblSoftwareHist.Action: if it's '1' then '+', if it's '2' then '-'. Add a condition to the WHERE clause:

WHERE
  ...
  AND tblSoftwareHist.Action = '1'