Report: Group Changes in the last 90 days (Help converting v4.2 to v5)

[MichaelSVHAC]

Hi,

Could i get some assistance updating this query to work with v5 of Lansweeper?

Are there any resources that walk you through creating reports? Im quite green and would prefer to learn.....

Select Top 100000 tblntlogevent.TimeGenerated,
Upper(SubString(Replace(Replace(Replace(tblntlogevent.Message, Char(10), ''),
Char(13), ''), Char(9), ','), CharIndex('Member ID:', tblntlogevent.Message) +
17, CharIndex('Target Account Name:', tblntlogevent.Message) -
CharIndex('Member ID:', tblntlogevent.Message) - 30)) + ' ' + Case
When tblntlogevent.Eventcode In (632, 660) Then 'added to'
Else 'removed from'
End + ' ' + SubString(Replace(Replace(Replace(tblntlogevent.Message, Char(10),
''), Char(13), ''), Char(9), ','), CharIndex('Target Account ID:',
tblntlogevent.Message) + 13, CharIndex('Caller User Name:',
tblntlogevent.Message) - CharIndex('Target Account ID:',
tblntlogevent.Message) - 38) As [Group Activity],
Upper(SubString(Replace(Replace(Replace(tblntlogevent.Message, Char(10), ''),
Char(13), ''), Char(9), ','), CharIndex('Caller User Name:',
tblntlogevent.Message) - 6, CharIndex('Caller Domain:', tblntlogevent.Message)
- CharIndex('Caller User Name:', tblntlogevent.Message) - 23)) As
[Performed By],
tblComputers.Computer As [Detected On],
'usersm.gif' As icon
From tblntlogevent
Inner Join tblComputers On tblntlogevent.Computername =
tblComputers.Computername
Where tblntlogevent.TimeGenerated > DateAdd(DAY, -90, GetDate()) And
tblntlogevent.Eventcode In (632, 633, 660) And tblntlogevent.Message
Not Like '%CN=Domain Users,%'
Union All
Select Top 100000 tblntlogevent.TimeGenerated,
lansweeperdb.dbo.udf_GetGroupChange2K8(tblntlogevent.Message,
tblntlogevent.Eventcode) As [Group Activity],
SubString(Replace(tblntlogevent.Message, Char(9), ','),
CharIndex('Account Name:,,', Replace(tblntlogevent.Message, Char(9),
',')) + 15, CharIndex('Account Domain:,,', Replace(tblntlogevent.Message,
Char(9), ',')) - CharIndex('Account Name:,,', Replace(tblntlogevent.Message,
Char(9), ',')) - 18) As [Performed By],
tblComputers.Computer As [Detected On],
'usersm.gif' As icon
From tblntlogevent
Inner Join tblComputers On tblntlogevent.Computername =
tblComputers.Computername
Inner Join tblADComputers On tblComputers.Computername =
tblADComputers.Computername
Where tblntlogevent.TimeGenerated > DateAdd(DAY, -90, GetDate()) And
tblntlogevent.Eventcode In (4728, 4729, 4756, 4761, 4762) And
PatIndex('%$%', tblntlogevent.Message) = 0 And PatIndex('%None%',
tblntlogevent.Message) = 0
Order By dbo.tblntlogevent.TimeGenerated Desc

[MichaelSVHAC]

Well......that was easier than i thought it would be


i updated the table names to have "upgrade_" at the front and that fixed my broken report

[MichaelSVHAC]

Too much?

A gentle push in the right direction? Anyone?