
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-29-2015 12:12 PM
I would like to change this report. I need only installed software (+ sign)
THank you
Solved! Go to Solution.
- Labels:
-
Report Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-13-2015 05:03 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-13-2015 05:03 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-13-2015 10:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-10-2015 10:55 PM
tblSoftwareUni.Approved = 2
The three states appear to be
0: neutral/not specified
1: approved
2: not approved

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-10-2015 03:17 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-10-2015 02:48 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-09-2015 10:32 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-30-2015 12:03 AM
WHERE
...
AND tblSoftwareHist.Action = '1'
