Valuable mappings for TMG logging into an external database

Valuable mappings for TMG logging into an external database

  • Comments 4
  • Likes

After you configure TMG to log into a SQL database, you may have noticed, that some logging fields like Malware Inspection Reason, IPS Scan result or URL Category, do not contain the string that can see in the live logging or in the integrated TMG Reports (see screenshot below), but just a number.

clip_image002

TMG only logs those numbers, because it’s way more faster to query and index numbers, and numbers take significantly less space in the data base.

The problem you run into, when you want to create any reports based on the data TMG did log, is quite obvious… where do I get the information which number represents which value?

Good news is, that most of this information is very well documented in the TMG SDK:

URL Categorization Reason

SQL value

Description

0

Not categorized

1

From overrides (user-defined)

2

From cache

3

From Webservice

4

Failed - URL filtering is disabled

5

Failed - No information available

6

Failed – No connection to Microsoft Reputation Service (MRS)

7

Failed – Microsoft Reputation Service (MRS) down

For detailed information: http://msdn.microsoft.com/en-us/library/ff796883.aspx

Malware Inspection Threat Level

SQL Value

Description

0

No threat detected

1

Threat level low

2

Threat level medium

3

Threat level high

4

Threat level severe

For detailed information: http://msdn.microsoft.com/en-us/library/dd447138.aspx

Malware Inspection Action Reason

SQL Value

Description

0

No reason was found to perform any action during malware inspection.

1

No malware was detected during malware inspection.

2

No remedial action was performed during malware inspection because low and medium threats are allowed.

3

The HTTP response was blocked because it was found to contain an infected file.

4

The HTTP response was blocked because it was found to contain suspicious content.

5

The HTTP response was blocked because it was found to contain an encrypted file.

6

The HTTP response was blocked because it was found to contain an archive whose achive depth level exceeded the user-defined maximum.

7

The HTTP response was blocked because it was found to contain a file whose size exceeded the user-defined maximum file size.

8

The HTTP response was blocked because it was found to contain an achive whose unpacked size exceeded the maximum.

9

The HTTP response was blocked because it was found to contain a file with unknown encoding.

10

The HTTP response was blocked because it was found to contain a corrupted file.

11

The HTTP response was blocked because the scanning time exceeded the user-defined maximum.

12

The HTTP response was blocked because the storage space limit for malware inspection was exceeded.

13

The HTTP response was blocked because an unsupported format was detected during malware inspection.

14

The HTTP response was blocked because it was found to contain a status not requested during malware inspection.

15

The HTTP response was blocked for another unspecified reason during malware inspection.

16

The HTTP response was allowed because malware inspection is disabled.

17

The HTTP response was allowed because malware inspection is disabled for the matching policy rule.

18

The HTTP response was allowed because malware inspection is disabled for the matching Web chaining rule.

19

The HTTP response was allowed because the destination is included in the list of malware inspection exceptions.

20

The HTTP response was allowed because it originated from a proxy server.

21

The HTTP response was allowed because it was servered by the Malware Insepction Filter.

22

The HTTP response was allowed because the request/response pair was identified as exempted protocol messages.

23

The HTTP response was allowed because it was found to be a 200 response to a CONNECT request.

24

The HTTP response was allowed because it was scanned before being routed by the Cache Array Routing Protocol (CARP).

25

The HTTP response was allowed because the source is included in the list of malware insepction exceptions.

26

The HTTP response was allowed because the folder containing the malware inspection definitions is not specified.

27

The HTTP response was blocked because it was a range response that did not include a range specification.

For detailed information: http://msdn.microsoft.com/en-us/library/dd447105.aspx

Malware Inspection Action

SQL value

Description

0

No action

1

Allowed

2

Cleaned

3

Blocked

For detailed information: http://msdn.microsoft.com/en-us/library/dd447104.aspx

IPS Scan Result

SQL value

Description

0

Unknown

1

Inspected – no threat detected

2

Blocked

3

Detected, but not blocked

For detailed information: http://msdn.microsoft.com/en-us/library/dd436151.aspx

Unfortunately Microsoft didn’t provide any documentation about the URLCategories … until now ;-)

In the following you can see the list of all mappings for the URL Categories used in TMG.

It’s possible, that Microsoft extends these categories in the future, however if Microsoft adds categories, these will be added at the end of the list, in order to prevent that the current mappings become invalid.

SQL value

URL Category Name

0

"Unknown"

1

"Alcohol"

2

"Anonymizers"

3

"Art/Culture/Heritage"

4

"Blogs/Wiki"

5

"Botnet"

6

"Chat"

7

"Child Friendly Materials"

8

"Criminal Activities"

9

"Dating/Personals"

10

"Digital Postcards"

11

"Dubious"

12

"Edge Content Servers/Infrastructure"

13

"Education/Reference"

14

"Employment"

15

"Fashion/Beauty"

16

"Financia"

17

"Forum/Bulletin Boards"

18

"Free Hosting"

19

"Gambling"

20

"Games"

21

"General Business"

22

"General Entertainment"

23

"Government/Military"

24

"Hacking/Computer Crime"

25

"Hate/Discrimination"

26

"Health"

27

"Humor/Comics"

28

"Illegal Drugs"

29

"Internet Services"

30

"Legal Services & Reference"

31

"Special Interests"

32

"Malicious"

33

"Mature Content"

34

"Media Sharing"

35

"Motor Vehicles"

36

"News"

37

"Non-Profit/Advocacy/NGO"

38

"Nudity"

39

"Obscene/Tasteless"

40

"Online Communities"

41

"Online Trading/Brokerage"

42

"P2P/File Sharing"

43

"Parked Domain"

44

"Personal Network Storage"

45

"Phishing"

46

"Politics/Opinion"

47

"Pornography"

48

"Portal Sites"

49

"Public Information"

50

"Real Estate"

51

"Recreation/Hobbies"

52

"Religion/Ideology"

53

"Remote Access"

54

"Restaurants/Dining"

55

"School Cheating Information"

56

"Search Engines"

57

"Self Defense"

58

"Shareware/Freeware"

59

"Shopping"

60

"Social Opinion"

61

"Spam URLs"

62

"Sports"

63

"Spyware/Adware"

64

"Streaming Media"

65

"Technical Information"

66

"Tobacco"

67

"Trave"

68

"Usenet News"

69

"Violence"

70

"Weapons"

71

"Web Ads"

72

"Web E-mai"

73

"Web Phone"

74

"Web-based Productivity Applications"

75

"Liability"

76

"Productivity"

77

"Security"

78

"Adult Lifestyle"

79

"Bandwidth"

80

"Business"

81

"Communication"

82

"Entertainment"

83

"Fraud/Crime"

84

"General Productivity"

85

"Information Technology"

86

"Lifestyles"

87

"Mature/Violent"

88

"Misc."

89

"News/Reports"

90

"Pornography/Nudity"

91

"Purchasing"

92

"Risk"

93

"Threats"

I hope this information will help configuring your reporting!

Author

Philipp Sand

Microsoft CSS Forefront Security Edge Team

Technical Reviewer

Thomas Detzner

Escalation Engineer - Microsoft CSS Forefront Security Edge Team

Comments
  • That's good to know, but quite tedious to maintain. I understand the technical reasons for logging only the numerical values, but why not store the mappings in a separate table in the SQL database so that they can be easily JOINed in custom reports? The table could be updated through Service Packs etc.

  • That would be "practical" therefore I don't expect that solution be implemented. Instead we will re-invent the wheel everytime a service pack or release arrives.

  • Another field that is converted to a number is Action.  This uses the fpcAction enumeration defined at msdn.microsoft.com/.../ff824053(v=vs.85).aspx.  Oddly enough, this is stored inefficiently as a varchar(32) as opposed to an int..

    It would be nice if other fields were normalized as well (such as UrlDestHost - varchar(255), DestHost - varchar(255), ClientAgent - varchar(128) and ClientUserName - nvarchar(514), to name a few) in order to save space.  SQL logging takes significantly more space than text logging.  As a result, I'm writing a utility to load text logs into a much more normalized logs database for querying.  

  • Hi all,    can somebody tell me, how can I create logging filter for overrides web pages?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment