Solved

Use SQL query to make data easier to manage in Excel

Posted on 2010-11-09
25
455 Views
Last Modified: 2012-05-10
I am using a program called WinAudit to scan client PCs.  It is able to dump the audit into a SQL database, but I have not been able to pull this data into excel and have it display how I want it to as a report.

The attached screenshot shows the structure of the data in SQL
 SQL Query
And this image shows how I would like the data to be pulled into Excel
 Excel view
I basically need the values of the column "ItemName" to end up each being their own column name under which it displays the values of "ItemValue1"

Is this even possible?
0
Comment
Question by:nexsyis
  • 10
  • 9
  • 6
25 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34097309
nexsyis,Absolutely possible.  However, to speed things along, please prepare and upload a small Excel sample file, with data for 3 or 4 computers, and for which you substitute dummy values for any sensitive data.Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34097364
Question: for any given computer name, might you have >1 user name?
0
 

Author Comment

by:nexsyis
ID: 34097401
Trying to make this spreadsheet for you now.  To answer your question, if you look at the SQL table there is a column called "auditID."  I would like it to display all unique AuditID's whether it was a duplicate scan from the same computer or not.  The login script will usually prevent more than one record from each PC being created, but it is not the end of the world if there are duplicates.  Again, lets just display all unique AuditIDs.  Thanks for your assistance.
0
 

Author Comment

by:nexsyis
ID: 34097433
Ok here is the sample spreadsheet I would like to create based on teh SQL data, again each line in this spreadsheet should be from the unique Audit ID.
Audits.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34097446
OK, but your sample output has no Audit ID column :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34097452
nexsyis,Sorry if I was not clear: what I need most is the sample *input* (sanitized, of course).Patrick
0
 

Author Comment

by:nexsyis
ID: 34097678
I don't actually need the Audit Id to be in the results, I just need that to be the unique identifier, but if it is easier to do in excel by all means add that column.  I dumped the database backup to a file, is that ok as far as sample input data?

Here is the link to the db backup:

http://rfgd.com/backup.zip

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 34097769
Not tested, as I am not really keen on restoring that db in my SQL Server instance.

The following relies on a parent/child class concept, and I used my Parent Class Builder add-in to automatically generate some of the code: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3802-Parent-Class-Builder-Add-In-for-Microsoft-Excel.html

Save the attached sample file, and then run the ProcessData macro.  It will ask you to select the Excel file with the raw data.  The recast data will get dumped into a new workbook.

Note that the code in the snippets below goes into three separate modules (two of which are class modules).
'**************************************************************************
'**************************************************************************
'**************************************************************************

'      Begin Class Module: clsAuditIDs

'**************************************************************************
'**************************************************************************
'**************************************************************************

' Patrick Matthews, Verint Systems
' Created 2010-11-09

' Parent collection class of clsAuditID

Option Explicit
Option Compare Text

' Container for all clsAuditID objects in the parent collection class
Private coll As Collection

Private Sub Class_Initialize()
    
    Set coll = New Collection
    
End Sub

Private Sub Class_Terminate()
    
    Set coll = Nothing
    
End Sub

Public Function Add(AuditID As String) As clsAuditID
    
    ' Adds a new item to the collection.  Causes an error if an item with the same key already exists
    ' or if you pass a zero length string for the AuditID argument
    
    If AuditID = "" Then
        Err.Raise vbObjectError + 1002, , "AuditID property of clsAuditID object cannot be zero length string"
    End If
    
    Set Add = New clsAuditID
    Add.AuditID = AuditID
    coll.Add Add, AuditID
    
End Function

Public Sub Clear()
    
    ' Recreates (and thus clears) collection
    
    Set coll = New Collection
    
End Sub

Property Get Count() As Long
    
    ' Returns number of items in the collection
    
    ' Read-only
    
    Count = coll.Count
    
End Property

Property Get Item(Index As Variant) As clsAuditID
    
    ' Default property.  Returns an item from the collection.  Index may be either ordinal position (Long) or AuditID (String)
    
    ' Read-only
    
    On Error Resume Next
    Set Item = coll(Index)
    On Error GoTo 0
    
End Property

Public Sub Remove(Index As Variant)
    
    ' Removes an item from the collection.  Index may be either ordinal position (Long) or AuditID (String)
    
    coll.Remove Index
    
End Sub

Function NewEnum() As IUnknown

    ' Enables enumeration of the clsAuditIDs parent collection, i.e.:
    '
    ' For Each Child In Parent...Next
    
    Set NewEnum = coll.[_NewEnum]
End Function

Sub Import()
    
    Dim WbPath As Variant
    Dim SourceWb As Workbook
    Dim SourceWs As Worksheet
    Dim WbWasOpen As Boolean
    Dim LastR As Long
    Dim arr As Variant
    Dim Counter As Long
    Dim AuditID As clsAuditID
    Dim TestAuditID As String
    Dim ItemName As String
    Dim ItemValue As String
    
    WbPath = Application.GetOpenFilename("Excel files (*.xls*), *.xls*", , "Select file to process", , False)
    If WbPath = False Then
        MsgBox "No file selected", vbCritical, "Aborting"
        Exit Sub
    End If
    
    On Error Resume Next
    Set SourceWb = Workbooks(Mid(WbPath, InStrRev(WbPath, "\") + 1))
    If Err <> 0 Then
        Err.Clear
        Set SourceWb = Workbooks.Open(WbPath)
        WbWasOpen = False
    Else
        WbWasOpen = True
    End If
    On Error GoTo 0
    
    Set SourceWs = SourceWb.Worksheets(1)
    With SourceWs
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        arr = .Range(.[a2], .Cells(LastR, "i")).Value
    End With
    If Not WbWasOpen Then SourceWb.Close False
    
    For Counter = 1 To UBound(arr, 1)
        If arr(Counter, 2) <> TestAuditID Then
            TestAuditID = arr(Counter, 2)
            Set AuditID = Me(TestAuditID)
            If AuditID Is Nothing Then Set AuditID = Me.Add(TestAuditID)
        End If
        With AuditID
            ItemName = arr(Counter, "h")
            ItemValue = arr(Counter, "i")
            Select Case ItemName
                Case "Computer Name": .ComputerName = ItemValue
                Case "User Name": .UserName = ItemValue
                Case "Domain Name": .DomainName = ItemValue
                Case "Site Name": .SiteName = ItemValue
                Case "Operating System": .OperatingSystem = ItemValue
                Case "Manufacturer": .Manufacturer = ItemValue
                Case "Model": .Model = ItemValue
                Case "Serial Number": .SerialNumber = ItemValue
                Case "Asset Tag": .AssetTag = ItemValue
                Case "Processor Description": .ProcessorDescription = ItemValue
                Case "Total Memory": .TotalMemory = ItemValue
                Case "Total Hard Drive": .TotalHardDrive = ItemValue
                Case "Local Time": .LocalTime = ItemValue
            End Select
        End With
    Next
    
    Set AuditID = Nothing
    
End Sub

Sub Export()
    
    Dim AuditID As clsAuditID
    Dim Counter As Long
    
    Workbooks.Add
    [a1:m1].Value = Array("Computer Name", "User Name", "Domain Name", "Site Name", "Operating System", _
        "Manufacturer", "Model", "Serial Number", "Asset Tag", "Processor Description", "Total Memory", _
        "Total Hard Drive", "Local Time")
    
    For Counter = 1 To Me.Count
        Set AuditID = Me(Counter)
        With AuditID
            Cells(Counter + 1, "a").Resize(1, 13) = Array(.ComputerName, .UserName, .DomainName, .SiteName, _
                .OperatingSystem, .Manufacturer, .Model, .SerialNumber, .AssetTag, .ProcessorDescription, _
                .TotalMemory, .TotalHardDrive, .LocalTime)
        End With
    Next
    
    Columns.AutoFit
    
    Set AuditID = Nothing
    
End Sub


'**************************************************************************
'**************************************************************************
'**************************************************************************

'      End Class Module: clsAuditIDs

'**************************************************************************
'**************************************************************************
'**************************************************************************






'**************************************************************************
'**************************************************************************
'**************************************************************************

'      Begin Class Module: clsAuditID

'**************************************************************************
'**************************************************************************
'**************************************************************************

' Patrick Matthews, Verint Systems
' Created 2010-11-09

Option Explicit
Option Compare Text

' Container for "write-once read-many" property
Private Safe_AuditID As String

Public ComputerName As String
Public UserName As String
Public DomainName As String
Public SiteName As String
Public OperatingSystem As String
Public Manufacturer As String
Public Model As String
Public SerialNumber As String
Public AssetTag As String
Public ProcessorDescription As String
Public TotalMemory As String
Public TotalHardDrive As String
Public LocalTime As String

Property Get AuditID() As String
    
    ' Returns item's AuditID value
    
    AuditID = Safe_AuditID
    
End Property

Property Let AuditID(AuditIDString As String)
    
    ' Sets AuditID value for item
    
    ' This makes the AuditID property "write once, read many".  If the AuditID is a zero length string,
    ' the Property Let allows you to change it; if not, the procedure raises a user defined
    ' error.  Basically, we cannot allow changes because we want this property to match the
    ' item's true key used when it was added to the parent clsAuditIDs collection
    
    If Safe_AuditID = "" Then
        Safe_AuditID = AuditIDString
    Else
        Err.Raise vbObjectError + 1001, , "Cannot change AuditID property of clsAuditID object"
    End If
    
End Property


'**************************************************************************
'**************************************************************************
'**************************************************************************

'      End Class Module: clsAuditID

'**************************************************************************
'**************************************************************************
'**************************************************************************





'**************************************************************************
'**************************************************************************
'**************************************************************************

'      Begin Regular Module: Module1

'**************************************************************************
'**************************************************************************
'**************************************************************************

Option Explicit

Sub ProcessData()
    
    Dim AuditIDs As clsAuditIDs
    
    Set AuditIDs = New clsAuditIDs
    
    With AuditIDs
        .Import
        If .Count > 0 Then
            .Export
            MsgBox "Done"
        Else
            MsgBox "No data!"
        End If
    End With
    
    Set AuditIDs = Nothing
    
End Sub


'**************************************************************************
'**************************************************************************
'**************************************************************************

'      End Regular Module: Module1

'**************************************************************************
'**************************************************************************
'**************************************************************************

Open in new window

Q-26603383.xls
0
 

Author Comment

by:nexsyis
ID: 34098656
The raw data currently only exists in SQL not excel, so I'm not sure how I'm supposed to run that vba app?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34099445
Dump the raw data into Excel, and then run my code from there.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34101808
The first thing you should do, if you can is to get the data into Excel using Data>Get external data...
You don't need to import all the data - just the columns of interest, one of which should probably be a unique identifier for each record.
Then attach a sample of that data - the zip file contains a .bak file but what is it exactly?
A backup of the data from the query?
If you can't get the data from SQL into Excel then try outputing the query to a file or text.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34101958
>>Then attach a sample of that data - the zip file contains a .bak file but what is it exactly?It's a backup of the SQL Server database.  If you have a SQL Server instance, you could restore the backup there.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 34102206
I tried that with no luck, I'll try again - probably wasn't using the right methods.
Got it, here's the data in Excel if anybody else wants it - I'll go and try matthew's code on it.

EE---10Nov2010---WinAudi2-Restor.xls
0
 
LVL 33

Expert Comment

by:Norie
ID: 34102326
matthew
I tried the code but I got a type mismatch at arr(counter, "h").
I changed "h" to 8, and in the next line "i" to 9 - is that what's needed?
0
 

Author Comment

by:nexsyis
ID: 34102619
Thanks for the help Imnorie.  Matthew, I'm not too excel saavy, can you give me the steps involved in running that code you gave me against the spreadsheet that Imnorie came up with?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34103645
Sorry, change these lines in clsAuditIDs:            ItemName = arr(Counter, "h")            ItemValue = arr(Counter, "i")to:            ItemName = arr(Counter, 8)            ItemValue = arr(Counter, 9)Once I did that, the code ran without incident.
0
 

Author Comment

by:nexsyis
ID: 34104309
Thanks guys, totally worked!
0
 

Author Closing Comment

by:nexsyis
ID: 34104327
I think there are quite a few people out there using Winaudit as an inventory tool.  You have both helped immensely.  I appreciate it.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34104373
Doesn't WinAudit 'do' other formats? eg CSV, HTML...
0
 

Author Comment

by:nexsyis
ID: 34104380
Yeah, it lets you export individual audits to single CSV files, but the SQL method is a little cleaner
0
 

Author Comment

by:nexsyis
ID: 34104427
Matthew or Imnorie, can you look at this spreadsheet that I generated myself out of the SQL database and see why it is giving me an error when I run the macro against it?  It looks like the one that Imnorie created did not contain the "username" field but mine does.
Bosdasdsdok2.xlsx
0
 

Author Comment

by:nexsyis
ID: 34104512
Nevermind, I found where the macro showed "User Name" not "User Account"  Works great now!
0
 
LVL 33

Expert Comment

by:Norie
ID: 34104594
Strange - I restored the bak file and then exported it to Excel, that's how I created the workbook I attached.
Didn't change anything in the data.
Anyway, you've sorted it.:)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34104748
Glad to help, and special thanks to imnorie for providing that sample data set :)
0
 
LVL 33

Expert Comment

by:Norie
ID: 34108503
nexsyis
How are you getting the data into SQL Server?
I can possibly see that it might seem 'nicer' but it might actually be harder to work with than with say a CSV file.
Plus a CSV file is far more accessible than a backup of an SQL table, and you could actually create a CSV file from within SQL.
(Well I can anyway - it might not be an option in some versions, or it might need a little more work)
matthewspatrick
Thank you, that's another thing I never knew about SQL server.
I thought you could only backup a whole database and that to restore it would involve all sorts of nonsense.:)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now