Hi all,
It might be worth creating a thread to help share tools. I'll start with a HTA for running a few SQL queries. Save below as "shhtool.hta" and run on the management server.
<html>
<head>
<title>SEC Shh/* Reports</title>
<hta:application id="SOPHOSSHHHTA" windowstate="normal" scroll="yes"/>
<script language="VBScript">
Sub Window_onLoad
window.resizeTo 1000,900
End Sub
'Constants
const adOpenDynamic = 2
const adLockReadOnly = 1
const HKEY_LOCAL_MACHINE = &H80000002
'language variables if need to change
CHOOSE_A_REPORT = "Please choose a report."
ERROR_MSG_TYPE = "Error"
CUSTOM_QUERY_RESULTS = "Custom Query Results."
CHECK_DB_CONNECTION = "Please check database connection values. "
ERROR_CODE_MSG = "Error code: "
ERROR_DESC_MSG = "Error Description: "
SQL_ERROR_SYN_MSG = "Please check syntax of SQL command. "
SAVE_TO_FILE = "Save to file"
COPY_TO_CLIPBOARD = "Copy to clipboard"
BACK_TO_TOP = "[Back to top]"
COULD_NOT_CREATE_FILE = "Could not create file. "
'Global Variables
dim strTitle
dim strHeaders
dim strSQL
dim objConn
dim objRsData
dim strConnectionKey
dim strConnectionName
'Set values for global variables:
strConnectionKey = "\Sophos\EE\Management Tools\"
strConnectionName = "DatabaseConnectionMS"
'==============================================================================================================================================================================
Function GetData(strReport)
if strReport = "1" then
msgbox CHOOSE_A_REPORT, 0, ERROR_MSG_TYPE
exit function
end if
SELECT case strReport
'Start of built in reports
'====================
'==============================================================================================================================================================================
case "AllComputers"
strTitle = "All managed computers missing fixed identity (javab-jd.ide)"
strHeaders = "<th>Computer</th>"
strSQL = "SELECT c.Name FROM dbo.Computers as c WHERE IDEList LIKE N'%agen-xuv%' AND IDEList NOT LIKE N'%javab-jd%' and c.managed = 1"
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 1 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "distictfilesdeleted"
strTitle = "All distinct files deleted"
strHeaders = "<th>FullFilePath</th>"
strSQL = "SELECT distinct t.FullFilePath FROM dbo.ThreatInstancesAll as t INNER JOIN dbo.ThreatEvents te ON t.ThreatInstanceID=te.ThreatInstanceID WHERE t.ThreatName LIKE N'Shh/%' AND te.ActionTaken = 103 ORDER BY t.FullFilePath"
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 1 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "ThreatCountsPerActionMoved"
strTitle = "Shh/* files moved per computer"
strHeaders = "<th>Computer</th><th>FullFilePath</th>"
strSQL = "SELECT c.Name , t.FullFilePath FROM " &_
" dbo.ThreatInstancesAll t INNER JOIN dbo.ThreatEvents te ON t.ThreatInstanceID=te.ThreatInstanceID INNER JOIN dbo.ComputersAndDeletedComputers c ON t.ComputerID = c.ID " &_
" WHERE t.ThreatName LIKE N'Shh/%' AND ActionTaken = 105 ORDER BY c.Name"
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 2 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "ThreatCountsPerActionDeleted"
strTitle = "Shh/* files deleted per computer"
strHeaders = "<th>Computer</th><th>FullFilePath</th>"
strSQL = "SELECT c.Name, FullFilePath FROM " &_
" dbo.ThreatInstancesAll t INNER JOIN dbo.ThreatEvents te ON t.ThreatInstanceID=te.ThreatInstanceID INNER JOIN dbo.ComputersAndDeletedComputers c ON t.ComputerID = c.ID " &_
" WHERE t.ThreatName LIKE N'Shh/%' AND ActionTaken = 103 ORDER BY c.Name"
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 2 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "ThreatCounts"
strTitle = "Total Shh/* threat counts in all States"
strHeaders = "<th>State</th><th>Count</th>"
strSQL = "select 'All' as ThreatReport, COUNT(*) as Number " &vbcrlf &_
"from ThreatInstancesAll as t with (nolock) where t.ThreatName like 'Shh/%' " &vbcrlf &_
"union " &vbcrlf &_
"select 'Outstanding=1', COUNT(*) as Number " &vbcrlf &_
"from ThreatInstancesAll as t with (nolock) where t.ThreatName like 'Shh/%' " &vbcrlf &_
"and Outstanding = 1 " &vbcrlf &_
"union " &vbcrlf &_
"select 'Outstanding=0', COUNT(*) as Number " &vbcrlf &_
"from ThreatInstancesAll as t with (nolock) where t.ThreatName like 'Shh/%' " &vbcrlf &_
"and Outstanding = 0"
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 2 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "ThreatByFullFilePathAndThreatName"
strTitle = "Top 500 Shh/* threats by file path"
strHeaders = "<th>FullFilePath</th><th>Count</th>"
strSQL = "select top 500 t.FullFilePath as CountByFullFilePathInstancesAll, COUNT(*) " &vbcrlf &_
"from ThreatInstancesAll as t with (nolock) where t.ThreatName like 'Shh/%'" &vbcrlf &_
"group by FullFilePath,threatname " &vbcrlf &_
"order by COUNT(*) DESC "
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 2 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
'==============================================================================================================================================================================
case "ThreatsByComputerName"
strTitle = "Top 500 Shh/* threats by computer"
strHeaders = "<th>Computer</th><th>Count</th>"
strSQL = "select top 500 c.Name as ComputerName, count(*) as ThreatInstancesAllCount " &vbcrlf &_
"from ThreatInstancesAll as t with (nolock) " &vbcrlf &_
"inner join ComputersAndDeletedComputers as c with (nolock) on c.ID=t.ComputerID where t.ThreatName like 'Shh/%'" &vbcrlf &_
"group by c.Name, c.DomainName " &vbcrlf &_
"order by COUNT(*) DESC "
sqltext.innertext = strSQL
headers.innertext = strHeaders
colcount.value = 2 ' number of columns returned
GetData2 colcount.value
'==============================================================================================================================================================================
case else
' error
end select
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function ResetReport()
ReportChooser.value = 1
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function CustomQ(strCSql, strHeadersC, strCols)
strTitle = CUSTOM_QUERY_RESULTS
strSQL = strCSql
strHeaders = strHeadersC
GetData2 strCols
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function SetupConnection()
on error resume next
dim strConnectionString
if Is64(".") then
strWow6432Node = "\WOW6432Node\"
else
strWow6432Node = ""
end if
strConnectionString = GetConnectionString()
'If fails to get a connection string
if strConnectionString = "-1" then
msgbox "Unable to obtain the connection string from the registry, please run tool on the SEC Server"
SetupConnection = "-1"
Exit Function
else
end if
connstring.innerhtml = "<font size=""-1"">Database connection string: '" & strConnectionString & "'</font>"
Set objConn = CreateObject("ADODB.Connection")
err.clear
objConn.Open strConnectionString, adOpenDynamic, adLockReadOnly
if err.number <> 0 then
msgbox CHECK_DB_CONNECTION & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG & err.description , 0, ERROR_MSG_TYPE
exit function
end if
err.clear
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function GetData2(intColumnsf)
on error resume next
dim intRecordCount
dim str1
dim c
intRecordCount = 0
if SetupConnection() = "-1" then
Exit Function
End if
Set objRsData = objConn.Execute(strSQL)
if err.number <> 0 then
msgbox SQL_ERROR_SYN_MSG & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG & err.description ,0, ERROR_MSG_TYPE
exit function
end if
str1 = "<p><span class=""repTitle"">" & strTitle & "</span>" &_
"<span align=""right"" id=""myContentCount""></span></p>" &_
"<table border=""1"" class=""tresults"">" & strHeaders
Do Until objRsData.EOF
str1 = str1 & "<tr>"
for c = 0 to intColumnsf - 1
strRSValue = objRsData(c)
str1 = str1 & "<td>" & strRSValue & " </td>"
next
str1 = str1 & "</tr>"
objRsData.moveNext
intRecordCount = intRecordCount + 1
loop
str1 = str1 & "</ul></table>"
myContent.innerHTML = str1
myFileSave.innerhtml = "<br><input name=""strFileName"" type=""text"" size=""30"" value=""SECToolReportOutput.txt"">" &_
"<input type=""button"" onclick=""OutputToFile(1)"" value="""&SAVE_TO_FILE&"""> " &_
"<Input type=""button"" onclick=""ClipBoard()"" value="""©_TO_CLIPBOARD&""">"
myBackToTop.innerhtml = "<p align=""right""><a href=""#TOP"">"&BACK_TO_TOP&"</a></p>"
myContentCount.innerhtml = " (" & intRecordCount &" records)"
set objRsData = nothing
set objConn = nothing
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function OutputToFile(strArea)
on error resume next
set objFso = CreateObject("scripting.FileSystemObject")
err.clear
set objFile = objFso.createtextfile(strFileName.value, TRUE, TRUE)
if err.number <> 0 then
msgbox COULD_NOT_CREATE_FILE & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG & err.description , 0, ERROR_MSG_TYPE
exit function
end if
err.clear
select case strArea
case 1
objFile.WriteLine myContent.innerText
case 2
objFile.WriteLine sgr.innerText
end select
objFile.close
set objFso = nothing
set objFile = nothing
End Function
'==============================================================================================================================================================================
'==============================================================================================================================================================================
Function GetConnectionString()
on error resume next
dim oReg, strValue
err.clear
Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
if err.number <> 0 then
msgbox "Error Number: " & err.number & " Error Description: " & err.description
GetConnectionString = "-1"
Set oReg = nothing
Exit Function
end if
oReg.GetStringValue HKEY_LOCAL_MACHINE,"SOFTWARE" & strWow6432Node & strConnectionKey, strConnectionName, strValue
if strValue <> "" then
GetConnectionString = strValue
else
GetConnectionString = "-1"
end if
Set oReg = nothing
End Function
'***********************************************************************************************************
Function Is64(strMachineName)
on error resume next
err.clear
dim objWMIService, objColSettings, strDesc, objProcessor
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strMachineName & "\root\cimv2")
Set objColSettings = objWMIService.ExecQuery ("SELECT AddressWidth FROM Win32_Processor")
if err.number <> 0 then
msgbox "Error Number: " & err.number & " Error Description: " & err.description
exit function
end if
For Each objProcessor In objColSettings
strDesc = objProcessor.AddressWidth
Next
if strDesc = "32" then
Is64 = false
end if
if strDesc = "64" then
Is64 = true
end if
Set objWMIService = nothing
set objColSettings = nothing
End Function
'***********************************************************************************************************
</script>
<script language="javascript" type="text/javascript">
function ClipBoard() {
holdtext.innerText = myContent.innerText;
Copied = holdtext.createTextRange();
Copied.execCommand("Copy");
}
function ClipBoardSGR() {
holdtext.innerText = sgr.innerText;
Copied = holdtext.createTextRange();
Copied.execCommand("Copy");
}
function showMe (it, box) {
var vis = (box.checked) ? "block" : "none";
document.getElementById(it).style.display = vis;
}
</script>
</head>
<body bgcolor="#ffffff" text="#000000" onload="ResetReport()">
<a name="TOP"></a>
<fieldset>
<legend>Shh/* Reports</legend>
<select size="1" name="ReportChooser" onChange="GetData(ReportChooser.Value)" id="rep">
<option value="1">Choose Report</option>
<option value="AllComputers">All managed computers missing the 'fix' ide</option>
<option value="ThreatCounts">Shh/* alert counts</option>
<option value="ThreatByFullFilePathAndThreatName">Shh/* alerts by filepath and counts</option>
<option value="ThreatsByComputerName">Shh/* alert counts per computer</option>
<option value="ThreatCountsPerActionMoved">Shh/* files moved per computer</option>
<option value="ThreatCountsPerActionDeleted">Shh/* files deleted per computer</option>
<option value="distictfilesdeleted">Shh/* distinct files deleted</option>
</select>
<label for="re">SQL Editor</label>
<input type="checkbox" name="c1" unchecked onclick="javascript:showMe('div1', this)" id="re">
</fieldset>
<br>
<div id="div1" style="display:block; display:none;">
<fieldset>
<legend>SQL Editor</legend>
SQL:<br>
<textarea name="sqltext" cols="100" rows="5"></textarea><br>
Table Headers:<br>
<textarea name="headers" cols="100" rows="5"></textarea><br>
Number of columns:<br>
<input type="text" value="" name="colcount" size="2">
<input type="button" value="Query!" onclick="CustomQ sqltext.innertext, headers.innertext, colcount.value"></fieldset>
</div>
<fieldset>
<legend>Report Operations</legend>
<div id="myFileSave">The ability to save report will appear here after running a report.</div>
<br><div id="connstring"></div>
</fieldset>
<fieldset>
<legend>Output</legend>
<div id="myContent"></div>
</fieldset>
<div id="myBackToTop"></div>
<textarea id="holdtext" STYLE="display:none;"></textarea>
</body>
</html>IMPORTANT NOTE:
The line in the code:
<input type="checkbox" name="c1" unchecked onclick="....
has
:
This should be a colon, i.e. :
Also describes some deployment methods using SEC.
Regards,
Jak
This thread was automatically locked due to age.