Hi,
Andrew recently gave me a hand with an Excel formula I had been working on to automatically construct an SCCM collection query from a list of computer names entered in column B of an Excel 2010 spreadsheet (found this thread here: http://www.mrexcel.com/forum/excel-questions/730447-excel-formula-help-dynamic-cell-range.html).
I’ve found that this has been a useful tool, and a real time saver, in rapidly creating SCCM collection queries and so I decided to try my hand at modifying this Excel spreadsheet to construct a second SCCM collection query I use all the time, that creates a collection using a range of hostnames instead of listing each individual host name in the query.
For me it’s also the fun and challenge of seeing how far I can push myself, using Excel to take a tedious and laborious task and make it 100% easier and more efficient through the automation of Excel formulas. I also always learn something new I didn’t know before about Excel through the process, which I love.
The second SCCM query I wish to generate using this Excel spreadsheet will be close to the first query, except that it will list start and end computer names that appear in a series instead of listing each individual computer name.
Here’s the original SCCM collection query:
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 where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE34564" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE65324" or SMS_R_System.NetbiosName = "PRE23465" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE65325" or SMS_R_System.NetbiosName = "PRE34565" or SMS_R_System.NetbiosName = "PRE34566" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE65326" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323" or SMS_R_System.NetbiosName = "PRE12344" or SMS_R_System.NetbiosName = "PRE65322" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65321" or SMS_R_System.NetbiosName = "PRE34563" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE34567" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName = "PRE23466" or SMS_R_System.NetbiosName = "PRE23468"
Here’s the second, range SCCM collection query:
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 where SMS_R_System.NetbiosName >= "PRE12344" and SMS_R_System.NetbiosName <= "PRE12349" or SMS_R_System.NetbiosName >= "PRE23465" and SMS_R_System.NetbiosName <= "PRE23466" or SMS_R_System.NetbiosName >= "PRE34563" and SMS_R_System.NetbiosName <= "PRE34567" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName >= "PRE65321" and SMS_R_System.NetbiosName <= "PRE65322" or SMS_R_System.NetbiosName >= "PRE65324" and SMS_R_System.NetbiosName <= "PRE65326" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE23468" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323"
As you can see, the first SCCM formula lists each individual computer name separately whereas the second formula uses start and end computer names (with greater than or equal to and less than or equal to BOOLEAN) to specify ranges, with the singular computer names appearing at the end of the formula as single entities.
So the challenge here was to try and construct a means of being able to determine:
In a column D of my Excel 2010 spreadsheet, which contain computer names entered in no particular order of numerical sequence:
1.) What are the sequential runs in this list in column D.
2.) What are the start and end computer number values of each sequential run (needed to construct my range).
3.) What are the singular computer name entities in this list in column D.
4.) To be able to extract the start and end numbers of each sequential run into a separate list that can then be imputed into an Excel formula (see the formula used at the end of this post), which outputs the SCCM query formula I can then copy and paste into the Configuration Manager’s Query Statement textbox.
5.) To extract each single entity into a separate list that can then be imputed into an Excel formula that outputs the SCCM query formula.
6.) When inputting range start and end computer name values and singular entity computer name values, be able to distinguish in my list which are the numerical sequential start and end values and which are the singular entities.
7.) To be able to do all of the above dynamically using formulas, in that the number of computers entered will vary from use to use as will the number of sequential runs and the number of singular entities vary from use to use.
On Saturday I began to piece together some helper columns, in columns G through J, that:
1.) Reorder the computer names entered in column D in numerical order in column G using a SMALL formula.
2.) That uses a concatenate formula in column H to add the prefix to the computer name, entered in cell $E$1.
3.) That uses an IF formula in column I to list SEQUENCE in that cell (if that runs is part of a sequence) or SINGLE in that cell (if a single entity).
4.) That uses an IF statement in column J to list START OF SEQUENCE next to each computer name that is at the start of a numerical sequence run, END OF SEQUENCE next to each computer name that is at the end of a numerical sequence run and nothing (“”) for all other cells in the column.
What I haven’t worked out yet is how to best take the START of SEQUENCE and END OF SEQUENCE information and turn this into the start and end values (using a MATCH formula to grab the computer names from column H) that I can then input into my final Excel formula that will auto generate my SCCM range formula.
I have images of this helper information in my spreadsheet here: http://staffweb.psdschools.org/mway/excelquestion2/
The original screen captures of my spreadsheet (used in my previous Excel post to explain how my Excel spreadsheet has been laid out) can be found here: http://staffweb.psdschools.org/mway/excelquestion/
Or maybe there’s a more eloquent way of performing this task, without needing the helper columns, using an Excel formula or array formula that I am not aware of?
I imagine for the single entities I can use a straight MATCH or VLookup formula (using information in columns H and I) to generate a new list of single computer names I can then enter into my SCCM query formula using the Excel formula below.
The second challenge in this task that I haven’t yet worked out will be to construct a modification of the below Excel formula, that will output the SCCM range query, in the correct format, as seen above. I think what is going to be challenging here is not constructing the Excel formula to output the correct SCCM query, but to do so in a way that is dynamic and that can accommodate any number of entered computer names with any number of ranges and singular entities - as it’ll be impossible to predict from use to use how many computer names will be entered (it could be anywhere from 1 to 36) nor will it be possible to predict how many machine names will be part of a sequential run or single entities (not part of a sequence).
If anyone has any ideas how to modify the below formula to accommodate the dynamic nature of this data, I’d love to hear your thoughts.
The original Excel formula I used to generate an SCCM query formula (one that listed each computer name separately) was:
="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 where SMS_R_System.NetbiosName = """&ConCatDelim(E3:INDEX(E:E,MATCH(9.99999999999999E+307,D:D)),""" or SMS_R_System.NetbiosName = """)&""""
And this Excel formula outputted the below SCCM collection query I could then copy from Excel to paste into the SCCM Query Statement textbox:
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 where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE34564" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE65324" or SMS_R_System.NetbiosName = "PRE23465" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE65325" or SMS_R_System.NetbiosName = "PRE34565" or SMS_R_System.NetbiosName = "PRE34566" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE65326" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323" or SMS_R_System.NetbiosName = "PRE12344" or SMS_R_System.NetbiosName = "PRE65322" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65321" or SMS_R_System.NetbiosName = "PRE34563" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE34567" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName = "PRE23466" or SMS_R_System.NetbiosName = "PRE23468"
Thanks much to anyone who might have some ideas to help me on this journey!
Matt
Andrew recently gave me a hand with an Excel formula I had been working on to automatically construct an SCCM collection query from a list of computer names entered in column B of an Excel 2010 spreadsheet (found this thread here: http://www.mrexcel.com/forum/excel-questions/730447-excel-formula-help-dynamic-cell-range.html).
I’ve found that this has been a useful tool, and a real time saver, in rapidly creating SCCM collection queries and so I decided to try my hand at modifying this Excel spreadsheet to construct a second SCCM collection query I use all the time, that creates a collection using a range of hostnames instead of listing each individual host name in the query.
For me it’s also the fun and challenge of seeing how far I can push myself, using Excel to take a tedious and laborious task and make it 100% easier and more efficient through the automation of Excel formulas. I also always learn something new I didn’t know before about Excel through the process, which I love.
The second SCCM query I wish to generate using this Excel spreadsheet will be close to the first query, except that it will list start and end computer names that appear in a series instead of listing each individual computer name.
Here’s the original SCCM collection query:
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 where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE34564" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE65324" or SMS_R_System.NetbiosName = "PRE23465" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE65325" or SMS_R_System.NetbiosName = "PRE34565" or SMS_R_System.NetbiosName = "PRE34566" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE65326" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323" or SMS_R_System.NetbiosName = "PRE12344" or SMS_R_System.NetbiosName = "PRE65322" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65321" or SMS_R_System.NetbiosName = "PRE34563" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE34567" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName = "PRE23466" or SMS_R_System.NetbiosName = "PRE23468"
Here’s the second, range SCCM collection query:
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 where SMS_R_System.NetbiosName >= "PRE12344" and SMS_R_System.NetbiosName <= "PRE12349" or SMS_R_System.NetbiosName >= "PRE23465" and SMS_R_System.NetbiosName <= "PRE23466" or SMS_R_System.NetbiosName >= "PRE34563" and SMS_R_System.NetbiosName <= "PRE34567" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName >= "PRE65321" and SMS_R_System.NetbiosName <= "PRE65322" or SMS_R_System.NetbiosName >= "PRE65324" and SMS_R_System.NetbiosName <= "PRE65326" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE23468" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323"
As you can see, the first SCCM formula lists each individual computer name separately whereas the second formula uses start and end computer names (with greater than or equal to and less than or equal to BOOLEAN) to specify ranges, with the singular computer names appearing at the end of the formula as single entities.
So the challenge here was to try and construct a means of being able to determine:
In a column D of my Excel 2010 spreadsheet, which contain computer names entered in no particular order of numerical sequence:
1.) What are the sequential runs in this list in column D.
2.) What are the start and end computer number values of each sequential run (needed to construct my range).
3.) What are the singular computer name entities in this list in column D.
4.) To be able to extract the start and end numbers of each sequential run into a separate list that can then be imputed into an Excel formula (see the formula used at the end of this post), which outputs the SCCM query formula I can then copy and paste into the Configuration Manager’s Query Statement textbox.
5.) To extract each single entity into a separate list that can then be imputed into an Excel formula that outputs the SCCM query formula.
6.) When inputting range start and end computer name values and singular entity computer name values, be able to distinguish in my list which are the numerical sequential start and end values and which are the singular entities.
7.) To be able to do all of the above dynamically using formulas, in that the number of computers entered will vary from use to use as will the number of sequential runs and the number of singular entities vary from use to use.
On Saturday I began to piece together some helper columns, in columns G through J, that:
1.) Reorder the computer names entered in column D in numerical order in column G using a SMALL formula.
2.) That uses a concatenate formula in column H to add the prefix to the computer name, entered in cell $E$1.
3.) That uses an IF formula in column I to list SEQUENCE in that cell (if that runs is part of a sequence) or SINGLE in that cell (if a single entity).
4.) That uses an IF statement in column J to list START OF SEQUENCE next to each computer name that is at the start of a numerical sequence run, END OF SEQUENCE next to each computer name that is at the end of a numerical sequence run and nothing (“”) for all other cells in the column.
What I haven’t worked out yet is how to best take the START of SEQUENCE and END OF SEQUENCE information and turn this into the start and end values (using a MATCH formula to grab the computer names from column H) that I can then input into my final Excel formula that will auto generate my SCCM range formula.
I have images of this helper information in my spreadsheet here: http://staffweb.psdschools.org/mway/excelquestion2/
The original screen captures of my spreadsheet (used in my previous Excel post to explain how my Excel spreadsheet has been laid out) can be found here: http://staffweb.psdschools.org/mway/excelquestion/
Or maybe there’s a more eloquent way of performing this task, without needing the helper columns, using an Excel formula or array formula that I am not aware of?
I imagine for the single entities I can use a straight MATCH or VLookup formula (using information in columns H and I) to generate a new list of single computer names I can then enter into my SCCM query formula using the Excel formula below.
The second challenge in this task that I haven’t yet worked out will be to construct a modification of the below Excel formula, that will output the SCCM range query, in the correct format, as seen above. I think what is going to be challenging here is not constructing the Excel formula to output the correct SCCM query, but to do so in a way that is dynamic and that can accommodate any number of entered computer names with any number of ranges and singular entities - as it’ll be impossible to predict from use to use how many computer names will be entered (it could be anywhere from 1 to 36) nor will it be possible to predict how many machine names will be part of a sequential run or single entities (not part of a sequence).
If anyone has any ideas how to modify the below formula to accommodate the dynamic nature of this data, I’d love to hear your thoughts.
The original Excel formula I used to generate an SCCM query formula (one that listed each computer name separately) was:
="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 where SMS_R_System.NetbiosName = """&ConCatDelim(E3:INDEX(E:E,MATCH(9.99999999999999E+307,D:D)),""" or SMS_R_System.NetbiosName = """)&""""
And this Excel formula outputted the below SCCM collection query I could then copy from Excel to paste into the SCCM Query Statement textbox:
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 where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE34564" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE65324" or SMS_R_System.NetbiosName = "PRE23465" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE65325" or SMS_R_System.NetbiosName = "PRE34565" or SMS_R_System.NetbiosName = "PRE34566" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE65326" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323" or SMS_R_System.NetbiosName = "PRE12344" or SMS_R_System.NetbiosName = "PRE65322" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65321" or SMS_R_System.NetbiosName = "PRE34563" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE34567" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName = "PRE23466" or SMS_R_System.NetbiosName = "PRE23468"
Thanks much to anyone who might have some ideas to help me on this journey!
Matt
