Several Report Questions
EDIT: Adding in other questions so as not to flood forum with multiple questions around same topic.
The following are reports I am working on at the moment and I have some questions about a few at the moment. A lot comes down to the rules making though I think. -
1. List of computers that haven’t rebooted in x number of days (maybe 30 or 60)
2. List of duplicate computer names from inventory
(No questions on these yet but I will edit if I do.)
List of computers with failed “managed software installationsâ€Â
List of new computers added to inventory (today, this week, this month)
List of computers without anti-virus software (Symantec endpoint, Symantec antivirus, etc.)
List of all of the printer models in use by computers
List of all of the local accounts used to log on to computers
List of computers NOT using dhcp for their network configuration
List of computers broken down by manufacturer and model with total counts
1. Is there a way to create a report for finding all computers that have not rebooted within the past 30 days? The report I was making was going swimmingly until it came to setting the rule. I dropped in the last reboot section and when I went to the operators there was nothing for, "is within", "is older than", etc. that you get using a different date field such as warranty. I used the > operator and set the value to 30 but I don't think that ended up working correctly as I got some computers in my report that were within 30 days and a few that were out. Why does this not behave like a normal date field within the wizard and how can I fix or get around this? Please keep in mind I know no SQL.
2. Is it even possible to have duplicate computers in inventory? I thought the system checked in and kept a live update of what was available.
How can I set a rule to find a duplicate?
The following are reports I am working on at the moment and I have some questions about a few at the moment. A lot comes down to the rules making though I think. -
1. List of computers that haven’t rebooted in x number of days (maybe 30 or 60)
2. List of duplicate computer names from inventory
(No questions on these yet but I will edit if I do.)
List of computers with failed “managed software installationsâ€Â
List of new computers added to inventory (today, this week, this month)
List of computers without anti-virus software (Symantec endpoint, Symantec antivirus, etc.)
List of all of the printer models in use by computers
List of all of the local accounts used to log on to computers
List of computers NOT using dhcp for their network configuration
List of computers broken down by manufacturer and model with total counts
1. Is there a way to create a report for finding all computers that have not rebooted within the past 30 days? The report I was making was going swimmingly until it came to setting the rule. I dropped in the last reboot section and when I went to the operators there was nothing for, "is within", "is older than", etc. that you get using a different date field such as warranty. I used the > operator and set the value to 30 but I don't think that ended up working correctly as I got some computers in my report that were within 30 days and a few that were out. Why does this not behave like a normal date field within the wizard and how can I fix or get around this? Please keep in mind I know no SQL.
2. Is it even possible to have duplicate computers in inventory? I thought the system checked in and kept a live update of what was available.
How can I set a rule to find a duplicate?
0 Comments
[ + ] Show comments
Answers (7)
Please log in to answer
Posted by:
airwolf
12 years ago
#1 is tricky because it's not a date field. The UPTIME field lists the number of days plus hours and minutes since last reboot separated by a comma. This should work:
SELECT NAME, UPTIME from MACHINE
where LEFT(UPTIME,LOCATE(',',UPTIME)) >= 30
Posted by:
scottlutz
12 years ago
List of computers with failed “managed software installationsâ€Â
SELECT M.NAME AS MACHINE_NAME,
S.DISPLAY_NAME,
S.DISPLAY_VERSION,
MIA.ATTEMPT_COUNT
FROM MACHINE M,
MI_ATTEMPT MIA,
MI,
SOFTWARE S
WHERE M.ID = MIA.MACHINE_ID
AND MI.ID = MIA.MI_ID
AND S.ID = MI.SOFTWARE_ID
ORDER BY ATTEMPT_COUNT DESC,
MACHINE_NAME
Posted by:
scottlutz
12 years ago
http://kace.uservoice.com/forums/82699-k1000/suggestions/1169811-improved-reporting
Posted by:
Gearshock
12 years ago
Ugh, the filtering options on this 5.3 reporting system are along the lines of minimal. Seems like a step back in some ways. Can I leave a suggestion somewhere for them to make it more robust?
Posted by:
Gearshock
12 years ago
Solved question 2 with the following.
select MACHINE.NAME AS SYSTEM_NAME,LAST_SYNC,IP,KUID,MAC,BIOS_SERIAL_NUMBER, OS_NAME,SERVICE_PACK,CT FROM MACHINE ,
( select COUNT(ID) CT, MACHINE.NAME FROM MACHINE GROUP BY MACHINE.NAME )M2
WHERE
M2.CT>1 and
MACHINE.NAME=M2.NAME
ORDER BY SYSTEM_NAME, BIOS_SERIAL_NUMBER,IP,MAC
Still working on the rest.
EDIT: Got these as well
List of computers NOT using dhcp for their network configuration
List of all of the local accounts used to log on to computers
select MACHINE.NAME AS SYSTEM_NAME,LAST_SYNC,IP,KUID,MAC,BIOS_SERIAL_NUMBER, OS_NAME,SERVICE_PACK,CT FROM MACHINE ,
( select COUNT(ID) CT, MACHINE.NAME FROM MACHINE GROUP BY MACHINE.NAME )M2
WHERE
M2.CT>1 and
MACHINE.NAME=M2.NAME
ORDER BY SYSTEM_NAME, BIOS_SERIAL_NUMBER,IP,MAC
Still working on the rest.
EDIT: Got these as well
List of computers NOT using dhcp for their network configuration
List of all of the local accounts used to log on to computers
Posted by:
Gearshock
12 years ago
Thank you both very much for your help. I really need to learn SQL so I don't have to keep bugging people on here.
Posted by:
scottlutz
12 years ago
I didn't know any SQL until I started with KACE and lingering around these forums. I just try to help anyone I can to polish my skillz. Your mileage may vary of course :)
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.