|
|
|||||||
I have been tasked with managing a file crawler. In effort of being as efficient/lazy as possible, I am trying to use Windows Search as the actual crawler and then just quering the data out of it. Using some working SQL DB code, I have managed to get it to query the search index. Code: $cn = CreateObject("ADODB.Connection") $cmd = CreateObject("ADODB.Command") $rs = CreateObject("ADODB.RecordSet") $cn.connectionstring = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" $cn.open $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmdtxt = "SELECT System.ItemFolderPathDisplay, System.ItemName, System.ItemTypeText, System.Size FROM SystemIndex WHERE SCOPE='file:c:\'" $cmd.commandtext = $cmdtxt $rs.open($cmd) $rs.MoveFirst While Not $rs.eof ? $rs.fields.item("System.ItemFolderPathDisplay").value ? $rs.fields.item("System.ItemName").value ? $rs.fields.item("System.ItemTypeText").value ? $rs.fields.item("System.Size").value ? $rs.moveNext Loop $rs.close $cn.close Much of the details are found here: http://msdn.microsoft.com/en-us/library/bb419046(VS.85).aspx http://code.msdn.microsoft.com/windowssearch I guess what I need is: How do I search for a specific filename or extention (*.doc, or *.xls) How do I add a filetype to the index (if I wanted it to index a custom file type) |
||||||||
|
|
|||||||
I can't get the wildcards to work... $cmdtxt = "SELECT System.ItemFolderPathDisplay, System.ItemName, System.ItemTypeText, System.Size FROM SystemIndex WHERE Contains(System.filename,'.xls?')" I have replaced the ? with each of these(* _ %) and it still only returns just the xls files, not including the xlsx... (unless I explicitly put xlsx) |
||||||||
|
|
|||||||
Speaking from a position of SQL ignorance, why the wildcard? Surely Contains() means "has the substring", so Contains(System.filename,'.xls') will match foosheet.xls and barsheet.xlsx as both have the .xls substring. It'd actually be trickier to limit the query to only files ending in .xls |
||||||||
|
|
|||||||
Rad, Was able to Get this to work in VBScript... I borrowed some code from - http://www.devx.com/VistaSpecialReport/Article/33767/1763/page/2 Code: Set objConnection = CreateObject("ADODB.Connection") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" sql = "SELECT System.ItemFolderPathDisplay, " sql = sql & "System.ItemNameDisplay, " sql = sql & " System.ItemType FROM " sql = sql & " systemindex WHERE CONTAINS(System.FileName,'%0%')" sql = sql & " AND System.ItemNameDisplay LIKE '%.doc%'" objRecordSet.Open sql, objConnection objRecordSet.MoveFirst Do Until objRecordset.EOF text = objRecordset.Fields.Item("System.ItemFolderPathDisplay") text = text & objRecordset.Fields.Item("System.ItemNameDisplay") text = text & objRecordset.Fields.Item("System.ItemType") Wscript.Echo text objRecordset.MoveNext Loop Tried to convert to KiXtart And is Not working.. Code: $cn = CreateObject("ADODB.Connection") $cmd = CreateObject("ADODB.Command") $rs = CreateObject("ADODB.RecordSet") $cn.connectionstring = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" $cn.open $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmdtxt = "SELECT System.ItemFolderPathDisplay, " $cmdtxt = $cmdtxt + "System.ItemName, " $cmdtxt = $cmdtxt + "System.ItemTypeText, " $cmdtxt = $cmdtxt + "System.Size " $cmdtxt = $cmdtxt + "FROM SystemIndex " $cmdtxt = $cmdtxt + "systemindex WHERE CONTAINS(System.FileName,'%0%') " $cmdtxt = $cmdtxt + "AND System.ItemNameDisplay LIKE '%.doc%'" $cmd.commandtext = $cmdtxt $rs.open($cmd) $rs.MoveFirst While Not $rs.eof ? $rs.fields.item("System.ItemFolderPathDisplay").value ? $rs.fields.item("System.ItemName").value ? $rs.fields.item("System.ItemTypeText").value ? $rs.fields.item("System.Size").value ? $rs.moveNext Loop $rs.close $cn.close HTH, Kent |
||||||||
|
|
|||||||
I have this working to a degree and have another "problem" Code: $PC = 'sn999999' ; No backslashes $cn = CreateObject("ADODB.Connection") $cmd = CreateObject("ADODB.Command") $rs = CreateObject("ADODB.RecordSet") $cn.connectionstring = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" $cn.open If @error ? 'Unable to connect to Windows Search' endif $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmdtxt = "SELECT System.ItemFolderPathDisplay, System.ItemName FROM "+$pc+".SystemIndex WHERE scope='file://"+$pc+"/c' and Contains(System.FileName,'.url')" $cmd.commandtext = $cmdtxt $rs.open($cmd) If @error ? @serror endif $rs.MoveFirst $count = 0 While Not $rs.eof ? $rs.fields.item("System.ItemFolderPathDisplay").value ? $rs.fields.item("System.ItemName").value ? $count =$count +1 $rs.moveNext Loop ? $count $rs.close $cn.close The remote query works, but it must be a real share, not an administrative share. I made a share called "c" which works, but C$ doesn't... which is kind of a PITA. |
||||||||
|
|
|||||||
Is it documented that it doesn't work that way or are you just having trouble getting C$ to work? |
||||||||
|
|
|||||||
I can't find any documentation that states the admin shares don't work, but... http://social.msdn.microsoft.com/Forums/...91-1cd64fa0db8b I did find this: Quote: Querying Remotely The following are troubleshooting tips to help you query a remote machine. When I query a remote machine, nothing is found. Remote queries fail when the machine name is longer than 15 bytes. This is a known issue, and the only workaround is to give the remote machine a shorter name. When I query a remote machine as admin, nothing is found. In order to query a remote machine, the folder you want to search must be shared even if you access it as admin. Sharing is a prerequisite for querying. First, share the folder to yourself, and then you can query it remotely. http://technet.microsoft.com/en-us/library/cc771814(WS.10).aspx |
||||||||
|
|
|||||||
here is a basic script to get a basic file query after creating the remote share and removing the share when complete Code: break on $PC = 'pcname' ; no backslashes $SearchStr = '.xls' $objWMIService = GetObject("winmgmts:\\" +$pc+ "\root\cimv2") ; ****** create remote share ****** $objNewShare = $objWMIService.Get("Win32_Share") $errReturn = $objNewShare.Create("C:\", "C","0","2", "Temporary access for remote Windows Search.") if not @error ; ****** query Windows Search ****** $cn = CreateObject("ADODB.Connection") $cmd = CreateObject("ADODB.Command") $rs = CreateObject("ADODB.RecordSet") $cn.connectionstring = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" $cn.open If @error ? 'Unable to connect to Windows Search' endif $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmdtxt = "SELECT System.ItemFolderPathDisplay, System.ItemName FROM "+$pc+".SystemIndex WHERE scope='file://"+$pc+"/c' and Contains(System.FileName,'"+$SearchStr+"') order by System.ItemFolderPathDisplay" $cmd.commandtext = $cmdtxt $rs.open($cmd) If @error ? 'bad parameter in search query' endif $count = 0 $rs.MoveFirst While Not $rs.eof $fullpath = '' + $rs.fields.item("System.ItemFolderPathDisplay").value +'\'+$rs.fields.item("System.ItemName").value ? $count ? $fullpath ? $count =$count +1 $rs.moveNext Loop ? "Number of hits: " + $count $rs.close $cn.close ; ****** delete remote share ****** $colShares = $objWMIService.ExecQuery("Select * from Win32_Share Where Name = 'C'") For Each $objShare in $colShares $nul = $objShare.Delete Next endif |
||||||||
|
|
|||||||
Hmm, so - if I want to search for all the .MP3 files on our file server - and delete them, this might be the faster way to locate them? We currently have a zero-tolerance for media on the user's home shares, and the kix script I have now enumerates the folders looking for about a dozen types of files. This might be just the kind of thing I need to update that script... Glenn |
||||||||
|
|
|||||||
the BEST thing about this is that the time it takes to run is virtually identical regardless of the the number of results. 5 hits or 500 is damn fast |
||||||||
|
|
|||||||
Sweet - I am definitely giving this a whirl. The tool I wrote takes about 90 minutes to run every night, and has about 96% success in finding things. Right now, Windows Search might actually be harder to use right now, since our data structure is pretty poor. Each of the 500+ departments has a pair of folders that are shared - one for the department share, and one for users in the department. Media is allowed in the department share (ad copy, resort images, etc) but not in the user's home share. The tool has to enumerate each department folder and then only the user subfolder. I'm in the process of changing the structure while migrating to storage on the new SAN. There will be one share for Departments and one for Users, so now I can specify the \Users root and locate all the inappropriate media. I think the other advantage of this is is that it reports media inside of compressed folders (ZIP files), which the previous tool did not find. Since I need to rewrite this to support the new structure, this is perfect timing for me. Thanks, Rad!! Glenn |
||||||||
|
|
|||||||
This is the some of the form code that shouldn't be too difficult to make workable (i'm pulling it from a larger project, so it will not work as is here) Code: ;region WinSearch Subform ;~~< WinSearchForm1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchForm1 $WinSearchForm1 = $System.Form() $WinSearchForm1.Size = 463, 395 $WinSearchForm1.Text = "Search Windows Search" ;~~< WinSearchStatusBar1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchStatusBar1 $WinSearchStatusBar1 = $WinSearchForm1.Controls.StatusBar() $WinSearchStatusBar1.Text = "Search Windows Search" $WinSearchStatusBar1.Size = 447, 22 $WinSearchStatusBar1.Location = 0, 221 $WinSearchStatusBar1.Dock = 2 ;~~< WinSearchPanel1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchPanel1 $WinSearchPanel1 = $WinSearchForm1.Controls.Panel() $WinSearchPanel1.BackColor = "MediumSeaGreen" $WinSearchPanel1.Size = 447, 114 $WinSearchPanel1.Location = 0, 0 $WinSearchPanel1.Dock = 1 ;~~< WinSearchLabel1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchLabel1 $WinSearchLabel1 = $WinSearchPanel1.Controls.Label() $WinSearchLabel1.Text = "Computer Name" $WinSearchLabel1.Size = 100, 22 $WinSearchLabel1.TextAlign = 32 $WinSearchLabel1.Location = 13, 13 ;~~< WinSearchTextBox1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchTextBox1 $WinSearchTextBox1 = $WinSearchPanel1.Controls.TextBox() $WinSearchTextBox1.Size = 165, 20 $WinSearchTextBox1.Location = 119, 13 ;~~< WinSearchLabel2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchLabel2 $WinSearchLabel2 = $WinSearchPanel1.Controls.Label() $WinSearchLabel2.Text = "Search String" $WinSearchLabel2.Size = 100, 22 $WinSearchLabel2.TextAlign = 32 $WinSearchLabel2.Location = 13, 47 ;~~< WinSearchTextBox2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchTextBox2 $WinSearchTextBox2 = $WinSearchPanel1.Controls.TextBox() $WinSearchTextBox2.Size = 165, 20 $WinSearchTextBox2.Location = 119, 47 ;~~< WinSearchGroupBox1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchGroupBox1 $WinSearchGroupBox1 = $WinSearchPanel1.Controls.GroupBox() $WinSearchGroupBox1.Text = "Search in" $WinSearchGroupBox1.Size = 95, 91 $WinSearchGroupBox1.Location = 314, 13 $WinSearchGroupBox1.TabStop = 0 ;~~< WinSearchRadioButton1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchRadioButton1 $WinSearchRadioButton1 = $WinSearchGroupBox1.Controls.RadioButton() $WinSearchRadioButton1.Text = "Filename" $WinSearchRadioButton1.Size = 79, 24 $WinSearchRadioButton1.Checked = -1 $WinSearchRadioButton1.Location = 6, 19 $WinSearchRadioButton1.TabStop = -1 ;~~< WinSearchRadioButton2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchRadioButton2 $WinSearchRadioButton2 = $WinSearchGroupBox1.Controls.RadioButton() $WinSearchRadioButton2.Text = "Contents" $WinSearchRadioButton2.Enabled = 0 $WinSearchRadioButton2.Size = 79, 24 $WinSearchRadioButton2.Location = 6, 40 ;~~< WinSearchRadioButton3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchRadioButton3 $WinSearchRadioButton3 = $WinSearchGroupBox1.Controls.RadioButton() $WinSearchRadioButton3.Enabled = 0 $WinSearchRadioButton3.Size = 79, 24 $WinSearchRadioButton3.Location = 6, 61 ;~~< WinSearchButton1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchButton1 $WinSearchButton1 = $WinSearchPanel1.Controls.Button() $WinSearchButton1.Text = "Search" $WinSearchButton1.Size = 165, 23 $WinSearchButton1.Location = 119, 81 ;~~< WinSearchPanel2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchPanel2 $WinSearchPanel2 = $WinSearchForm1.Controls.Panel() $WinSearchPanel2.Size = 447, 243 $WinSearchPanel2.Location = 0, 114 $WinSearchPanel2.Dock = 5 ;~~< WinSearchListView1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchListView1 $WinSearchListView1 = $WinSearchPanel2.Controls.ListView() $WinSearchListView1.Size = 447, 243 $WinSearchListView1.View = 1 ;~~< WinSearchListView1.Columns >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $WinSearchColumn = $WinSearchListView1.Columns.Add $WinSearchColumn.Width = 421 $WinSearchColumn.Text = "File Path" $WinSearchListView1.Location = 0, 0 $WinSearchListView1.MultiSelect = 0 $WinSearchListView1.GridLines = -1 $WinSearchListView1.Dock = 5 ;~~< WinSearchContextMenu1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Global $WinSearchContextMenu1 $WinSearchContextMenu1 = $System.ContextMenu() ;~~< WinSearchContextMenu1.MenuItems >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $WinSearchClearListToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Clear List") $WinSearchClearListToolStripMenuItem.Text = "Clear List" $WinSearchCopyListToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Copy List") $WinSearchCopyListToolStripMenuItem.Text = "Copy List" $WinSearchCopyFilenameToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Copy Filename") $WinSearchCopyFilenameToolStripMenuItem.Text = "Copy Filename" $WinSearchOpenFileToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Open File") $WinSearchOpenFileToolStripMenuItem.Text = "Open File" $WinSearchOpenFileLocationToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Open File Location") $WinSearchOpenFileLocationToolStripMenuItem.Text = "Open File Location" $WinSearchDeleteFileToolStripMenuItem = $WinSearchContextMenu1.MenuItems.Add("Delete File") $WinSearchDeleteFileToolStripMenuItem.Text = "Delete File" ;endregion $WinSearchListView1.ContextMenu = $WinSearchContextMenu1 $WinSearchClearListToolStripMenuItem.OnClick = "WinSearchClearlistview()" $WinSearchButton1.Enabled = 0 $WinSearchButton1.OnClick = "WinSearch()" $WinSearchtextBox2.OnKeyDown = "WinSearchButtonEnable()" Function WinSearchClearlistview() $winSearchListView1.Items.clear EndFunction Function WinSearchButtonEnable() If Trim($WinSearchTextBox2.Text) > "." $WinSearchButton1.Enabled = 1 Else $WinSearchButton1.Enabled = 0 EndIf EndFunction Function WinSearch() $WinSearchButton1.Enabled = 0 $PC = Trim($WinSearchTextBox1.Text) $SearchStr = Trim($WinSearchTextBox2.Text) $IP = WPing($PC) If @ERROR $WinSearchStatusBar1.Text = $pc + ' offline' Else $WinSearchStatusBar1.Text = 'Connecting to: ' +$pc $objWMIService = GetObject("winmgmts:\\" + $pc + "\root\cimv2") ; ****** create remote share ****** $objNewShare = $objWMIService.Get("Win32_Share") $errReturn = $objNewShare.Create("C:\", "C", "0", "2", "Temporary access for remote Windows Search.") If Not @error ; ****** query Windows Search ****** $cn = CreateObject("ADODB.Connection") $cmd = CreateObject("ADODB.Command") $rs = CreateObject("ADODB.RecordSet") $cn.connectionstring = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" $cn.open If @error $WinSearchStatusBar1.Text = 'Unable to connect to Windows Search' Else $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmdtxt = "SELECT System.ItemFolderPathDisplay, System.ItemName FROM " + $pc + ".SystemIndex WHERE scope='file://" + $pc + "/c' and Contains(System.FileName,'" + $SearchStr + "') order by System.ItemFolderPathDisplay" $cmd.commandtext = $cmdtxt $rs.open($cmd) If @error $WinSearchStatusBar1.Text = 'bad parameter in search query' Else $WinSearchStatusBar1.Text = $cmdtxt $count = 0 $rs.MoveFirst While Not $rs.eof $fullpath = '' + $rs.fields.item("System.ItemFolderPathDisplay").value + '\' + $rs.fields.item("System.ItemName").value $add = $WinSearchListView1.Items.add($fullpath) $count = $count + 1 $rs.moveNext Loop $WinSearchStatusBar1.Text = "Number of hits: " + $count $rs.close ToolLog($Form1.Text, 'Search for: '+$SearchStr, $pc) EndIf $cn.close EndIf ; ****** delete remote share ****** $colShares = $objWMIService.ExecQuery("Select * from Win32_Share Where Name = 'C'") For Each $objShare in $colShares $nul = $objShare.Delete Next Else $WinSearchStatusBar1.Text = 'Unable to create to Windows Search share' EndIf EndIf $WinSearchButton1.Enabled = 1 EndFunction |