cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
briang
Engaged Sweeper II
Hi,

I have found two reports on the LS site that are great, but I need a combination of both reports, and doing that is beyond my expertise so I was wondering is there a place that I can request a report be made, or help me make it?

The 2 reports are "Servers being members of a group", and "device uptime".

https://www.lansweeper.com/report/domain-computers-and-their-ad-groups/
https://www.lansweeper.com/report/asset-uptime-since-last-reboot-audit/

So what I'm looking for is a report that shows me:
Device Name, IP address, etc... Basic Info.
As well as the servers Uptime AND that only belongs to a specific group that I specify in a WHERE statement in the query.

If someone would help point me in the right direction I would be very grateful, thank you!

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Not a problem. Strip out the asset group bits and put back the AD group bits...
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName

From there you want to add your group type filters to the WHERE clause, so, for example,
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
for all active servers in the "Security - Global" AD group.

View solution in original post

8 REPLIES 8
briang
Engaged Sweeper II
Thank you VERY MUCH!


Where tblADGroups.Name = 'PATCH_TEST_Anyday' And tblState.Statename = 'Active'
And tblComputersystem.Domainrole > 1
RCorbeil
Honored Sweeper II
Just like to learn like the others here.

You and me both.
RCorbeil
Honored Sweeper II
This seems to only work on the Static Groups, not the Dynamic Groups.

I'm pretty sure it was determined a number of years ago that this was the case. Your testing confirms that that's unchanged.

Searching... Here we go.

Posted by Daniel.B on 2015-06-25:
Using Dynamic Asset groups for reports is not possible. In fact, through custom reports you can do everything what you can do with dynamic asset groups (plus much more).


rader
Champion Sweeper III
RC62N wrote:
This seems to only work on the Static Groups, not the Dynamic Groups.

I'm pretty sure it was determined a number of years ago that this was the case. Your testing confirms that that's unchanged.

Searching... Here we go.

Posted by Daniel.B on 2015-06-25:
Using Dynamic Asset groups for reports is not possible. In fact, through custom reports you can do everything what you can do with dynamic asset groups (plus much more).




Good to know. I hadn't seen that one before. Just like to learn like the others here.
Thanks.
RCorbeil
Honored Sweeper II
Not a problem. Strip out the asset group bits and put back the AD group bits...
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName

From there you want to add your group type filters to the WHERE clause, so, for example,
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
for all active servers in the "Security - Global" AD group.
briang
Engaged Sweeper II

Thank you for your reply!

"I'm going to assume that when you say you want computers that are members of groups, you mean asset groups "


I actually mean AD Groups, not groups in LS. Can you show me how to do that?

I am willing to learn, I have basic SQL exp. select, from, where. Your stuff is next level to me, lol.

Please write back!
RCorbeil
Honored Sweeper II
Are you willing to learn? Being able to build your own reports increases LANSweeper's value considerably.

Engaging "teach a man to fish" mode...

Let's start with the domain computers and their AD groups report that you identified. That actually includes most of the basics you'd likely want to start with. It lists both servers and desktops/laptops, generally categorised as workstations, so we'll need to add a filter to the WHERE clause to limit that to just servers.
Where
Case tblComputersystem.PartOfDomain
When 0 Then 'No'
When 1 Then 'Yes'
End = 'Yes'
And tblState.Statename = 'Active'
becomes
Where
Case tblComputersystem.PartOfDomain
When 0 Then 'No'
When 1 Then 'Yes'
End = 'Yes'
And tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1

(If you run a SELECT * FROM tblDomainRoles you'll see the list of values. 0 & 1 are workstations, 2-5 are servers.)

Now you add the uptime to that. You can copy that directly from the second report you referenced.
Select Top 1000000
...
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot
From
...

You now have a list of servers and their uptimes.

I'm going to assume that when you say you want computers that are members of groups, you mean asset groups (Configuration > Asset Groups), not AD groups, so let's strip out references to AD groups from the report.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName

Any given asset can be a member of multiple asset groups, so the actual groups are connected through a separate link table.
tblAssets --- tblAssetGroupLink --- tblAssetGroups

We'll need to add both the link table and the asset groups table in.

Question: do you need to see the groups or do you just want to filter on the groups?

Let's assume initially you just want to filter. Modify the WHERE clause to add the filter.
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblAssets.AssetID IN ( Select
tblAssetGroupLink.AssetID
From
tblAssetGroupLink
Inner Join tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
tblAssetGroups.AssetGroup IN ('Group 1', 'Group 2', 'Group 3')
)

The sub-select builds a list of assets that are members of the groups you specify, then the asset ID in the main select is checked to see if it's a member of that short list.

If you want to see the asset groups each asset belongs to, that can be added. If an asset could only belong to one group, that would be a simple matter of joining tblAssetGroupLink and tblAssetGroups to the main query, but an asset can potentially belong to multiple groups, so I'll combine them into a single output column using Stuff().
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
-- combine all the asset groups the asset belongs to into a single output column
Stuff( ( Select ', ' + Cast(tblAssetGroups.AssetGroup As VarChar(30)) -- arbitrarily truncate to 30 chars; source field is defined as VarChar(150)
From tblAssetGroupLink
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where tblAssets.AssetID = tblAssetGroupLink.AssetID
For Xml Path('')), 1, 2, '') As [Asset Groups]
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblAssets.AssetID IN ( Select
tblAssetGroupLink.AssetID
From
tblAssetGroupLink
Inner Join tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
tblAssetGroups.AssetGroup IN ('Group 1', 'Group 2', 'Group 3')
)
Order By
tblAssets.Domain,
tblAssets.AssetName

And that's how you catch a fish.
rader
Champion Sweeper III
RC62N wrote:

I'm going to assume that when you say you want computers that are members of groups, you mean asset groups (Configuration > Asset Groups)


Side note:
This seems to only work on the Static Groups, not the Dynamic Groups.
I tested this our by running the report using the static 'Default Group' for 'Group 1' and a dynamic group I created called 'Servers' as 'Group 2' and only the Static group came out on the report.

To test, create the Dynamic Group shown here. This catches all the servers in an environment that show up on the Static Default Group.