- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-21-2021 08:15 PM
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!
Solved! Go to Solution.
- Labels:
-
Report Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 04:58 PM
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,
Wherefor all active servers in the "Security - Global" AD group.
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 08:20 PM
Where tblADGroups.Name = 'PATCH_TEST_Anyday' And tblState.Statename = 'Active'
And tblComputersystem.Domainrole > 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 06:07 PM
Just like to learn like the others here.
You and me both.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 05:50 PM
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 05:54 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 04:58 PM
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,
Wherefor all active servers in the "Security - Global" AD group.
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 04:55 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-21-2021 10:10 PM
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.
Wherebecomes
Case tblComputersystem.PartOfDomain
When 0 Then 'No'
When 1 Then 'Yes'
End = 'Yes'
And tblState.Statename = 'Active'
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-22-2021 05:32 PM
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.