Asset Management Suite

I want to populate the current month's primary user with the previous month's primary user when the current month's primary user value is NULL. I am having a hard time figuring out how to change the month value that is reported in the results (prev month) to the current month. I will take this query and use it's results to modify the AeX AC Primary User data class data. Here is the query I am using:

SELECT DISTINCT IAAPU._ResourceGuid, IAAPU.Month, IAAPU.[User], IAAPU.Domain
FROM dbo.Inv_AeX_AC_Primary_User as IAAPU
WHERE(Month=DATENAME(Month,GETDATE()))AND(IAAPU.[User] ='')
UNION
SELECT DISTINCT IAAPU_2._ResourceGuid, IAAPU_2.Month, IAAPU_2.[User], IAAPU_2.Domain
FROM dbo.Inv_AeX_AC_Primary_User AS IAAPU_2
WHERE(Month=DATENAME(Month,DATEADD(Month,- 1,GETDATE())))AND(IAAPU_2._ResourceGuid NOT IN
(SELECT IAAPU_1._ResourceGuid
FROM dbo.Inv_AeX_AC_Primary_User AS IAAPU_1
WHERE(Month=DATENAME(Month,GETDATE()))AND(IAAPU_1.[User] <>'')))

Thanks in advance!
Dave

DId you want to insert a new row if there is no row found for the current month, and update the row if the current month's user(where is was an existing row for the previous month) is empty?

Or as simple as just inserting as new from those rows in your query result?

I want to use the previous months information (username) to populate the current month's username if the asset has not been logged onto yet for the current month.  The answer to your first question is YES.  I want it to overwrite any existing information left in the current months table (i.e. overwrite December from previous year with data from this December).  I know it only keeps 12 months of data and overwrites info when it goes around again after 12 months.  I want to be able to look at the resource manager - inventory tab - Aex AC Primary User and look at December and know that I am looking at data for this December and not last December if no one has logged on yet (not sure if that is possible unless done by a CMDB rule). 

Plus in the query I am using for a report, I want to be able to reflect last months user if there is no current primary user for the current month.  When the Primary User rule runs nightly and someone has logged on to the asset during the current month, it should overwrite the data that was placed there using the above modification (overwrite the November data that was placed in December data until some logged onto the asset in December). 

I may have not explained it too well, but we are simply trying to populate the report field with Primary user name from the prev month if there is no primary user for the current month, whether it be through query modification for the report or to directly modify the table, the second option being my choice.

Thanks in advance!
dave

Jfang, did you forget me???  I sure could use some help here.

Thanks!
Dave

Thanks for the reply Ludovic!  I will give it a try and post back with my results.

Dave

 You probably noticed I made a few edits in the last mintue or some.

Some where cosmetic but a couple actually corrected the SQL code itself.

I look forward to see the results (this may prove to be my first solution on the forum ;-).

Ludovic, I looked over your rule and discovered 2 things. 
1. I set pu1.month rule to look at current month.  You had pu1 and pu2 both looking at previous month. 
2. I wanted to check for NULL user for current month, so I set last line to "AND pu1.[User] is null" instead of is not NULL.  Resultant report shows no listings. 

This is kind of what I was running up against before, in that if current month is NULL, it's like it doesnt know where to go in the table to show NULL as it will ignore the identical month name from 12 months ago.  I DO like your rule as it is very simple and much easier to implement than my mess was.  Any more ideas?

Many thanks!
Dave

Sure. You should do a left outer join in order to get results if this month exists or not.

But in this case you need to use the ISNULL function for each entry to be displayed (so it remains consitent over a single set of columns as it is now).

I trust it that you can modify the query on your own now.

I Think you need to add a join in the right place (on the AC Primary User dat table) and then use ISNULL function to display the previous month primary user. Try this and let us know how it goes:

SELECT DISTINCT ISNULL(pu1._ResourceGuid, pu2._ResourceGuid), ISNULL(pu1.Month,DATENAME(Month,GETDATE())) , ISNULL(pu1.[User],pu2.[User]), ISNULL(pu1.Domain , pu2.Domain)
/* The above ISNULL() function replaces pu1.User with pu2.user if the former is null */
/* On update 3 we use the function with all fields to show last month info if this month doesn't exist */
  FROM dbo.Inv_AeX_AC_Primary_User as pu1
 RIGHT OUTER JOIN Inv_AeX_AC_Primary_User pu2
    ON pu1._ResourceGuid = pu2._ResourceGuid
 WHERE pu1.Month = DATENAME(Month,GETDATE()) /* UPDATE 1 HERE */
   AND pu2.Month = DATENAME(Month,DATEADD(Month,- 1,GETDATE()))
--   AND pu1.[User] is not null /* UPDATE 2 HERE */

PS: I tested this on SQL Server 2005 against a SMP 7.0 SP3 database, but I have no data in the DB to verify that the output would be correct.

! UPDATE 1: Correct the month seleection mechanism for pu1.month
! UPDATE 2: Commenting out the last line (pu1.user...) as it's irrelevant in this query
! UPDATE 3: Changing join to RIGHT OUTER so if no data exist for this month (pu1) we still have data from last month (if it exists)

Thanks Ludovic.  I will give this a try and report back.
Dave

I tried this and your query is correct in how it works, but if a current month is NULL for a user, it doesn't get in the output.  My output shows only user names that have a valid name in the current month, no NULL's.  To prove this, I added the following code to your select line to see if there is actually a NULL instance for user.  ", pu1.[user],pu1.month".  The output should show NULL's as well as your changed info.  It does not show any NULLS.  That is why I am wondering if the table is not updated with a current month until internally, Altiris creates a Primary user for the current month.

Thanks again and open for more suggestions  :)
Dave
.

I think I'll have to wait until tomroow to get back to you on this one.

One of the problems here is that we are somehow forced to use fields in the where clause that prevent us from getting nulls on the righ side using a left join.

I think the query needs a design make over.

Thanks Ludovic.  I will probably not be in tomorrow, as we are to get hit with 13" of snow tonight and tomorrow.  :)  Post something if you come up with anything and I will check it out when I get back.  My concern is that maybe Altiris doesnt create a current month until there is a valid primary user for that month...

dave

Ludovic (or anyone else), have you had any other thoughts yet?

Thanks!
Dave

Hi Dave,

I meant to put an update for you ealrier because I have a bad feeling with this attempt. I think we are suffering from a mutual-exclusion here, whereby you won't get data form the last month if you are filtering results for this month... may be trying with a group by and select max could help.

I'll see if I can get some times on this today.

Thanks Ludovivc!  I appreciate your time and patience on this.

dave

go

declare @month as varchar(16);
 set @month = DATENAME(Month,GETDATE())
declare @offset int
 set @offset = (select

case @month
 when 'December' then 1
 when 'November' then 2
 when 'October' then 3 
 when 'September'then 4 
 when 'August' then 5
 when 'July' then 6
 when 'June' then 7
 when 'May' then 8
 when 'April' then 9
 when 'March' then 10
 when 'February' then 11
 when 'January' then 0 end)

declare @Year table ([month] varchar(16),[position] int)
insert @Year values('January', 1 + @offset)
insert @Year values('Feburary', 2 + @offset)
insert @Year values('March', 3 + @offset)
insert @Year values('April', 4 + @offset)
insert @Year values('May', 5 + @offset)
insert @Year values('June', 6 + @offset)
insert @Year values('July', 7 + @offset)
insert @Year values('August', 8 + @offset)
insert @Year values('September',9 + @offset)
insert @Year values('October', 10 + @offset)
insert @Year values('November',11 + @offset)
insert @Year values('December', 12 + @offset)
update @Year set position = position - 12 where position > 13

 SELECT _ResourceGuid, q.month, y.position
   FROM Inv_AeX_AC_Primary_User as q
   join @Year y on q.month = y.month) r
  group by _ResourceGuid

--select top 10 * from Inv_AeX_AC_Primary_User

This is just not working. I can get the resource guid and the month (MAX based on a computed value using offset and table bariable) that would always yield in a group by query... but once we have done the group by it's impossible to get the month/user/computer data back and exploit this information in a report.

I'm sorry David, but I'll have to say this one can't be done with SQL (you can't select this month and then get the previous one for computers missing data).

Thank you so much for your help on this!  I was pulling my hair out and it seems like this is something that can't be easily done.  I will just have to accept doing it the way we do now then.

Thanks again for your time!
Dave

;--start of CTEs
with cte_sortableACPrimaryUser as
--this cte gives an extra sortable date column wrapping around table Inv_AeX_AC_Primary_User
(
	select
		_id,
		_ResourceGuid,
		[Month],
		[User],
		[Domain],
		[Server Generated],
		[Year],
		case	when [Month] = 'December'	then cast(cast([Year] as varchar(20)) + '-Dec-01' as datetime)
				when [Month] = 'November'	then cast(cast([Year] as varchar(20)) + '-Nov-01' as datetime)
				when [Month] = 'October'	then cast(cast([Year] as varchar(20)) + '-Oct-01' as datetime)
				when [Month] = 'September'	then cast(cast([Year] as varchar(20)) + '-Sep-01' as datetime)
				when [Month] = 'August'		then cast(cast([Year] as varchar(20)) + '-Aug-01' as datetime)
				when [Month] = 'July'		then cast(cast([Year] as varchar(20)) + '-Jul-01' as datetime)
				when [Month] = 'June'		then cast(cast([Year] as varchar(20)) + '-Jun-01' as datetime)
				when [Month] = 'May'		then cast(cast([Year] as varchar(20)) + '-May-01' as datetime)
				when [Month] = 'April'		then cast(cast([Year] as varchar(20)) + '-Apr-01' as datetime)
				when [Month] = 'March'		then cast(cast([Year] as varchar(20)) + '-Mar-01' as datetime)
				when [Month] = 'February'	then cast(cast([Year] as varchar(20)) + '-Feb-01' as datetime)
				when [Month] = 'January'	then cast(cast([Year] as varchar(20)) + '-Jan-01' as datetime)
		end as [SortableDate]
	from
		Inv_AeX_AC_Primary_User
),
cte_NoPrimaryUser as
--this cte gives all this month's records which have no user info
(
	select
		_id,
		_ResourceGuid,
		[SortableDate]
	from
		cte_sortableACPrimaryUser
	where
		(
			([User] is null)
			or
			(ltrim(rtrim([User])) = '')
		)
		and
		[Month] = datename(month, getdate())
		and
		[Year] = year(getdate())
),
cte_PrevMonthPrimaryUser as
--this CTE looks for previous month's records and tries to locate the primary user
(
	select
		npu._id,
		npu._ResourceGuid,
		npu.[SortableDate],
		spu.[User],
		spu.[Domain],
		spu.[Server Generated]
	from
			cte_sortableACPrimaryUser spu
		join cte_NoPrimaryUser npu on spu._ResourceGuid = npu._ResourceGuid and datediff(month, spu.[SortableDate], npu.[SortableDate]) = 1
	where
		(spu.[User] is not null)
		and
		(ltrim(rtrim(spu.[User])) != '')
)
select
	apu._id,
	apu._ResourceGuid,
	apu.[Month],
	isnull(apu.[User], pmpu.[User]) as [User],
	isnull(apu.[Domain], pmpu.[Domain]) as [Domain],
	isnull(apu.[Server Generated], pmpu.[Server Generated]) as [Server Generated],
	apu.[Year]
from
	Inv_AeX_AC_Primary_User apu
	left join cte_PrevMonthPrimaryUser pmpu on apu._id = pmpu._id /* and apu._ResourceGuid = pmpu._ResourceGuid */
;--end of CTEs

Dave, can you please give this monster query a go and let me know if there is a problem? Hope it help!

I am getting an error when testing in SQL server management studio...  See below.
dave

...but whatever logic is used for the [User] column in the often-hated-upon vComputer view,  seems to figure this out fairly well.  If you ever tear down that view, it gets pretty ugly but the code in there seems to work...

My bad, I forgot you were using NS6. I constructed this on NS7. Give me a few more mintures I will boot up a NS6 VM and revise this query.

;--start of CTEs
with cte_sortableACPrimaryUser as
--this cte gives an extra sortable date column wrapping around table Inv_AeX_AC_Primary_User
(
    select
        _id,
        _ResourceGuid,
        [Month],
        [User],
        [Domain],
        [Server Generated],
         case    when cast(cast(year(getdate()) as varchar(40)) + '-' + [Month] + '-01' as datetime) >= getdate() 
                 then cast(cast(year(getdate())-1 as varchar(40)) + '-' + [Month] + '-01' as datetime) 
                 else cast(cast(year(getdate()) as varchar(40)) + '-' + [Month] + '-01' as datetime)
        end as [SortableDate]
    from
        Inv_AeX_AC_Primary_User
),
cte_NoPrimaryUser as
--this cte gives all this month's records which have no user info
(
    select
        _id,
        _ResourceGuid,
        [SortableDate]
    from
        cte_sortableACPrimaryUser
    where
        (
            ([User] is null)
            or
            (ltrim(rtrim([User])) = '')
        )
        and
        [Month] = datename(month, getdate())
),
cte_PrevMonthPrimaryUser as
--this CTE looks for previous month's records and tries to locate the primary user
(
    select
        npu._id,
        npu._ResourceGuid,
        npu.[SortableDate],
        spu.[User],
        spu.[Domain],
        spu.[Server Generated]
    from
            cte_sortableACPrimaryUser spu
         join cte_NoPrimaryUser npu on spu._ResourceGuid = npu._ResourceGuid and datediff(month, spu.[SortableDate], npu.[SortableDate]) = 1
    where
        (spu.[User] is not null)
        and
        (ltrim(rtrim(spu.[User])) != '')
)
select
    apu._id,
    apu._ResourceGuid,
    apu.[Month],
    isnull(apu.[User], pmpu.[User]) as [User],
    isnull(apu.[Domain], pmpu.[Domain]) as [Domain],
    isnull(apu.[Server Generated], pmpu.[Server Generated]) as [Server Generated]
from
    Inv_AeX_AC_Primary_User apu
    left join cte_PrevMonthPrimaryUser pmpu on apu._id = pmpu._id /* and apu._ResourceGuid = pmpu._ResourceGuid */
;--end of CTEs

Guru, this one ran, however I have a question.  It seems to have outputted all data for previous months (I had 160k rows generated from about 17,000 assets...).  Note on the screenshot below that it lists all months for that _resourceguid.  How do I output only the current month with the last months user inserted when current month is NULL from this query?  Sorry, I'm trying to get a grip on how your query works.

Kyle, could you please go into further detail on your answer for me?  How do I tear down the view for vcomputer - User to look at, and do you have any ideas in addition to Guru's?

Thanks!
Dave

With regard to the duplicate rows, I think at the bottom of the query, before the ";-- end of CTEs" line, add the following:

WHERE apu.[Month] = DATENAME(month, GETDATE()) 
OR DATENAME(month, pmpu.[SortableDate]) = DATENAME(month, GETDATE())

To look at the vComputer view, open up the Altiris database with SQL management studio, look under Views then dbo.vComputer.  Right-click it and choose "Modify".  DON'T save any changes to it, just copy the code out.  It is really ugly (mainly as it tries several different ways to get the User column, depending on what data is in the database) but it does work.

You guys are saints!

Thanks!
dave 

Like Kyle suggested:
With regard to the duplicate rows, I think at the bottom of the query, before the ";-- end of CTEs" line, add the following:

WHERE apu.[Month] = DATENAME(month, GETDATE()) 

This will then give you the result limiting to current month records only.

There is some subtle difference between this approach and what vComputer's approach. My approach gives you exactly previous month's user as current user scenario. The vComputer view's approach will give you whoever was the last primary user. So beware of this difference. If vComputer view's approach actually suits you better, it will greatly reduce the code complexity.
 

Glad we could help Dave.  If it wouldn't be too much trouble, could you select one of our comments as the Solution to your issue?

If I may kick the tiger once more to understand... :), Your results above will give me names for only the assets that have no primary user for the current month, correct?  If there is a user for the current month, it will use that name?  Looking at your comments in the code, it seems to be that way, but I wanted to confirm this.  You mention 'previous month's user as current user'.  Is this inferring Primary user?  Sorry for so many questions, but still learning here... :)

dave

Your results above will give me names for only the assets that have no primary user for the current month, correct? 

No, it will give you all the records for the current month

If there is a user for the current month, it will use that name?

Yes,if there is a current month primary user record available, it will use that. If no, then it falls back to previous month's primary user if that is available.

 

Thanks!!!

dave