→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
B_B_
Engaged Sweeper II
I have two queries that run fine as long as they are two different reports. I need to take the output from each report and either combine them, along with their totals, into a single report then have it emailed. Or I need to combine the two reports into one report, then have it emailed. I'm not sure how to either combine the two reports, or how to schedule the reports to be sent out weekly.

I can easily paste the two queries, if necessary.

Thank you.
2 REPLIES 2
B_B_
Engaged Sweeper II
Charles.X,

First of all, thank you so much for all of your assistance. I'm still very new to Lansweeper, SQL code, and my job. I'm sort of feeling my way around all of these items at the same time, so forgive my newbie questions.

Now, this is what I am attempting to do. I have two reports that were written by other people here in the message boards, and I am trying to combine them into one report. Here is the first one, it checks to see if BitLocker is running on a Windows 10 machine:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1)
Order By tblEncryptableVolume.ProtectionStatus


Here is the second query, where it queries all the computers on the network and returns back just the total Windows 10 machines:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Version = Case tblOperatingsystem.Version When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511' When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703' When '10.0.16299' Then '1709'
When '10.0.17017' Then '1803' When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809' Else '?' End
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By Build,
tblAssets.AssetUnique


Okay, now with these two reports (queries?), I am needing to combine them to create a new report that shows the asset name (Windows name), Windows user name (Active Directory), the drive letter(s) encryption value (yes or no), the computer make/model, the last time/date seen, and possibly if the hard drive is HDD or SSD.

At this time, I will continue to work on this myself, and if I come up with something, I will post back here. Just a warning, it may be garbled and inefficient. With this being said, I do learn from my mistakes and I am always open to constructive criticism. I'm going to open a couple of instances of Notepad++, paste these two queries in each one, then see what I can cobble together between the two.

Thank you!
Esben_D
Lansweeper Employee
Lansweeper Employee
Kinda depends on the reports, you can always try and create a subquery or make both a subquery of a report.