does anybody know how to get the "agents last checkin time" into a variable?
i think a the "Execute SQL Select Query" might be usefull but have no idea of how to find out what the query statement should be.
i want to check the agent last checkin time to do some calculations when a tickets is generated or escalated.
Example1: when a server goes down, outside covarage hours it escalates when it comes into covarage but also could be back up again by then...so that would be a much lower severty then a "system down".
Example2: a ticket is generated when a workstation does not get KES updates for 7 days, so that COULD be a problem, but is logical if the system has not checked in the last 7 days, so in that case we can automatically close the ticket created.
your help is very welcome !
Hans, I have a SQL select query that I use to grab the Machine ID into a variable. I check the submitter name against the current logged in user and put the machine name into a variable. I do this because I have multiple Service Desks for multiple clients, and unfortunately if they open a ticket through live connect they are able to see all of the service desks for all the other clients. So I have created logins for all the users and they open a ticket by logging in to the system. By doing it that way it does not capture the machine id in the ticket. My method is not perfect, because I some time have users logged into multiple machine at the same time that is why I am selecting the "TOP (1)" I am still trying to figure out how to actually populate the "MachineID" field in the ticket. I am only able to populate a custom field with the variable. If you or anyone has some insight into what ticket property it is I would be grateful.
All you should need to do is find the proper view that the info is stored in. If you want some help finding it let me know.
SELECT TOP (1) [Expr1] FROM dbo.vAgents_AgentStatus WHERE(currentLogin = '[$SubmitterName$]')
i tried this SQL statements but cannot get it working
Select [lastCheckinTime] FROM dbo.vAgents_AgentStatus where DisplayName = [$machine$]
Select [lastCheckinTime] FROM [ksubscribers].[dbo].[vAgentStatus] where DisplayName = [$machine$]
I responded via email, but I see it never posted here. That is a good thing because I just tested what I sent you and it still didn't work :) Use this SQL statement
SELECT [lastCheckinTime] FROM dbo.vAgents_AgentStatus WHERE(DisplayName = '[$FullMachineName$]')
You almost had it the variable you were using didn't have the full name of the machine and you were missing the single quotes and parenthesis in the statement. I have just tested this and it worked for me. I hope this helps and let me know if you need anything else.
that one does the trick !
Thanks Erik !
Awesome!! glad I could help.
i've created a few small VBScripts to determine if a system has been offline for more then 6 days, more then 30 days or is actually online so i can start to automate ticket priotiry and automatic closure for "false KES update alarms" etc !
Great help !