We were using the following SQL Report to monitor the Software Portal Download logs, It was working fine now we have noticed that it is not returning correct information.
Please note that our Kbox is not integrated with AD and all user are using same Logon ID for Portal Package install or download that is why we have used the following SQL. Any idea will be highly appreciated.

Select
KBR.Start_Time 'Time',
MACHINE.Name 'Machine Name',
MACHINE.IP 'Machine IP',
KB.Name 'Package Name',
MACHINE.User 'User ID'
from KBOT KB
join KBOT_RUN KBR
on
KBR.KBOT_ID=KB.ID
join KBOT_RUN_MACHINE KBRM
on
KBRM.KBOT_RUN_ID=KBR.ID
join MACHINE MACHINE
on
MACHINE.ID=KBRM.Machine_ID
where KBR.Description ='portal request' and Date(KBR.Start_Time) between '2010-04-01' and '2011-12-31'

Regards,
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Can you be more specific on what is wrong? A before and after snippet would be great. If it's too much to tackle on the forums then I suggest a ticket.
Answered 03/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I just ran the query against my database and it worked fine. As Gerald stated, we need to know exactly HOW it is broken. What data did it show previously that it does no longer?
Answered 03/24/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thank you Andy and Gerald,

This query is working fine on Two ORGS out of 3 ORGS, and in one ORG it is not showing records of year 2010. Further, when I tested it on another virtual instance of Kbox then it is shows nothing at all.

Andy can you plz explain what type of data you are getting, are you getting both download and install logs as present in the download log Tab of user portal.

On which version of Kbox you are testing this query ? we have latest release of 5.1 installed.

Thank you and Best Regards,
Answered 03/24/2011 by: afzal
Fourth Degree Green Belt

Please log in to comment
0
If you remove your WHERE clause and sort by KBR.START_TIME do you see entries you are expecting?
Answered 03/25/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
No, i dont see any record, i think KACE has chaged the DB Structure now 'Portal request ' information is not in Kbot_Run table. However I have found Portal Logs in User History Table using the following SQL
SELECT * FROM USER_HISTORY U;

It has all informaiton except Machine Name, and i dont know how to join it with Machine Table. Any help will be highly appriciated.

Regards,
Answered 03/25/2011 by: afzal
Fourth Degree Green Belt

Please log in to comment
0
I don't think anything changed in the database - at least for a single org box. I can run the query on my 5.2 and 5.3 systems, and I get expected results.

If 'portal request' exists in the USER_HISTORY table, then this would be your query:

SELECT * FROM USER_HISTORY UH
JOIN MACHINE M ON (UH.USER_NAME = M.USER)
WHERE UH.DESCRIPTION ='%portal request%' and Date(UH.CREATED) between '2010-04-01' and '2011-12-31'
Answered 03/25/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Previously you were making an assumption that the machine of interest matched the IP address of the download.

Now you will need to make the assumption that the username being used matches the username on a machine. So join on the user.user_name to machine.user
Answered 03/25/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thank you Gerald and Andy,

But this will not solve my problem because , Kbox is not integrated with AD , so User name of Portal_History is different from which is in Machine Table.
Further, correcting my answer related to
removing WHERE clause, I have checked very carefully only 'Portal Request' inforamtion is not there in Kbot_Run other information is there, Gerald, would you please comment on this!

Regards,
Answered 03/25/2011 by: afzal
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity