Report for comparing of files

[Maestro]

Hello everyone!

 

I've got a task to find all unauthorized executables on all workstations in domain. The good point is that workstations are identical to each other (both hard and soft), bad point is that I have to find the existence of these files on HDDs, not their launches. And I have to use SCCM 2012 SP1 for reporting as well. (That's why I cannot use the AppLocker).

 

Well, what I've decided to do is to take one of workstations as the sample (SW - sample workstation). All updates, patches, etc. are provided at SW first, then are spread on whole domain. All .exe (and another file masks) on SW are presumed as "white", all others on workstations are presumed as "black" ones.What I need now is to compare white-list from SW with file list from every computer in collection.

There already exists almost ready-to-use report that I need, but it has to be modified. Unfortunately, my knowledge of MS SQL is somewhere below zero (maybe below absolute zero, -274C ). That's why I'm asking for help. I've tried to find some articles about creating or modifying reports, but most of them are the same: "Open Report Builder, now copy and paste there the sample query from below. Wonder what a pretty report you've got!"

 

The report is "Compare software inventory on two computers". What modifications do I need:

- Compare not "Computer name - Computer name" but "Computer name - Select a collection"

- Exclude files from white-list (from SW) from report.

- Exclude size, version and time check - only existence and (maybe) the difference in path..

- Group by machine name.

 

Maybe instead of ready query you can advice me some good article like "Composing reports in Report Builder for absolute newbies", I'll appreciate it very much as well.

Thank you for your time.

Sincerely, Alexey

[GarthMJ]

I’m sorry to say this is an insane request. You need to talk to the person asking this and tell them “No”.

 

Yes it is theoretically possible to do but theory and practice are two different things.

 

1. CM12 will NOT inventory all EXE, even if you tell it too.
2. CM12 will time out after 4 hours (runtime, not real time) of Scanning, if it not complete it will just stop and that it.
3. While SW Inv is going on all other inventory tasks are blocked.
4. Each different computer will have different EXE based on what tools are installed or Driver are installed. ‘
5. Just using my PCs as an example and only look at EXE, I have over 4700 EXEs on my C: alone!
6. Each version of a EXE will be consider as two different items.
7. Etc.

 

Seriously I could go on and on about how this will virtually never work.

 

What you need to find out is exactly what are their trying to solve by doing this. What is the risk of nto doing this and how much $$s do they want to spend on this?

[Maestro]

Hello, Garth!

 

Thank you for fast and detailed answer.

 

To say "No" to customer, I need to have some reasons that are hard as reinforced concrete. That's why I'm particularly interested in two of your statements. If you can, explain them in details, please.

 

CM12 will NOT inventory all EXE, even if you tell it too.

Is this a well-known bug described somewhere? If yes - only this fact is enough to stop the work.

 

CM12 will time out after 4 hours (runtime, not real time) of Scanning, if it not complete it will just stop and that it.

As the previous statement, this is also very serious fact to consider. Can you explain it or give me a link where it is described in details?

 

Thank you very much in advance.

Sincerely, Alexey

[GarthMJ]

Since you are clearly a consultant and clearly are going to charge your client for this work. I will let you do all the research into this but I will point you in the right direction.

 

Skpswi.dat

http://technet.microsoft.com/en-us/library/bb632671.aspx

Plus System and Hidden file don’t always get inventoried.

 

4 hour time out, This is a well know issue and you can see the 4 hour time by reviewing your inventoryagent.log, just look at the last command. And there is no supported way to change this.

Collection: Namespace = \\.\root\ccm\invagt; Query = SELECT Name, Path, LastWriteDate, Size, CompanyName, ProductName, ProductVersion, ProductLanguage, FileVersion, FileDescription FROM FileSystemFile WHERE Name = 'iexplore.exe' AND Path = 'C:\\Program Files\\Internet Explorer\\*' AND IsCompressed = FALSE AND IsEncrypted = FALSE; Timeout = 14400 secs.

 

 

You should read this one as well.

http://be.enhansoft.com/post/2013/10/03/Slow-Software-Inventory-Cycle-in-SCCM-2012.aspx

 

I too am a consultant and I regularly tell my clients they are nuts. What they think is a simple thing is not always or it will cost huge $$s. This is one of those things that just doesn’t make sense.