Additional Reporting

[MADCookie]

I searched around the discussions for "report", "reporting", "sql" and
"xml". I couldn't find anything so I'm starting a new discussion.

How do you all report your information? Specifically, I'm
troubleshooting a weird error on my web site where I getting a ton of
404 errors. I know ELMAH has all the data that I need, but I'm a
little unclear on how to "ask" ELMAH for the data. I would like to
mine out of it all the errors from a specific IP (REMOTE_ADDR).

I can very easily query the database for all the 404 status code
errors, but using the AllXml column is difficult for me. How do you
all use the AllXml column? Do you "filter" it? How?

Please help. Maybe you could point me in the right direction. I just
don't want to waste my time thrashing.

thanks in advance,
mike

[James_2JS]

Hi Mike,

You could try something like this:

select * from dbo.elmah_error
where statuscode = 404
and allxml like '%string="xxx.xxx.xxx.xxx"%'

I'm assuming that the IP address you are interested in is only ever
going to come in as the REMOTE_ADDR... in which case this will work
fine!

You don't say which version of SQL Server you are using...
If you are using SQL 2005, you *might* be able to cast AllXml as XML
and then do an XPath query on it...
Something like this might work (NB I haven't got a 2005 setup here to
test this out!!):

select * from dbo.elmah_error
where statuscode = 404
and (cast allxml as xml).exist('/error/serverVariables/item/
@name[.="REMOTE_ADDR"]/value/@string[.="xxx.xxx.xxx.xxx"]') = 1

Cheers,

James

[Atif Aziz]

> How do you all report your information?

> but I'm a
> little unclear on how to "ask" ELMAH for the data. I would like to
> mine out of it all the errors from a specific IP (REMOTE_ADDR).

The way I usually do this is download the entire log as CSV. Then I have a small script that fetches the XML of each error and enhances the CSV with additional columns from the XML. This works independent of the log implementation you're using. Finally, I use Excel or MS LogParser for mining the CSV. If it would help then I can publish the script.

- Atif

[MADCookie]

Wow. This is great! I knew I was coming to the right place for aid.

I am using MS SQL Server 2005. Unfortunately, I'm getting this error.
Have you seen "bad" xml before? My feeling is that I need to escape
that character somehow.

XML parsing: line 141, character 82, illegal xml character

[MADCookie]

I found the row that I think has bad XML. Here is the snippet of the
querystring

<queryString>
<item name="404;http://www.ashcompanies.com:80/normal_html.cgi?
file">
<value string="&lt;script&gt;alert(&quot;QualysGS&quot;)&lt;/
script&gt;&#x0;" />
</item>
</queryString>

Visual Studio tells me &#x0; is illegal.

I am using ELMAH 2.0.50727.42. I have never had any troubles.

[Atif Aziz]

> Visual Studio tells me &#x0; is illegal.

That is indeed an invalid character as per XML spec. The valid ranges defined are:

Char ::= #x9 | #xA | #xD
| [#x20-#xD7FF]
| [#xE000-#xFFFD]
| [#x10000-#x10FFFF]

See http://www.w3.org/TR/REC-xml#charsets for more.

Last time I checked, there was no way to get SQL Server to accept invalid XML characters as you can do in .NET via XmlReaderSettings.CheckCharacters. One workaround may be, however, to remove such characters using string replacement before treating the AllXml column as XML.

- Atif

-----Original Message-----
From: el...@googlegroups.com [mailto:el...@googlegroups.com] On Behalf Of MADCookie
Sent: Thursday, August 28, 2008 9:19 PM
To: ELMAH

[MADCookie]

I'm adding onto this thread to help out others. I can't count the
number of times I'm searching for a solution and people don't follow
up with the answers.

Here is a great SQL script that will do a Find / Replace for the ntext
datatype used in column AllXml. I used the script to replace the
invalid XML. BTW, I'm choose a valid value from thin air.

Here is the URL I found this gem:
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html



DECLARE
@TextPointer BINARY(16),
@TextIndex INT,
@oldString NVARCHAR(32), -- change to VARCHAR
@newString NVARCHAR(32), -- change to VARCHAR
@lenOldString INT,
@currentDataID uniqueidentifier

SET @oldString = N'&#x0;'; -- remove N
SET @newString = N'&#xD;'; -- remove N

IF CHARINDEX(@oldString, @newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN
SET @lenOldString = DATALENGTH(@oldString)/2; -- remove /2

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
ErrorId
FROM
dbo.ut_ELMAH_Error
WHERE
PATINDEX('%'+@oldString+'%', allxml) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @currentDataID;

WHILE (@@FETCH_STATUS = 0)
BEGIN

SELECT
@TextPointer = TEXTPTR(allxml),
@TextIndex = PATINDEX('%'+@oldString+'%', allxml)
FROM
dbo.ut_ELMAH_Error
WHERE
ErrorId = @currentDataID;

WHILE
(
SELECT
PATINDEX('%'+@oldString+'%', allxml)
FROM
dbo.ut_ELMAH_Error
WHERE
ErrorId = @currentDataID
) > 0
BEGIN
SELECT
@TextIndex = PATINDEX('%'+@oldString+'%', allxml)-1
FROM
dbo.ut_ELMAH_Error
WHERE
ErrorId = @currentDataID;

UPDATETEXT dbo.ut_ELMAH_Error.allxml @TextPointer
@TextIndex @lenOldString @newString;
END

FETCH NEXT FROM irows INTO @currentDataID;
END

CLOSE irows;

DEALLOCATE irows;
END

On Aug 28, 12:33 pm, Atif Aziz <Atif.A...@skybow.com> wrote:
> > Visual Studio tells me &#x0; is illegal.
>
> That is indeed an invalid character as per XML spec. The valid ranges defined are:
>
> Char ::= #x9 | #xA | #xD
>          | [#x20-#xD7FF]
>          | [#xE000-#xFFFD]
>          | [#x10000-#x10FFFF]
>

> Seehttp://www.w3.org/TR/REC-xml#charsetsfor more.

[MADCookie]

Forgive my ignorance. I'm in the crash course of XPath / SQL 2005.

Thank you James for your SQL, but it did work out-of-box. Here is the
SQL that worked for me.

SELECT *
FROM dbo.ut_ELMAH_Error
WHERE
(cast (allxml as xml)).exist('/error/serverVariables/
item[@name="REMOTE_ADDR"]/value[@string="xxx.xxx.xxx.xxx"]') = 1

Lower in this thread you'll see how I had to overcome some invalid
XML. What I ended up doing was adding another column to my ELMAH table
with a XML datatype. After Find / Replace invalid XML, I updated ELMAH
setting that new XML column to the casted value of AllXml. This way
I'm casting once and not every single time I want to query more.

Thanks everyone for your help. I've learned a lot.

On Aug 27, 3:36 pm, James_2JS <jamesdrisc...@btinternet.com> wrote:

[Atif Aziz]

>>
I'm adding onto this thread to help out others. I can't count the
number of times I'm searching for a solution and people don't follow
up with the answers.
<<

Mike, thanks for sharing all your findings. Would you be interested in publishing your solution as a wiki article on the project site?

[James Driscoll]

Hi Atif,

Looks like this issue http://code.google.com/p/elmah/issues/detail?id=43 is
part of the problem.
I see you made a comment about just going with it as is...

"On second thoughts, it looks like that it may be best to resort to
XmlWriterSettings.CheckCharacters for now in order to remain compatible with
.NET
1.x builds as well as error objects that have already been formatted and
persisted
based on the current behavior."

However, maybe it's not such a problem...
It looks to me like the 404 error that Mike was getting is some sort of
injection hack.
Would it be so bad to find invalid XML characters and then encode on the
way...
And then to leave them as is on the way out??
In this instance, the character could go through as &amp;#x0; instead of
&#x0;

What do you think??

Cheers,

James

[Atif Aziz]

Hi James,

>>
Would it be so bad to find invalid XML characters and then encode on the way...
And then to leave them as is on the way out??
<<

Not sure I get you here. The problem is sanitizing existing logs that may have those invalid characters. It won't help folks like Mike who were trying to process the data directly from SQL.

[James Driscoll]

Hi Atif,

Yes, Mike's problem is in an existing log...
But, shouldn't we be looking at why we've got an existing log that is
invalid in the first place??
We can't do anything about what's already been logged, but we can stop it
happening again!

[Atif Aziz]

Mike,

It might be simpler to use REPLACE with the ad-hoc query to sanitize the XML. I've merged your reporting and sanitization requirements together with James's suggestions to come up with a single SQL that will do the job. The sample script extracts the REMOTE_ADDR, HTTP_USER_AGENT and HTTP_REFERRER server variables from the AllXml column and makes them available as additional columns in the final result set.

I've included the SQL script below and attached it to this post in case it gets re-formatted along the way. And in the event the attachment is removed by some gateway, you can also find it online at:
http://gist.github.com/7943

SELECT
[ErrorId],
[Application],
[Host],
[Type],
[Source],
[Message],
[User],
[StatusCode],
[TimeUtc],
[Sequence],
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="REMOTE_ADDR"]
/value
/@string)[1]',
'varchar(20)'), N'')
REMOTE_ADDR,
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="HTTP_USER_AGENT"]
/value
/@string)[1]',
'nvarchar(250)'), N'')
HTTP_USER_AGENT,
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="HTTP_REFERER"]
/value
/@string)[1]',
'nvarchar(2000)'), N'')
HTTP_REFERER
FROM (
SELECT
[ErrorId],
[Application],
[Host],
[Type],
[Source],
[Message],
[User],
[StatusCode],
[TimeUtc],
[Sequence],
CAST(
-- Remove characters invalid in XML
REPLACE(CAST([AllXml] AS NVARCHAR(MAX)), CHAR(0), '?')
AS XML) [AllXml]
FROM
[ELMAH_Error]) AS [Error]


Further suggestions welcomed. :)

[Atif Aziz]

>>
But, shouldn't we be looking at why we've got an existing log that is
invalid in the first place??
We can't do anything about what's already been logged, but we can stop it
happening again!
<<

I hear you now, James. Unfortunately, what makes the XML invalid are characters contained in .NET strings as they get written out. Question is how to best sanitize those strings without loss or mangling of information. Replace with a space or question mark? Perhaps we should open this as a separate thread or issue.