[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

SQL Query of Print event ID 10 in event viewer

I have logparser 2.2 from microsoft.  ANd I have some example SQL queries for event viewer.
But I'm not an SQL person so I don't know how to modify this query.
The below query prints out who printed in the last 2 days and how many pages and the total file size.
I want a specific date range, like 2/4/11 thru 2/7/11 not the last 2 days
Can someone modify this so I can run that?
ALso, I'd prefer details instead of just totals, like filenames is really what I'm looking for.
The below script gives you totals

DatePrinted      FileOwner           FileSizeTotal      PagesPrintedTotal
2011-02-14      Julia          1216267      10
2011-02-15      Julia      116490016      69
2011-02-15      Paul      6499548                      58

SCRIPT BELOW:

SELECT
      TO_STRING(TimeGenerated, 'yyyy-MM-dd') AS DatePrinted,
      SUBSTR( Message, ADD(INDEX_OF(Message, ' owned by '), 10), SUB(SUB(INDEX_OF(Message, ' was printed on '), INDEX_OF(Message, ' owned by ' )), 10) )  AS FileOwner,
      SUM(TO_INT(SUBSTR( Message, ADD(INDEX_OF(Message, '. Size in bytes: '), 17), SUB(SUB(INDEX_OF(Message, '; pages printed: '), INDEX_OF(Message, '. Size in bytes: ' )), 17) ) ) ) AS FileSizeTotal,
      SUM(TO_INT(TRIM(SUBSTR( Message, ADD(INDEX_OF(Message, '; pages printed: '), 17), SUB(SUB(STRLEN(Message), INDEX_OF(Message, '; pages printed: ' )), 17) ) ) ) ) AS PagesPrintedTotal
INTO PrintJobsLast2DaysByOwner.txt
FROM System
WHERE (EventID = 10
      AND TimeGenerated >
            SUB( TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-03 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
      )
GROUP BY DatePrinted, FileOwner


SELECT 
	TO_STRING(TimeGenerated, 'yyyy-MM-dd') AS DatePrinted,
	SUBSTR( Message, ADD(INDEX_OF(Message, ' owned by '), 10), SUB(SUB(INDEX_OF(Message, ' was printed on '), INDEX_OF(Message, ' owned by ' )), 10) )  AS FileOwner,
	SUM(TO_INT(SUBSTR( Message, ADD(INDEX_OF(Message, '. Size in bytes: '), 17), SUB(SUB(INDEX_OF(Message, '; pages printed: '), INDEX_OF(Message, '. Size in bytes: ' )), 17) ) ) ) AS FileSizeTotal,
	SUM(TO_INT(TRIM(SUBSTR( Message, ADD(INDEX_OF(Message, '; pages printed: '), 17), SUB(SUB(STRLEN(Message), INDEX_OF(Message, '; pages printed: ' )), 17) ) ) ) ) AS PagesPrintedTotal
INTO PrintJobsLast2DaysByOwner.txt 
FROM System 
WHERE (EventID = 10 
	AND TimeGenerated >
		SUB( TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-03 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
	)
GROUP BY DatePrinted, FileOwner

Open in new window

0
atrevido
Asked:
atrevido
  • 2
  • 2
1 Solution
 
SharathData EngineerCommented:
check this.
SELECT t1.*, 
       SUM(TO_INT(SUBSTR(t1.Message,ADD(INDEX_OF(t1.Message,'. Size in bytes: '), 
                                        17),SUB(SUB(INDEX_OF(t1.Message,'; pages printed: '),
                                                    INDEX_OF(t1.Message,'. Size in bytes: ')),
                                                17)))) 
         OVER(PARTITION BY DatePrinted,FileOwner ) AS FileSizeTotal, 
       SUM(TO_INT(TRIM(SUBSTR(t1.Message,ADD(INDEX_OF(t1.Message,'; pages printed: '), 
                                             17),SUB(SUB(STRLEN(t1.Message),INDEX_OF(t1.Message,'; pages printed: ')),
                                                     17))))) 
         OVER(PARTITION BY DatePrinted,FileOwner ) AS PagesPrintedTotal 
  INTO PrintJobsLast2DaysByOwner.txt 
  FROM (SELECT TO_STRING(t1.TimeGenerated,'yyyy-MM-dd') AS DatePrinted, 
               SUBSTR(Message,ADD(INDEX_OF(t1.Message,' owned by '),10), 
                      SUB(SUB(INDEX_OF(t1.Message,' was printed on '),INDEX_OF(t1.Message,' owned by ')),
                          10)) AS FileOwner, 
               t1.* 
          FROM System AS t1 
         WHERE (EventID = 10 
                AND TimeGenerated > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()),TIMESTAMP('0000-01-03 00:00:00','yyyy-MM-dd hh:mm:ss')))) AS t1

Open in new window

0
 
atrevidoAuthor Commented:
that didn't work

C:\Program Files\Log Parser 2.2>LogParser.exe -i:EVT -o:TSV file:test.sql
Error: Syntax Error: <from-clause>: expecting FROM keyword instead of token '*,'

but i figured at least the 3 day window out.

SELECT
      TO_STRING(TimeGenerated, 'yyyy-MM-dd') AS DatePrinted,
      SUBSTR( Message, ADD(INDEX_OF(Message, ' owned by '), 10), SUB(SUB(INDEX_OF(Message, ' was printed on '), INDEX_OF(Message, ' owned by ' )), 10) )  AS FileOwner,
      SUM(TO_INT(SUBSTR( Message, ADD(INDEX_OF(Message, '. Size in bytes: '), 17), SUB(SUB(INDEX_OF(Message, '; pages printed: '), INDEX_OF(Message, '. Size in bytes: ' )), 17) ) ) ) AS FileSizeTotal,
      SUM(TO_INT(TRIM(SUBSTR( Message, ADD(INDEX_OF(Message, '; pages printed: '), 17), SUB(SUB(STRLEN(Message), INDEX_OF(Message, '; pages printed: ' )), 17) ) ) ) ) AS PagesPrintedTotal
INTO PrintJobsFeb4ByOwner.txt
FROM System
WHERE (EventID = 10
      AND TimeGenerated BETWEEN TO_TIMESTAMP('2011-02-04 17:00:00', 'yyyy-MM-dd hh:mm:ss') AND TO_TIMESTAMP('2011-02-07 08:00:00', 'yyyy-MM-dd hh:mm:ss')

      )
GROUP BY DatePrinted, FileOwner
0
 
SharathData EngineerCommented:
may be this?
SELECT t1.*, 
       SUM(TO_INT(SUBSTR(t1.Message,ADD(INDEX_OF(t1.Message,'. Size in bytes: '), 
                                        17),SUB(SUB(INDEX_OF(t1.Message,'; pages printed: '),
                                                    INDEX_OF(t1.Message,'. Size in bytes: ')),
                                                17)))) 
         OVER(PARTITION BY t1.DatePrinted,t1.FileOwner ) AS FileSizeTotal, 
       SUM(TO_INT(TRIM(SUBSTR(t1.Message,ADD(INDEX_OF(t1.Message,'; pages printed: '), 
                                             17),SUB(SUB(STRLEN(t1.Message),INDEX_OF(t1.Message,'; pages printed: ')),
                                                     17))))) 
         OVER(PARTITION BY t1.DatePrinted,t1.FileOwner ) AS PagesPrintedTotal 
  INTO PrintJobsLast2DaysByOwner.txt 
  FROM (SELECT TO_STRING(t1.TimeGenerated,'yyyy-MM-dd') AS DatePrinted, 
               SUBSTR(Message,ADD(INDEX_OF(t1.Message,' owned by '),10), 
                      SUB(SUB(INDEX_OF(t1.Message,' was printed on '),INDEX_OF(t1.Message,' owned by ')),
                          10)) AS FileOwner, 
               t1.* 
          FROM System AS t1 
         WHERE (EventID = 10 
                AND TimeGenerated > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()),TIMESTAMP('0000-01-03 00:00:00','yyyy-MM-dd hh:mm:ss')))) AS t1

Open in new window

0
 
atrevidoAuthor Commented:
I partially answered the question and the first attempt by the expert had many syntax errors.  Thanks for your help, you sent me in the right direction
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now