Can I request a custom report here?

[briang]

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!

[briang]

Thank you VERY MUCH!


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

[RCorbeil]

Just like to learn like the others here.

You and me both.

[RCorbeil]

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]

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 · Accepted solution]

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]

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]

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]

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.