Notification

Icon
Error

Can I request a custom report here? - Would like a report created for my need, or help creating one.

Posted: Wednesday, July 21, 2021 7:15:17 PM(UTC)
Brian G

Brian G

Member Original PosterPosts: 3
1
Like
This issue has been solved! Click here to view the solution
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!

RC62N
#1RC62N Member Posts: 575  
posted: 7/21/2021 9:10:50 PM(UTC)
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.
Code:
Where
  Case tblComputersystem.PartOfDomain
    When 0 Then 'No'
    When 1 Then 'Yes'
  End = 'Yes'
  And tblState.Statename = 'Active'
becomes
Code:
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.
Code:
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.
Code:
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.
Code:
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.
Code:
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().
Code:
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. Angel
Brian G
#2Brian G Member Original PosterPosts: 3  
posted: 7/22/2021 3:55:24 AM(UTC)

Thank you for your reply!

Quote:
"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!
RC62N
#3RC62N Member Posts: 575  
posted: 7/22/2021 3:58:51 PM(UTC)
Not a problem. Strip out the asset group bits and put back the AD group bits...
Code:
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,
Code:
Where
  tblState.Statename = 'Active'
  And tblComputerSystem.DomainRole > 1
  And tblADGroups.GroupType = -2147483646
for all active servers in the "Security - Global" AD group.
rader
#4rader Member Posts: 36  
posted: 7/22/2021 4:32:14 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post

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.
rader attached the following image(s):
Dynamic Server Group.png
RC62N
#5RC62N Member Posts: 575  
posted: 7/22/2021 4:50:17 PM(UTC)
Quote:
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:
Quote:
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
#6rader Member Posts: 36  
posted: 7/22/2021 4:54:20 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
Quote:
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:
Quote:
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.Angel
RC62N
#7RC62N Member Posts: 575  
posted: 7/22/2021 5:07:36 PM(UTC)
Quote:
Just like to learn like the others here.

You and me both. Angel
Brian G
#8Brian G Member Original PosterPosts: 3  
posted: 7/22/2021 7:20:56 PM(UTC)
Thank you VERY MUCH!


Where tblADGroups.Name = 'PATCH_TEST_Anyday' And tblState.Statename = 'Active'
And tblComputersystem.Domainrole > 1

Active Discussions

Lansweeper Scan User Exclusion / Define User OU for active scanning
by  Almada   Go to last post Go to first unread
Last post: Yesterday at 6:43:34 PM(UTC)
Lansweeper sqlServerId is changing
by  Ciro Bizelli  
Go to last post Go to first unread
Last post: Yesterday at 3:40:34 PM(UTC)
Lansweeper Slow Loading Lansweeper
by  Maikel Vanroelen   Go to last post Go to first unread
Last post: Yesterday at 9:25:31 AM(UTC)
Lansweeper Sync information
by  Jay-IT  
Go to last post Go to first unread
Last post: 9/16/2021 9:20:11 PM(UTC)
Lansweeper "Unknown" exclusion does not exclude Unknown assets
by  Almada   Go to last post Go to first unread
Last post: 9/16/2021 7:18:43 PM(UTC)
Lansweeper multiple scanning servers with granular permission
by  FrankSc  
Go to last post Go to first unread
Last post: 9/16/2021 6:29:54 PM(UTC)
Lansweeper Separate helpdesk websites?
by  JCochran   Go to last post Go to first unread
Last post: 9/15/2021 2:51:03 PM(UTC)
Lansweeper Remove Ads
by  FrankSc  
Go to last post Go to first unread
Last post: 9/15/2021 12:30:18 PM(UTC)