Portal Download logs
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,
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
Answers (8)
Please log in to answer
Posted by:
GillySpy
13 years ago
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.
Posted by:
airwolf
13 years ago
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?
Posted by:
afzal
13 years ago
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,
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,
Posted by:
GillySpy
13 years ago
If you remove your WHERE clause and sort by KBR.START_TIME do you see entries you are expecting?
Posted by:
afzal
13 years ago
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,
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,
Posted by:
airwolf
13 years ago
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'
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'
Posted by:
GillySpy
13 years ago
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
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
Posted by:
afzal
13 years ago
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,
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,
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.