Jump to content


mike

Established Members
  • Posts

    29
  • Joined

  • Last visited

mike's Achievements

Newbie

Newbie (1/14)

0

Reputation

  1. I got it! Just in case any one else was wondering. SELECT DISTINCT v_R_System.Netbios_Name0 'Computer Name', v_R_System.User_Name0 AS 'Last user logged in', v_R_User.Full_User_Name0, v_R_User.physicalDeliveryOfficeNam0 'Location', v_R_User.telephoneNumber0 'Telephone', v_RA_System_IPAddresses.IP_Addresses0 'IP Address', v_GS_PC_BIOS.SerialNumber0 'Serial Number', v_RA_System_MACAddresses.MAC_Addresses0, v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 'Assest Tag', v_R_System.Operating_System_Name_and0, v_GS_COMPUTER_SYSTEM.TimeStamp FROM v_R_System INNER JOIN v_R_User ON v_R_System.User_Name0=v_R_User.User_Name0 INNER JOIN v_RA_System_IPAddresses ON v_R_System.ResourceID=v_RA_System_IPAddresses.ResourceID INNER JOIN v_GS_PC_BIOS ON v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID INNER JOIN v_RA_System_MACAddresses ON v_R_System.ResourceID=v_RA_System_MACAddresses.ResourceID INNER JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID= v_GS_SYSTEM_ENCLOSURE.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID WHERE IP_Addresses0 NOT LIKE 'Fe80%'
  2. Hello all, I have been trying to join to reports of computer information from v_R_system, v_RA_System_IPAddress, v_RA_System_MACAddress and v_GS_PC_BIOS. Along with user information from v_R_User. My SQL query kind of works but it shows many duplicates of users and computer names. Here is the SQL query: SELECT TOP (100) PERCENT dbo.v_R_System.Netbios_Name0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_R_User.Full_User_Name0, dbo.v_R_User.physicalDeliveryOfficeNam0, dbo.v_R_User.telephoneNumber0, dbo.v_R_System.User_Name0, dbo.v_RA_System_IPAddresses.IP_Addresses0, dbo.v_RA_System_MACAddresses.MAC_Addresses0, dbo.v_GS_PC_BIOS.SerialNumber0 FROM dbo.v_RA_System_MACAddresses INNER JOIN dbo.v_RA_System_IPAddresses ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_RA_System_IPAddresses.ResourceID INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_GS_PC_BIOS.ResourceID CROSS JOIN dbo.v_R_System INNER JOIN dbo.v_R_User ON dbo.v_R_System.User_Name0 = dbo.v_R_User.User_Name0 Here are my SCCM queries that I would like to combine: SELECT A.Netbios_Name0, B.SerialNumber0, A.User_Name0 AS 'Last user logged in' A.Operating_System_Name_and0, C.IP_Addresses0, D.MAC_Addresses0 FROM v_R_System A, v_GS_PC_BIOS B, v_RA_System_IPAddresses C, v_RA_System_MACAddresses D WHERE A.ResourceID=B.ResourceID AND B.ResourceID=C.ResourceID AND C.ResourceID=D.ResourceID ************************************************* SELECT Full_User_Name0,physicalDeliveryOfficeNam0,telephoneNumber0 FROM v_R_User Can someone please send me in the right direction or assist with joining these two queries. Thanks, Mike
  3. mike

    Variable Report

    Finally got, I day and half later! SELECT v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_R_System.User_Name0, v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion, v_GS_SoftwareProduct.CompanyName FROM v_GS_PC_BIOS JOIN v_GS_SoftwareProduct ON v_GS_PC_BIOS.ResourceID = v_GS_SoftwareProduct.ResourceID JOIN v_R_System ON v_GS_SoftwareProduct.ResourceID = v_R_System.ResourceID WHERE v_GS_SoftwareProduct.ProductName = @variable ORDER BY v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion
  4. mike

    Variable Report

    Hello, Can someone assist me with modifing SCCM Report ID 66? The statement for Report ID 66 is: Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion FROM v_GS_SoftwareProduct SP JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID WHERE SP.ProductName = @variable AND SP.ProductVersion = @variable1 Order by SP.ProductName, SP.ProductVersion I want to add the computer's serial number into this statement. This is what I have: SELECT v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_R_System.User_Name0, v_SoftwareProduct.ProductName, v_SoftwareProduct.ProductVersion, v_SoftwareProduct.CompanyName FROM v_R_System, v_GS_PC_BIOS, v_SoftwareProduct JOIN I get errors here as well becuase of the "=" v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID AND v_GS_SoftwareProduct.ProductID = v_SoftwareProduct.ProductID WHERE v_SoftwareProduct.ProductName = @variable (I get Errors here because of the '@' sign) ORDER BY v_SoftwareProduct.ProductName, v_SoftwareProduct.ProductVersion Can someone help out on why my errors are occurring? I have also trired different JOIN and FROM variations, but always encounter errors. Thanks, Mike I made changes afer reviewing the above statement: SELECT v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_R_System.User_Name0, v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion, v_GS_SoftwareProduct.CompanyName FROM v_R_System, v_GS_PC_BIOS, v_GS_SoftwareProduct JOIN v_R_System ON v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID WHERE v_GS_SoftwareProduct.ProductName = @variable ORDER BY v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion But now I get the following error: An error occurred when the report was run. The details are as follows: The multi-part identifier "v_GS_PC_BIOS.ResourceID" could not be bound. Error Number: -2147217900 Source: Microsoft OLE DB Provider for SQL Server Native Error: 4104
  5. mike

    Custom Reporting

    Hello, I am trying to create a report that will prompt to enter an AD boundary then once runned it will count the clients and list some system resources. I have gotten to runing a query on the entire AD site with the system resources needed be listed. SELECT a.Netbios_Name0, a.User_Name0, a.Operating_System_Name_and0, a.AD_Site_Name0, b.IP_Addresses0 From v_RA_System_IPAddresses b, v_R_System a WHERE a.ResourceID=b.ResourceID But now I want to query say boundary 2 only and not the entire listing. I have tried using the 'count' command with no success and also the v_SiteBoundary_ADSite view with column ADSiteName. but couldnt find a relation with the current views that I am using. Any help appreciated. Thanks, mike Ok so I have this but still having issues: SELECT a.Netbios_Name0, a.User_Name0, a.Operating_System_Name_and0, a.AD_Site_Name0, b.IP_Addresses0, Count(SMS_Installed_Sites0) AS 'Count' From v_R_System a, v_RA_System_IPAddresses b, v_RA_System_SMSInstalledSites c WHERE a.ResourceID=b.ResourceID AND b.ResourceID=c.ResourceID Group By a.Netbios_Name0, a.User_Name0, a.Operating_System_Name_and0, a.AD_Site_Name0, b.IP_Addresses0, c.SMS_Installed_Sites0
  6. mike

    Collection

    Ok, i thought this was going to be easy but it hasnt. I have created a new collection and want it to populate all computers that start with a specific computer name. So I created a basic query from the Reports section and copied my statement to the Collection Query Rule Properties, so this is what I have: SELECT Netbios_Name0 FROM v_R_System WHERE Netbios_Name0 LIKE 'XYZ%' XYZ is representing the start of the computer name. In the report function it works great, but when I copy it to the Collection it doesnt return any results...... I have noticed the Resource class, but not sure what to have it on. So I leave it on System Resource (but it wont take my query). I can select a different resource class but i get no result. I enable the 'limit to collection' and limit it to all systems. Questions: 1. Should I be creating the queries in the Report part? If not what should I be using? 2. Is my script wrong? thanks, mike OK, i got this to work, just not sure it will automatically add computers from the all system collection to the specified collection: select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name like "XYZ%"
  7. mike

    Secondary MP setup

    No, the other site has about 200 systems and growing but are in a different subent than the main location but all in the same domain. I have setup the secondary site at the other location and the install went well and have the active directory discovery enabled. I wouldnt call it a poor connection, but it could be better. but from you suggestion i went ahead and installed the secondary site to see how it fairs. right now i have some clients managed and some not. Not sure why some are not managed, although I know one system has the client installed but it is not reporting. the secondary site status is good but for the warnings that some clients cannot be found on the network, even though the clients are online.
  8. mike

    WSUS/SCCM 2007

    hello web world, SCCM 2007(mixed) WSUS on same server as SCCM. I have created an all All XP folder with the search critera of: 1. Product (Windows XP) 2. Update Classification (critical, feature packs, security updates, service packs, tools, update rollups, updates and applications)I did this to remove driver updates. 3. Expired (No) 4. Superseded (No) I have checked marked search all subfolders. But I cant seem to find the option to only download updates for english. I have checked the WSUS settings (inside server manager) and the SCCM settings (software update point component properties). Now not all updates come in different languages it is mostly service packs, .net service packs, IE and media player interfaces. Is there a way I can remove these other languages from software updates? thanks, Mike
  9. mike

    Secondary MP setup

    thanks wmmayms, i will try to BDP option. I saw a guide in the forum about it. I will give it a try. we dont have a bad network, we have about 2000+ local systems and about 200+ are off site and i want to push the updates to them 'locally' instead of coming across the network. i was thinking i could just push it to one local computer and the clients can update locally. but we do have servers over there that i can make a BDP. mike
  10. mike

    Secondary MP setup

    Question: Would you all recommend setting up a secondary management point for computers in a different subnet? I currently have a remote site with a different subnet and want to avoid pushing updates to 100+ computers over the newtork when I just want to do it a management point. Or would creating a secondary CM site work better? i was hoping to put a seconday MP up at the remote site so those clients can be updated. Will I need to reconfigure the clients with the secondary MP or will it search for either of the MPs? SCCM2K7 SP1 WinServer2K8
  11. mike

    New Collection

    Thanks a bunch! I see the approved clients. Sorry was out yesterday. Really appreciate your help.
  12. mike

    New Collection

    Ok, I am lost here. I thought I could just copy some other SQL queries (with modifications) but it is not working. Either it doesnt run or I have errors. I think it is a simple query but I am lost. I want to create a new collection that only shows approved clients, I believe I did it before but cant find my old scripts. Can someone save me? Thanks, mike
  13. I have setup SCCM with WSUS and i was finally leveling out and getting it to work the way i need it to work, but now i am running out of disk space for all the update downloads. So i connected an external storage drive for now. My questions: Can i move all my update folders to my new external drive and still have the WSUS to function? If yes, where do i need to go to reconfigure the storage locations for WSUS or SCCM (i couldnt find it, other than reinstalll WSUS) ? (I thought i could just do it when deploying the software, but that didnt work) thanks, mike
  14. Log Name: Application Source: SMS Server Date: 7/8/2009 2:09:58 PM Event ID: 6703 Task Category: SMS_WSUS_SYNC_MANAGER Level: Error Keywords: Classic User: N/A Computer: SCCM Server Name Description: On 7/8/2009 2:09:58 PM, component SMS_WSUS_SYNC_MANAGER on computer computername reported: SMS WSUS Synchronization failed. Message: UssCommunicationError: WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did n. Source: Microsoft.SystemsManagementServer.SoftwareUpdatesManagement.WSyncAction.WSyncAct ion.SyncWSUS. The operating system reported error 2148734464: Event Xml: <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> <System> <Provider Name="SMS Server" /> <EventID Qualifiers="49152">6703</EventID> <Level>2</Level> <Task>73</Task> <Keywords>0x80000000000000</Keywords> <TimeCreated SystemTime="2009-07-08T19:09:58.000Z" /> <EventRecordID>39292</EventRecordID> <Channel>Application</Channel> <Computer>computername</Computer> <Security /> </System> <EventData> <Data>Microsoft.SystemsManagementServer.SoftwareUpdatesManagement.WSyncAction.WSync Action.SyncWSUS</Data> <Data>UssCommunicationError: WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did n</Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data> </Data> <Data>On 7/8/2009 2:09:58 PM, component SMS_WSUS_SYNC_MANAGER on computer sccm server name reported: </Data> <Data> The operating system reported error 2148734464: </Data> </EventData> </Event> SCCM 2007 SP 1 Win2K8 with WSUS WSUS was working fine without any issues until about 3 weeks ago when this error kept popping up very now and then. Came across a suggestion to remove the FQDN from the site settings of the SUP. (Site DB>Site Managemnet>Site>Site Settings>Site Systems>ConfigMgr software update point) I removed the role and re-added the role without the FQDN and the WSUS has been updating without issues for 2 days now. Not sure why it was working then stopped (maybe anyone has an idea) but hopefully this will help others.
  15. mike

    SCCM Reporting Webpage

    so i noticed in my sql logs (c:program files\mssql server\mssql10.sccm name\mssql\log) that i was receiving this error as well: 2009-06-25 08:52:42.16 Logon Error: 18456, Severity: 14, State: 38. 2009-06-25 08:52:42.16 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: IP Address ] 2009-06-25 08:52:42.18 spid14s Service Broker manager has started. 2009-06-25 08:52:42.44 Logon Error: 18456, Severity: 14, State: 38. 2009-06-25 08:52:42.44 Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: IP Address ] 2009-06-25 08:52:42.50 Logon Error: 18456, Severity: 14, State: 38. 2009-06-25 08:52:42.50 Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: IP Address ] 2009-06-25 08:52:42.54 Logon Error: 18456, Severity: 14, State: 38. 2009-06-25 08:52:42.54 Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: IP Address ] 2009-06-25 08:52:42.86 Logon Error: 18456, Severity: 14, State: 38. 2009-06-25 08:52:42.86 Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: IP Address ] I found a solution at this website here. Hope this helps someone else as well. Thanks anyweb for your time. I really apprecitate your site! Keep up the good work! mike
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.