Chart Report: Jabber

[rawi05ab]

Hi,

I would like a chart report counting the number (and %) of assets with Cisco Jabber installed and which have not.

I have made this none-chart detailed version, which works fine:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Jabber%') Then 'YES'
Else 'NO'
End As JabberFound
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery On tblPortableBattery.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1

Basically I would like to transfer above into a simple chart report for my dashboard.

[JacobH]

The easiest way I've found to do this is with a UNION statement... where you make one query with HAS, and make one query with HAS NOT, and UNION them together. The column names are tricky when you do that but you can just manually set the columns like I did below. The only thing is, be careful when you do a LIKE for software, if an asset has two similar software names on it - like a laptop has 'Jabber' but also may have 'Plugin for Jabber' or something - that will mess up the results. If they all have the exact same name, I'd recommend putting equals instead of like.

At any rate, it's a pretty useful trick

Select Status='Laptops With Jabber', count(tblAssets.AssetID) As Count
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblPortableBattery On tblPortableBattery.AssetID =
tblAssets.AssetID
Where tblAssets.AssetID In (Select Top 1000000 tblSoftware.AssetID
      From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
      Where
        tblSoftwareUni.softwareName Like
        '%Jabber%') And
  tblAssetCustom.State = 1
UNION
Select Status='Laptops Missing Jabber', count(tblAssets.AssetID)
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblPortableBattery On tblPortableBattery.AssetID =
tblAssets.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
      From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
      Where
        tblSoftwareUni.softwareName Like
        '%Jabber%') And
  tblAssetCustom.State = 1