Newb: Need help formatting a script output

**Klark Kent** · 05-25-2013, 11:11 PM

Hi Excel Forum!

Newb here. Hope you can help me. TIA.

I'm a network administrator and I've got a few hundred servers who i think are missing hotfixes. 792 servers, 56 possible missing hotfixes.

I'm using a PowerShell script to ping each server for the hotfixes, then if the hotfix isn't installed write to the output file.

My output txt file looks like this:

Server1
KB1234
KB2345
KB3456
KB4567
KB5678
KB6789
Server2
KB1234
KB2345
KB3456
KB4567
Server3
KB2345
KB3456
KB4567
KB5678
KB6789
Server4
KB1234
KB2345
KB3456
KB4567
KB5678
KB6789

With 792 servers and 56 possible missing hotfixes......my output file is huge and unmanageable as a never ending list. I want to pull this ridiculous list into excel and format it so management can comprehend this problem. I'm not set on any particular format, in fact i'm open to suggestions. Hell, anything is better than what I've currently got. I'm far far from an excel guru, but am familiar with basic features and formulas. If someone could help me get this output formatted, I would forever be in there debt.

So, i've come here seeking the help of the Excel Forum.

Thanks in Advance.

**Mallycat** · 05-25-2013, 11:34 PM

You should format your data in a table, then create a pivot table over the top. From there you can do anything.

Ideally, get your script to produce data in the format

Server1,KB1234
Server1,KB2345
Server2,KB1234
Server2,KB2345

etc.

Then put a pivot table over the top.

I will produce a macro that formats you existing data this way and post it shortly.

**Mallycat** · 05-26-2013, 12:10 AM

Here is a sheet that will convert your data. Just paste the data into the input sheet and press the button. The converted data will be on the output page. See the sample pivot table contained within. Once your data is in the right format, select the new table you created and then goto INSERT\PIVOT TABLE. Accept the defaults.

Then On the right hand side, drag the Server field to the row heading box, and the Patch field to the Sum Values box.

One good thing about pivot tables, is you can then swap the fields around, and get the number of servers that need each patch.

**Klark Kent** · 05-26-2013, 12:32 AM

Thank you MallyCat! Let me go give that a shot.

**Klark Kent** · 05-26-2013, 04:52 PM

Hi Mally!

It works brilliantly. more than i could've hoped for. Thank you soo much.

One slight problem. 32990 lines of output from my script. I paste the result into excel press the "button 1" macro and it appears to be calculating.

Is it possible for it to be too big or am I just being impatient.

I have a great home computer. i7. 8GB of RAM. SSD OS drive.

**Mallycat** · 05-26-2013, 06:57 PM

There is no way Excel or your PC wont handle that. It could be getting stuck on something, but I would have to look at the data. Try breaking the code by pressing control break on your keyboard

**Klark Kent** · 05-26-2013, 09:47 PM

Hmmm, i decided to about a quarter of the list since it wouldn't ever finish.
And still the same result. Just the Windows loading pinwheel.

How is the macro setup? is it using the key letters "server"?
Because my actual servers aren't Server1, Server2, Server3.

They are in a naming convention: SiteCode - Server Type - Number

so, like ALATSS12, NYYTSS23, LAATSS34.

...but they do all contain the 3 letters "tss"

**Klark Kent** · 05-26-2013, 11:06 PM

.....mally?

**Klark Kent** · 05-27-2013, 03:05 PM

BUMP. Anyone else?

**Mallycat** · 05-27-2013, 09:06 PM

I can only give you advice based on the sample data you provide. If you post all the data, I can take another look.

**Klark Kent** · 05-28-2013, 12:45 AM

Hi Mally,

I appreciate all your help. I'm just not comfortable posting server names or hotfix #'s.

I can assure you the data is formatted like such tho:

ALATSS001
KB1234
KB2345
KB3456
KB4567
KB5678
KB6789
MISTSS001
KB1234
KB2345
KB3456
KB4567
FLATSS001
KB2345
KB3456
KB4567
KB5678
KB6789
ARKTSS001
KB1234
KB2345
KB3456
KB4567
KB5678
KB6789

all the servers have the 3 letters TSS in the naming convention.

**Klark Kent** · 05-28-2013, 04:33 PM

I fixed it!

sub formatdata()
dim myserver as string
dim mykb as string

application.screenupdating = false

range("a1").select
sheets("output").activate
range("a1").currentregion.delete
range("a1").value = "server"
range("b1").value = "patch needed"
range("a2").select
sheets("input").activate

while selection.value <> ""
if instr(selection.value, "tss") then
myserver = selection.value
selection.offset(1, 0).select
while instr(selection.value, "kb")
mykb = selection.value
sheets("output").activate
activecell.value = myserver
activecell.offset(0, 1).value = mykb
activecell.offset(1, 0).select
sheets("input").activate
activecell.offset(1, 0).select
if selection.value = "" then goto exithere:
Wend
end if
wend
exithere:
Application.screenupdating = true
end sub

**Mallycat** · 05-28-2013, 07:16 PM

Ok, good......

Newb: Need help formatting a script output

LinkBack

Thread Tools

Rate This Thread

Display

Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Re: Newb: Need help formatting a script output

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions