« Previous Next »

• 06-03-2009, 9:18 AM

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52

  Date \ Time & Graph Reply Contact Hi please can some on assist me with my problem. I have 3000 text files. Each text file has 3 bits of information Computer Name, Date, Time B00NOVA,     17/07/2007, 12:45:24 B00NOVA     is the name of a computer and the date and time is when this PC was added to the domain. How then can I write a query that wil go through all these text files and for example pull out all PC's that were put on the domain in the year 2007 and the month 7 and then put this information into a graph.

• 06-03-2009, 1:29 PM

  In reply to

  ksingla Joined on 06-13-2006, 11:02 PM Redmond, WA Posts 845

  Re: Date \ Time & Graph Reply Contact Something like following will work. LogParser.exe "select Year,Month,count(*) using Extract_Token(Date,1,'/') as Month, Extract_Token(Date,2,'/') as Year from file*.csv group by Year,Month" -i:csv -headerrow:off -iheaderfile:header.csv header.csv contains only one line which serves as header as ComputerName, Date, Time. You will need this if your files doesn't contain a header already. Thanks, Kanwal Follow me on twitter at http://twitter.com/kjsingla

• 06-03-2009, 1:55 PM

  In reply to

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52

  Re: Date \ Time & Graph Reply Contact Hi Kanwal hope you are well and thank you for your prompt reply. However on running your query I am getting an error. Let me show you. This is my data ComputerName,Date,Time B002627,08/10/2004,21:03:27   This is my query strQuery =  "select Year,Month,count(*) using Extract_Token(Date,1,'/') as Month, Extract_Token(Date,2,'/') as Year  into " & "\\h505050\c$\wamp\www\helpdesk\assets\lp_output\interrogation\" & remote_host & ".gif" & " From 'C:\wamp\www\helpdesk\pc_audits\creation\*.txt' group by Year,Month"     and this is the error LP is generating --------------------------- Windows Script Host --------------------------- Script: C:\Documents and Settings\hg363.RBHNT\Desktop\interrogation.vbe Line: 183 Char: 1 Error: Error executing query: Column Month is not an INTEGER nor a REAL [Type mismatch.] Code: 80020005 Source:  CLogQueryClass --------------------------- OK   --------------------------- Can you please tell me where it is going wrong regards Mr Ghelani

• 06-03-2009, 2:15 PM

  In reply to

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52	Re: Date \ Time & Graph Reply Contact   Sorry Kanwal please accept my apologies as there is a mistake in the post above. My data looks like this ComputerName,Date B002627,08/10/2004 21:03:27 But obviously without the space inbetween the fields and the actual data Regards Mr Ghelani

• 06-08-2009, 11:40 AM

  In reply to

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52	Re: Date \ Time & Graph Reply Contact Dear Kanwal hope you are well. Have you had an opportunity to look at my last post. Please if its at all possible can you fix this error for me and provide me with the correct syntax, kind regards Mr Ghelani UK

• 06-08-2009, 11:57 AM

  In reply to

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52

  Re: Date \ Time & Graph Reply Contact Hi Kanwal also if you dont mind, could you please in very lay man terms explian to me and those who are new to LP what this query means   select Year,Month,count(*) using Extract_Token(Date,1,'/') as Month, Extract_Token(Date,2,'/') as Year   In specifi what does the Extract_Token(Date,1,'/') as Month and Extract_Token(Date,2,'/') as Year  mean thnak you very much kind regards Mr Ghelani

• 06-08-2009, 1:40 PM

  In reply to

  ksingla Joined on 06-13-2006, 11:02 PM Redmond, WA Posts 845

  Re: Date \ Time & Graph Reply Contact Hi, Regarding the error, it seems there is no comma between date and time. So you can merge the Date and Time fields into one in header.csv as datetime. Extract_Token method in logparser takes three parameters. First is the field, second index and third the token separator. Extract_Token(Date, 1, ‘/’) will thus take date field, break it at ‘/’ character and pick the element at index 1 (first one is 0). I used the Extract_Token method to get Month and Year from the date field. Once you have the Month and Year, you can just do a standard group by sql query to get what you wanted. Let me know if you still get the error with the query after fixing header.csv. Thanks, Kanwal Follow me on twitter at http://twitter.com/kjsingla

• 06-09-2009, 6:38 AM

  In reply to

  hg363 Joined on 09-04-2007, 3:44 PM Posts 52

  Re: Date \ Time & Graph Reply Contact Hi Kanwal I am still unbale to get this working, please can you help me further. This is my data (the data is in CSV format, here is an example) ComputerName ,Date H650242,17/04/2007 10:34:32 This is my code Set objShell = CreateObject("WScript.Shell") objShell.CurrentDirectory = "\\h505050\C$\Program Files\Log Parser 2.2\" Set objLogParser = CreateObject("MSUtil.LogQuery") Set objinputFormat = CreateObject("MSUtil.LogQuery.CSVInputFormat") Set objOutputFormat = CreateObject("MSUtil.LogQuery.ChartOutputFormat") objOutputFormat.chartType = "BarStacked" objOutputFormat.chartTitle = "Creation Times taken from Active Directory" objOutputFormat.fileType = "gif" 'GIF BMP objOutputFormat.groupSize = "800x600" objOutputFormat.Values = "OFF" strQuery =  "select  Year, Month, count(*) using Extract_Token(Date,1,'/') as Month, Extract_Token(Date,2,'/') as Year  into " & "\\h505050\c$\wamp\www\helpdesk\assets\lp_output\interrogation\graph.gif" & " From 'C:\wamp\www\helpdesk\pc_audits\creation\*.csv' group by Year,Month" objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat And on running this code here is the error Error: Error executing query: Column Month is not an INTEGER nor a REAL [Type mismatch.] Code: 80020005 Source:  CLogQueryClass And the exact line that is causing this error within the code is:   objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat Do you think you could take a look at this and correct what ever is making the error,as I really am lost, regards Mr Ghelani