Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Scan Duration

Created: 11 Dec 2013 | 7 comments

Hi,

I have a script that is pulling Scan Duration as one of the columns.  I am assuming this is in seconds.  How can I get it to print in HH.MM.SS format?

Thanks in advance.

 

 

Operating Systems:

Comments 7 CommentsJump to latest comment

.Brian's picture

Are you trying to do this in excel? It sounds like it may be in epoch time.

There is a simple SQL to convert it:

select dateadd(s, time_stamp/1000, '1/1/1970 00:00') from sem_client

If in excel you can do:

=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.

tnaqvi's picture

Brian,

I am using the following SQL Query and getting the Scan duration time in seconds (I think).  I need the information in HH.MM.SS (Hour.minute.second) format:

SELECT DISTINCT
   "SEM_AGENT"."DELETED"
  ,"PATTERN"."VERSION"
  ,"SCANS"."DURATION"
  ,"SCANS"."SCAN_TYPE"
  ,"SCANS"."STATUS"
  ,"PATTERN"."PATTERNDATE"
  ,"SEM_AGENT"."AGENT_VERSION"
  ,"SEM_CLIENT"."COMPUTER_NAME" "Computer Name"
  , "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System"
  , dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00') CREATION_DTTM
  , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970 00:00:00') Lastupdatetime
  , DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time"
  , "PATTERN"."PATTERNDATE" "Pattern Date"
  , "SEM_CLIENT"."USER_NAME" "User Name"
  , "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address"
  , "IDENTITY_MAP"."NAME" "Group Name"
FROM sem5.dbo.SCANS SCANS, (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER" "SEM_COMPUTER"
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"
  AND "SEM_AGENT"."DELETED"=0
WHERE SCANS.COMPUTER_IDX = SEM_COMPUTER.COMPUTER_ID
ORDER BY "Computer Name

tnaqvi's picture

James, The link IS very helpful ( I use it often), but I need my query altered to give me the desired results.  Right now my query works perfectly EXCEPT that it presents the Duration in seconds and I need it to show as HH:MM:SS.  If you of someone can edit just that one line in my query to give me the reults I need it would be greatly appreciated.

pete_4u2002's picture

why dont you use the SEPm monitor --> logs --> and get the information for 'Scan' log--> it gives information of machine, scan time HH:MM::SS

tnaqvi's picture

Thanks Pete but I actually need it as part of a report that I am building for management.  The query I presented shows "Scan Duration" in seconds.  I just need it in the hh:mm:ss format.

.Brian's picture

This article will show you how to get the SQL syntax, it may be of use

https://www-secure.symantec.com/connect/articles/e...

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.