1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
| package com.sagem.serveurpfe.controleur;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.sagem.serveurpfe.resultatliste.Top10threatcategory;
import com.sagem.serveurpfe.resultatliste.Topthreatsources;
import com.sagem.serveurpfe.resultatliste.Topuserdetected;
import com.sagem.serveurpfe.resultatliste.Unwantedporgrams;
public class TopBean {
private List <Top10threatcategory> ts1 ;
private List <Topthreatsources> ts2;
private List <Topuserdetected> ts3;
private List <Unwantedporgrams> ts4;
public String init()
{
ts1= new ArrayList<Top10threatcategory>();
ts2= new ArrayList<Topthreatsources>();
ts3= new ArrayList<Topuserdetected>();
ts4= new ArrayList<Unwantedporgrams>();
try{
System.out.println("connexion en cours...");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:sqlserver://10.154.249.173;user=sa;password=$agem001;database=ePO4_BENSRV33");
System.out.println("Connexion effective !");
Statement stmt = con.createStatement();
ResultSet rs1 = stmt.executeQuery("select top 1000 count(*) as 'count', [EPOEvents].[ThreatType], [EPOEvents].[ThreatName]from [EPOEvents] where ( ( ([EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ([EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ([EPOEvents].[DetectedUTC] between '2015-02-27T14:44:04.306' and '2015-03-06T14:44:04.306' ) and ( ([EPOEvents].[ThreatType] = N'app' ) or ([EPOEvents].[ThreatType] = N'app_adware' ) or ([EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ([EPOEvents].[ThreatType] = N'app_pua' ) or ([EPOEvents].[ThreatType] = N'app_puo' ) or ([EPOEvents].[ThreatType] = N'app_package' ) or ([EPOEvents].[ThreatType] = N'app_rootkit' ) or ([EPOEvents].[ThreatType] = N'app_P2P' ) or ([EPOEvents].[ThreatType] = N'app_IM' ) or ([EPOEvents].[ThreatType] = N'app_keylogger' ) or ([EPOEvents].[ThreatType] = N'app_pwcracker' ) or ([EPOEvents].[ThreatType] = N'app_dialer' ) or ([EPOEvents].[ThreatType] = N'app_spyware' ) or ([EPOEvents].[ThreatType] = N'virus' ) or ([EPOEvents].[ThreatType] = N'comp' ) or ([EPOEvents].[ThreatType] = N'trojan' ) or ([EPOEvents].[ThreatType] = N'joke' ) or ([EPOEvents].[ThreatType] = N'test' ) ) ) group by [EPOEvents].[ThreatType], [EPOEvents].[ThreatName] order by 'count' desc, [EPOEvents].[ThreatType] asc,[EPOEvents].[ThreatName] asc");
ResultSet rs2 = stmt.executeQuery("select count(*) as 'count', [EPOEvents].[TargetUserName] from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ( [EPOEvents].[DetectedUTC] between '2015-02-27T14:42:29.555' and '2015-03-06T14:42:29.555' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) ) group by [EPOEvents].[TargetUserName] order by 'count' desc, [EPOEvents].[TargetUserName] asc");
ResultSet rs3 = stmt.executeQuery("select count(*) as 'count', [EPOEvents].[SourceHostName] from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ( [EPOEvents].[DetectedUTC] between '2015-03-06T06:45:02.025' and '2015-03-06T14:45:02.025' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) and ( ( [EPOEvents].[SourceHostName] is null or ( [EPOEvents].[SourceHostName] <> N'_' )) and ( not ( [EPOEvents].[SourceHostName] is null or ltrim( rtrim( [EPOEvents].[SourceHostName] ) ) = '' ) ) ) ) group by [EPOEvents].[SourceHostName] order by 'count' desc, [EPOEvents].[SourceHostName] asc");
ResultSet rs4 = stmt.executeQuery("select count(*) as 'count', datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.year' , datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.dayofyear' from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) ) ) and ( [EPOEvents].[DetectedUTC] between '2015-02-27T14:40:43.883' and '2015-03-06T14:40:43.883' ) ) group by datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ), datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) order by datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) desc, datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) desc");
while (rs1.next()) {
Top10threatcategory tr1= new Top10threatcategory();
System.out.print(rs1.getString(0));
tr1.setNbthreat(rs1.getString(0));
tr1.setTypethreat(rs1.getString(1));
tr1.setNomthreat(rs1.getString(2));
ts1.add(tr1);
}
/*
while (rs2.next()) {
Topthreatsources tr2= new Topthreatsources();
tr2.setNbthreat(rs2.getString(0));
tr2.setSourceName(rs2.getString(1));
ts2.add(tr2);
}
while (rs3.next()) {
Topuserdetected tr3= new Topuserdetected();
tr3.setNbthreat(rs3.getString(0));
tr3.setUsername(rs3.getString(1));
ts3.add(tr3);
}
while (rs4.next()) {
Unwantedporgrams tr4= new Unwantedporgrams();
tr4.setNbthreat(rs4.getString(0));
tr4.setYearofthreat(rs4.getString(1));
tr4.setMonthofthreat(rs4.getString(2));
ts4.add(tr4);
}
*/
} catch (Exception e) {
e.printStackTrace();
System.out.println("erreur de connexion !");
}
return null;
}
public List<Top10threatcategory> getTs1() {
init();
return ts1;
}
public void setTs1(List<Top10threatcategory> ts1) {
this.ts1 = ts1;
}
public List<Topthreatsources> getTs2() {
return ts2;
}
public void setTs2(List<Topthreatsources> ts2) {
this.ts2 = ts2;
}
public List<Topuserdetected> getTs3() {
return ts3;
}
public void setTs3(List<Topuserdetected> ts3) {
this.ts3 = ts3;
}
public List<Unwantedporgrams> getTs4() {
return ts4;
}
public void setTs4(List<Unwantedporgrams> ts4) {
this.ts4 = ts4;
}
} |
j'ai un probléme pour afficher les résultats des requêtes sql (la base de donnée sql server 2005)
Répondre avec citation
Partager